Multiple Columns of Header Data to Append to Row Data Help

[hhamilton]

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):

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

[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.

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

Dan

[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

[danilang]

Hi Heather

I'm still thinking about this.  The addition of the varying FAC, MIN, MAX columns add a extra level of complexity

Dan

[danilang · Accepted solution]

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

[hhamilton]

Hi Dan,

This works. Thank you so much for your help!!

-Heather