November 6, 2020
I have a single csv dataset that looks like below. I have multiple csv files like this which i want to combine once i figure out the correct transformation for a single file. Here is the Sample Excel File containing this data :
The Input data has broadly 4 sections :
1] The 1st 3 columns are used to create Questions, Answers & Label columns.
2] a section of Percentage columns.
3] a section of Stat-testing columns.
4] a section of 1 columns.
I want to Merge each set of columns R.1....R10 and then Unpivot (Stack) them one above the other as shown in the output. The R1...R10 column counts may vary each time, but they will be the same for each section i.e. for Percentage columns, Stat testing columns & 1 columns.
While googling, i am came across an Imke Feldmann post on Merging and unpivoting columns dynamically using a custom function. However, i am unable to select and merge the different sections of columns dynamically. Also, i found Bill Szysz's post which is somewhat similar and trying to figure out a way to modify his 2nd solution i.e. Combine Method on a similar post. Here is Bill Szysz's Combine Method solution.
Percentage section | | Stats section | | Numbers section |
R1 | R3 | R2 | R5 | R4 | | R1 | R3 | R2 | R5 | R4 | | R1 | R3 | R2 | R5 | R4 |
This data is tricky and beyond my amateur skills. Is this possible to do?
October 5, 2010
Cross posted to here
Do not cross post without linking to the cross post to let us know