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]

Hi @CGIQV ,

I've attached the workflow for you.

This was solved using a good old multi-row formula to reference the row above to add the name to every row.

I hope this helps.

M.

[CGIQV]

This got the first half, but I think is missing the second half.  

Names are lining up appropriately which I had a different way of getting done.  My issue is the disjointed columns.  Becky only had data under light and hard, not medium.  The resulting workflow places her hard data under medium:

[mceleavey]

Ah, apologies I didn't notice the differences.

I've built a simple batch macro which will allow you to loop through all names and this should put them in the right format.

I hope this solves it!

M.

[mceleavey]

...and I've attached the macro here as I think I forgot to save the final version...

M.

[CGIQV]

I've been tinkering with this for a while to try and apply it to my data set.  I'm not understanding how the macro is being integrated.

The data set was an example to provide a conceptual basis of the issue which was having data layered one on top of another where the columns did not align.  But it may be 100 people and 10 different parameters where each person is only utilizing a few.

How would the macro need to be modified to account for this?

[mceleavey]

It will work if the data is consistent, but if there are variations not represented in the example data then it won't handle that.

If you could provide a larger dataset more representative of the format you will be using then I can amend accordingly.

All the macro does is individually parses each record into the required format, then unions the results.

M.

[CGIQV]

On my own I can get the file to look something like this:

Chuck	Exercise	Slow	Medium	Fast	Extra Fast
Chuck	Run	1	2	5	4
Chuck	Jump	1	5	8	2
Chuck	Swim	1	5	8	2
Chuck	Bike	1	5	8	2
Mary	Exercise	Slow	Fast	Extra Fast
Mary	Run	1	8	4
Mary	Bike	5	6	3
Gary	Exercise	Fast	Extra Fast
Gary	Run	1	15
Gary	Bike	4	5
Gary	Swim	2	8
Ryan	Exercise	Medium	Extra Fast
Ryan	Bike	9	20

But ultimately I want I single header column (edit: I mean row), not each person to have their own header independent header which prevents the data from being consistent top to bottom.

Thanks in advance for the help.

[mceleavey · Accepted solution]

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.

[CGIQV]

What I meant to say was I can ultimately transposed my dataset into the format I showed in the most recent post, so I've bypassed the need for the name to be layered in as a new column.

I still am unsure how the macro is parsing the data from the columns appropriately.