Forum Discussion
How do I change column headers to a variables under a single column?
Hi all,
I have a dataset with the first column header of 'SKU List' with 3000 SKUs listed. The next 10 columns headers are sales from 2010 to 2020 and the contents in the cells are the number of units sold. Hence, right now, there are a total of 11 columns.
If I would like to move the 2010 to 2020 columns to a single 'Year' Column with the units sold in the next column (compressing my dataset contents to 3 columns), what is the best way? Doing this so I can add further variables in the future instead of using the year numbers as a column header. I do understand that it will make my Excel dataset a lot longer (as each SKU goes from 1 row to 10 rows) but that's fine.
Thanks for your help in advance.
6 Replies
If from left (Source) to right (Target)
- create Target table with headers and only one empty row. Add formulas to it
Year: =INDEX(Source[SKU], MOD(ROW()-ROW(Target[[#Headers],[SKU]])-1,ROWS(Source))+1) SKU: =INDEX(Source[#Headers],1,INT((ROW()-ROW(Target[[#Headers],[SKU]])-1)/ ROWS(Source) )+2) Qty: =INDEX(Source, MATCH([@SKU],Source[SKU],0), MATCH([@Year],Source[#Headers],0))-starting from this first row select the range where to expand, e.g. type in name box P5:R40000 if P5 is the reference on left top cell with data in Target table
- Ctrl+D
- Target table will be expanded on entire range.
I did that on Windows, on Mac it shall be similar.
pheerful Connect to the entire table with PowerQuery. Select the first column. Right-click and select Unpivot other columns. Close and load back to a table.
- Thank you for the response. I cannot install PowerQuery as it is a company laptop and they are very strict about what we do. Is there an alternative solution other than manually inserting/transposing? We do have access to macros.
pheerful From Excel 2016, PQ is integrated in Excel. So you are on an older version?
