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):
Solved! Go to Solution.
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.
Dan
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
Hi Heather
I'm still thinking about this. The addition of the varying FAC, MIN, MAX columns add a extra level of complexity
Dan
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
Hi Dan,
This works. Thank you so much for your help!!
-Heather