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