Backwards fill dynamic columns based on parsing field

amw1

Hi - I have to split out a field of data into a dynamic number of columns and then backfill any empty ones so that all rows have the same number of columns, I currently hard-coded a set number by adding to the field I want to parse but I am wondering if there is a way to do this dynamically.

The field that I am parsing has data similar to: jones,jeff|den,david|hour,sam|glen,judy  where the pipe character is used to separate out each person. The values can remain as last name, firstname. 

so this data:

Recid	Employee	Manager Hierarchy
1	sara smith	jones,jeff|den,david|hour,sam|glen,judy
2	john jones	hour,sam|glen,judy
3	becky wyse	wane,hi|jones,jeff|den,david|hour,sam|glen,judy
4	harold good	jasper,jen|jones,jeff|den,david|hour,sam|glen,judy
5	hi wane	jones,jeff|den,david|hour,sam|glen,judy
6	david den	hour,sam|glen,judy

Becomes data shown below.

Notice that the first name found in the field being parsed is the one used to fill remaining columns. I attached my workflow. I initially tried to count the number of pipes and then wanted to use that to know how many rows to add for each record to bring the number of rows to the max and then pivot them but I couldn't figure out how to find the maximum number of columns needed from the entire data set. 

Thank you for all the help in this!!!!!

Recid	Employee	Manager Hierarchy	1	2	3	4	5	6	7	8	9
1	sara smith	jones,jeff|den,david|hour,sam|glen,judy	glen,judy	hour,sam	den,david	jones,jeff	jones,jeff	jones,jeff	jones,jeff	jones,jeff	jones,jeff
2	john jones	hour,sam|glen,judy	glen,judy	hour,sam	hour,sam	hour,sam	hour,sam	hour,sam	hour,sam	hour,sam	hour,sam
3	becky wyse	wane,hi|jones,jeff|den,david|hour,sam|glen,judy	glen,judy	hour,sam	den,david	jones,jeff	wane,hi	wane,hi	wane,hi	wane,hi	wane,hi
4	harold good	jasper,jen|jones,jeff|den,david|hour,sam|glen,judy	glen,judy	hour,sam	den,david	jones,jeff	jasper,jen	jasper,jen	jasper,jen	jasper,jen	jasper,jen
5	hi wane	jones,jeff|den,david|hour,sam|glen,judy	glen,judy	hour,sam	den,david	jones,jeff	jones,jeff	jones,jeff	jones,jeff	jones,jeff	jones,jeff
6	david den	hour,sam|glen,judy	glen,judy	hour,sam	hour,sam	hour,sam	hour,sam	hour,sam	hour,sam	hour,sam	hour,sam

testhierarchy.xlsx

testhierarchy.yxmd

NickSm

Hey @amw1 

You can make it a little more dynamic than hard-coding in a number of pipes by just doing an extra pivot before you do your fill-down.  Or you can make use of the tile tool as shown above.

testhierarchy.yxmd