Forum Discussion
Excel: Help with putting a subheader into a column
Hi!
I need help transforming the headers into a column. The row next to each header is always "Summing row", so I need a way to go from ie header1 to header2 based on when the summing row appears, but I can't wrap my head around it.
The purpose of this is to VLOOKUP the "As is" into a new sheet in the "To be" format. See an example below. Thanks in advance!!
| As is | To be | ||||
| Name column | Category column | Name column | Header column | Category column | |
| Header1 | Summing row | ||||
| Name1 | Category1 | Name1 | Header1 | Category1 | |
| Name2 | Category2 | Name2 | Header1 | Category2 | |
| Name3 | Category3 | Name3 | Header1 | Category3 | |
| Header2 | Summing row | ||||
| Name4 | Category4 | Name4 | Header2 | Category4 | |
| Name5 | Category5 | Name5 | Header2 | Category5 | |
| Name6 | Category6 | Name6 | Header2 | Category6 |
You can try Power Query. Enter the data in the blue dynamic table. Then click in any cell of the green table and right-click with the mouse and refresh. According to the sample data the assumption is that there are always 3 names under each header and 3 categories under each summing row.
Thanks. Sadly, there is not three names under each header, I just wanted to create a simple example instead of the messy Excel file I'm trying to clean up.
I was thinking something like IF "summing row" is present then copy the cell to the left of "summing row". Repeat until "summing row" appears again, then repeat the loop, but I can't wrap my head around how to do it.
