Fixing the Grouping Received in Excel File

Question

Hi I’m looking for some help on how to convert data in an Excel file.

I was provided an Excel file that has data grouped. How can I populate the missing data down the rows and have it automatically stop when the next row is already populated with new data.

For example, Susan E. Smith’s Specialty is Candy in Department 1, 4, and 8.  I need Susan’s name and ID is each row and then I need it to stop when it gets to Hazel Anderson and then start up again with Bob Jackson, etc.

And if possible, I would like to split out the first name, the last name and the middle initial when provided.

Prometheus · Accepted Answer

@TinaLong I used two Multi-Row Formula tools -- one for Employee and one for ID and filled down using this expression:

if isnull([Employee]) and !isnull([Row-1:Employee])

then [Row-1:Employee]

else [Employee]

endif

I also used the Text to Columns tool and used a space (\s) as the delimiter to push out each value between spaces to a separated column, then renamed them.