datatable - How can I create a table in Excel from a spreadsheet that contains data that is grouped in rows

Question

I have a thousands of rows of here data in a spreadsheet (excel 1 image) that I need to create a table with the data populated across the headers below.

Answer 1

If you don't want to use a VBA or spend time with other custom queries, you can do something like this (not, need to create helper column):

Create a helper column that does a count if:

Using this formula:

=IF(B2="Facility ID:", COUNTIFS($B$1:B2,"Facility ID:"),"")

Then structure your data table like this:

And you can use this formula (starting in cell I7):

=INDEX($A:$E,MATCH($H7,$A:$A,0)+I$4,I$5)

Then all you need to do is drag down the rows and increment the counts and it should work provided that the spacings always stay the same and the locations of the fields also stay the same