Multiple Columns of Header Data to Append to Row Data Help

I'm hoping someone can help me here...I have many Excel files that have multiple sections of column data that needs to repeat for each row under that section and then union with the next set of columns for each row. The number of column sections can vary by file. I've tried a bunch of things, but haven't been able to figure out how to systematically separate out the column groupings and then get all of the header rows to duplicate on each row. Please help - Thanks, Heather

The data looks like the below (I don't need the red data. The green is the info that needs to repeat for each of the column sections):

Sample Input.JPG

And I would like the output to look like (don't need any formatting):

Sample Output.JPG

Input Output file.xlsx

Workflow

Input

Output

Help

Accepted answers

danilang

Hello Heather

I think I've got it now. Similar to the first one but extra complexity around the data rows to handle the differing FAC,MIN,MAX values. This should be able to handle any number of FAC,MIN,MAX triplets in the input as well as any number of data rows. This way you don't have to change the min max headers to match the FAC. I modified your excel input file(attached) to include unique values in each of the FAC,MIN,MAX value cells so I could keep track of which cell ended up where.

Dan

Input Output file.xlsx

Multi-column Header v2.0.yxmd

All comments

danilang

Hi @hhamilton

The trick here is to split the rows into header and data, treat them separately and then join the results. The Transpose/Crosstab sequence in the header section is to handle the case where you might have a varying number of POG: columns.

I had to make a few assumption about your data.

The data rows start with the row that has "DPCI" in field 1.
You always have the same number of columns before the 1st MAX column

Dan

Multi-column Header.yxmd

hhamilton

Thank you @danilang!

This almost works. However, the fac/min/max are different for each one of the column header sets, and the output doesn't account for that. I think if I could figure out how to make the two columns above "Min" and "Max" to be equal the column above "Fac" for each set of headers, I'd be able to complete this. Any thoughts?

Thanks,

Heather

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335