excel cell parsing

[TheDataCleaner]

Hi there 

Only just begun with Alteryx, and am facing a challenge of parsing excel cells to multiple rows in two ways:

• in column D, some cells contain multiple names for one objective; these should split into rows
• in column D, some cells contain multiple names for more objectives; these should be split into rows with the first name in a newly created row with the first objective, the second name in a newly created row with th the second objectve, etc...

I know the first can be done with columns to rows parsing, but the second is too complex for me. Does anyone have an idea how to do this?

Best regards, Sander

[danilang · Accepted solution]

Hi @TheDataCleaner 

Edit: Reread your post and working on a different solution to handle the row 4 case

When you split a column to rows in a text to Column tool or a Regex Parse tool, any values in the other columns are automatically copied to the new rows 

See the attached workflow

Dan

[danilang]

Tricky indeed, @TheDataCleaner 

To handle this, the first multirow tool generates a unique ID for each the rows that need to be filled down(blank volunteer) staring from the one with the multiple volunteers.  The second one fills in the blank volunteer values.  The Text to Columns splits the volunteers on \n.  Once split, the next Multi-row assigns a unique id to each row within each group that needs to be filled.  The filter then removes the rows where the row number != the group number.  The final effect is that volunteer 1 is assigned to the first Volunteer record.  Volunteer 2 is assigned to the first blank record, Volunteer 3 goes to 2nd blank, etc.

Dan

[TheDataCleaner]

Hi Dan,

Thanks so much for taking the time, so you actually managed to find a way!

Having trouble with opening the workflow that u shared.

Is my version of Alteryx 2019.3 x 64 too old? Then I will try to re-build what you invented 🙂

Best regards, Sander

[danilang]

@TheDataCleaner 

The yxmd file is just an xml document.   If you go into the folder where the workflow was extracted to and edit the .yxmd file using NotePad, you can change the second line from 

<AlteryxDocument yxmdVer="2020.3">

to 

<AlteryxDocument yxmdVer="2019.3">

Save the file and you'll be able to open it in your version

Dan

[TheDataCleaner]

Apologies Dan, I do feel like a bit of a donkey, but where in that notepad you said...? 🤔