Excel: Help with putting a subheader into a column

New Contributor



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 columnCategory column Name columnHeader columnCategory column
Header1Summing row    
Name1Category1 Name1Header1Category1
Name2Category2 Name2Header1Category2
Name3Category3 Name3Header1Category3
Header2Summing row    
Name4Category4 Name4Header2Category4
Name5Category5 Name5Header2Category5
Name6Category6 Name6Header2Category6
3 Replies


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.

name header category.JPG


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.


best response confirmed by dain14ac (New Contributor)


name header category.JPG

You can try this Power Query suggestion. This could be what you are looking for.