Extracting data from misaligned columns

I fear in trying to simplify for the purposes of asking my question, I've omitted some key points.

My additional table would just be using cell refs to pull in the data in the respective columns.

I was planning on using something like =if[NAME]*<>"",[CellRef] so that I only picked up the from lines that were populated with results. I'd suppress any lines that were blank. I've additionally pulled the race title onto the same line as the rest of the data so that can be pulled across.

So, I want to write one line's worth of cell references that I can use to pull the right data into the right columns, then copy those down the entire table. It's writing those cell references to deal with the fact that some data is offset that I can't figure out.

* i.e. using the Name column as a check of whether the row is populated with a result or not.

Your intermediate table seemed to work well, so I adapted it a bit.... and stuck on a few extra bits to identify the important add-ons. Take a look. Lots of formulae in the shaded areas. Happy to explain any of them.

Take a look...

The one that does the hard work is in T2. To improve understanability... change it to (i moved it at one stage and forgot to change the bit in red):

=IFERROR(AGGREGATE(15,6,ROW($G$2:$G$20)/(($G$2:$G$20<>"Name")*($G$2:$G$20<>"")),ROWS(T$2:T2)),"")

How does it work?

=IFERROR(AGGREGATE(15,6,ROW($G$2:$G$20)/(($G$2:$G$20<>"Name")*($G$2:$G$20<>"")),ROWS(T$2:T2)),"")

Basically it is looking in G2:G20 for rows that match TWO criteria:

RED - not = "Name" and

Cyan - not blank

It then returns the row numbers, that match those criteria... T$2:T2 being a counter... and AGGREGATE's subfunction 15 telling it to retun them in ascending order.

The rest is reasonably straightforward.