Making Sense Out Of Horrible Spreadsheet

Hello Alteryx Gurus -

I've got a spreadsheet from the customer that fits the format of the attached document. Basically this is how the customer has their scheduling data prepared, my job is to write a workflow that can digest it, and eventually reconcile back to me an Alteryx Ace, Customer name, address, and phone number. It has merged cells sometimes, lots of empty cells, and instances where sometimes the address spans two cells, sometimes only one, with a resultant change in the location of the phone number. I've been banging my head on this one a while and nothing is happening.

Can anyone help?

Thanks.

brian

sampleSpreadsheetForAlteryxForums.xlsx

Developer

Dynamic Processing

Preparation

Accepted answers

WillBowman

Ok so without doing the fun part for you... ;P

1. Open up the file and go to option 7 on the input tool - start on line 3

2. Use multi row to fill in each AlteryxAce field in with the line above it

3. Dynamic rename columns so their names are all different (not all AM)

4. Use transpose to split out each column, grouping by alteryxace.

5. Use regex to segment each type of field

5.1 For phones use regex_match ^(\+\d{1,2}\s)?$?\d{3}$?[\s.-]\d{3}[\s.-]\d{4}$

5.2.1 For address vs name once phone is done, addresses will have numbers and names wont (yeah i know not 100% esp if they are company names)

5.2.2 if 5.2.1 does not work then pull out a copy of the input (before multi row formula), rename columns the same way, and transpose with group by alteryx ace. These are the company names. Then join them to identify.

6. The order of the addresses I haven't thought through all the way but maybe you could search for key words or length to approximate?

7. Cross tab it all back with Type as the headers and grouping by alteryx ace and former column name.

Good luck! Hopefully this all makes sense.

All comments

AustinRiggs94

Does the Original document have the merged, empty, split cells or the output after your workflow? Can you also share some of your workflow?

brianscott

This is a clone of an original document with names, addresses and phone numbers replaced to protect the innocent. I do not need to output a file after the end of this, but I do need to know which person in column A (represented by JM and MF in my sample sheet), has which customers at which time slots, and what the phone number and address of those customers.

I.e., I need to be able to know that Joe has a Random Customer in the second AM slot, and Happy Person B in the first pm slot, along with their address and phone numbers. I need to know that Mark has Another Random Name in the first AM slot, and Last Entry in the first PM slot. With data transformed in that manner, I could work on doing the rest of the tasksI need to perform.

My workflow as it exists now would probably jut give you a migraine. I know it has given me one. I have been able to identify min/max row numbers for a particular agent, and have sort of figured out how to pivot the entire world and identify phone numbers, though the possibility of columns for which there may never be data for is complicating things.

Thanks.

brian

WillBowman

Ok so without doing the fun part for you... ;P

1. Open up the file and go to option 7 on the input tool - start on line 3

2. Use multi row to fill in each AlteryxAce field in with the line above it

3. Dynamic rename columns so their names are all different (not all AM)

4. Use transpose to split out each column, grouping by alteryxace.

5. Use regex to segment each type of field

5.1 For phones use regex_match ^(\+\d{1,2}\s)?$?\d{3}$?[\s.-]\d{3}[\s.-]\d{4}$

5.2.1 For address vs name once phone is done, addresses will have numbers and names wont (yeah i know not 100% esp if they are company names)

6. The order of the addresses I haven't thought through all the way but maybe you could search for key words or length to approximate?

7. Cross tab it all back with Type as the headers and grouping by alteryx ace and former column name.

Good luck! Hopefully this all makes sense.

brianscott

Thanks WillBowman! I'm going to try this now and will report back.

brianscott

That pretty much got me there. Utilizing multi row to give me consistent ace names made the transpose to crosstab component make sense within my brain cells. Thanks a lot.

brian

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235