Forum Discussion
Solved
Transforming rows and columns to a sequence of rows
I have a largely empty spreadsheet that looks something like this:
Col1Label Col2Label Col3Label ...
Row1Label
Row2Label
Row3Label
Row4Label
...
I want to transform it into a spreadsheet that looks like this:
Col1 Col2 Col3....
Col1Label Row1Label
Col2Label Row2Label
Col3Label Row3Label
...
so that I can use an entire row to capture data for each column-row combination.
It is also acceptable to have the first two label columns concatenated so they take up only one column.
Any ideas on the easiest way to do this?
Hello,
Power Query can be used for that. First, copy all column labels into all rows, like this:
Then load the table into Power Query (called Get&Transform) in Excel 2016. Select Column 1 and then click Unpivot other columns. Combine the two columns you want to keep and delete the rest. The result looks like this:
If anyone else is viewing this post and they aren't familiar with the easy method (Power Query). It's also possible to accomplish this with workbook formulas.
=IF(ISBLANK(INDIRECT("Sheet1!A"&(INT((ROW(D1)-1)/3)+1)+1)),"",INDIRECT("Sheet1!A"&(INT((ROW(D1)-1)/3)+1)+1))&IF(ISBLANK(INDIRECT("Sheet1!"&CHAR(65+ROW())&1)),INDIRECT("Sheet2!B"&(ROWS($B$1:B1)-3)),INDIRECT("Sheet1!"&CHAR(65+ROW())&1))
WHERE 3 = COLUMN HEADER COUNT
THIS COULD EASILY BE REPLACED WITH COUNTA()....
See attached workbook for an example.
- Ingeborg,
Worked perfectly. Thanks a lot! Hello,
Power Query can be used for that. First, copy all column labels into all rows, like this:
Then load the table into Power Query (called Get&Transform) in Excel 2016. Select Column 1 and then click Unpivot other columns. Combine the two columns you want to keep and delete the rest. The result looks like this:
