Lining up disjointed columns within same dataset?

CGIQV

I have a report that I'd like to transform so that it can be batch imported to our software.

The report looks something like this:

Name: Charlie
Exercise	Light	Medium	Hard
Run	15	10	2
Jump	20	10	2
Swim	20	5	5
Name: Becky
Exercise	Light	Hard
Run	30	12
Jump	30	12
Swim	25	12

I need this:

Name	Exercise	Light	Medium	Hard
Charlie	Run	15	10	2
Charlie	Jump	20	10	2
Charlie	Swim	20	5	5
Becky	Run	30		12
Becky	Jump	30		12
Becky	Swim	25		12

Using the multi-row formula I've been able to create the name column:

IF Contains([Field1]],"Name:") THEN trim(Replace([Field1],"Name:",""))
ELSE [Row-1:Name] ENDIF

This adds a column and layers in the name until a new name appears.  But I can't seem to figure out how to work with the gap in columns.  When one person doesn't have data in a specific column it is omitted, but I need them all to lineup as shown.

Suggestions?

mceleavey

Are you using the latest macro I attached? The one I'm using renames the columns from the headers dynamically, so you shouldn't get these repeated.

I've attached it now, embedded within the workflow.

solution.yxzp