Excel: Help with putting a subheader into a column

[dain14ac]

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

[#2]

@dain14ac 

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.

[#3]

@OliverScheurich 

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.

[#4 · Accepted solution]

@dain14ac 

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