Would you be able to explain to me how this is working? I understand some of it but am missing something ...

Take the formula in H4 of my example:

=

IF(MOD(ROWS(H$1:H4),2)=0,"",INDEX(A$1:A$7,(ROWS(H$1:H4)-1)/2+1))

The formula counts the rows from H1:H4. That is, 4

Divide by 2 and look at the remainder (that's what MOD does). In this case the remainder when 4 is divided by 2 is 0, so the formula returns "", leaving nothing apparent in H4

Now look at the formula that is in H5

=IF(MOD(ROWS(H$1:H5),2)=0,"",

**INDEX(A$1:A$7,(ROWS(H$1:H5)-1)/2+1)**)

This time the remainder is 1 so the blue part of the formula is evaluated. It becomes

**INDEX(A$1:A$7,(5-1)/2+1)**
**INDEX(A$1:A$7,4/2+1)**
**INDEX(A$1:A$7,3)**
That is, the third cell in the range A1:A7 = A3

Also, the columns I am copying over are coming from different parts of the data set spreadsheet and are not side by side.

Then, instead of one formula copied across and down, you may need a separate formula in the top row of each column and only copy them down. Instead of A$1:A$7 you would need to put the range that should be pulling in to each particular column of the results.