Sorting out this kind of messy data and restructuring or reordering it happens a lot. This is a general strategy that can be adapted to re-sorting data and other challenges. Unless we are talking much larger datasets my strategy is to:
(1) think of my output as a one- or two-D table.
(2) work out the row and column location in the table corresponding to each row of data
(3) use index/match formulas to populate the table accordingly
Probably best to give an example. Below we have some similarly structured made-up source data (Source Name & Ref).
In the next columns ("Increments") we keep track of the 'most recent' row and column number for each row of data. But to keep track of these when there are blank rows we have to carry over the previous row & column position (without any increments).
So the row number is incremented whenever (in this specific case) the first column for that row is "Name Of". Otherwise the row number remains the same.
Similarly the column number is incremented every row, except that when it is a blank row rather than a data row then the column number is not incremented. When the row number changes the column count is reset to 1.
All good, excep that there are now duplicate row and column numbers in the blank rows, so we create another pair of columns that are identical except that we blank out the duplicates ("Locations"). You simply can't do this in one step as you need the duplicates to carry the data down through all the rows including the blank ones.
So at this point we know what the row and column location is for each line of data. Now we have to build the table.
We do this using index/match formulas, matching on a text string we create that uniquely identifies the location. For example, for row 2 column 3 we create a string "2-3". (See column labeled "Index")
So for row 2 column 3 of our table we know we are trying to match with a row of data with the index string of "2-3". Use MATCH() to find its row number and then INDEX() to pull out the data you want from that row.
When there is no string to match - for example for high column numbers once all the data for that row is in - an error is triggered. We can catch this with the IFERROR() function.
Not sure how clear this is ... happy to explain more... this is quite a useful general technique.