Take data from one row and convert to 4 rows and repeat every four rows

Question

I would like to go from here:

To here:

If there are other ways to do this please let me know. I've tried the offset and index features to no avail. The problem is when I try to autofill to the end of the sheet. I don't know how to get the formula to keep going in sets of four.

Answer 1

(Personal Note: Never, but NEVER, organize stuff like you did in the beginning. The functions I designed are incredibly ugly and in no way are they practical or optimized. You have been warned).

Ranting aside, the OFFSET and INDEX are useful features, although that I would have to use an even more convoluted way to then get the needed values. Here is my solution. I'm assuming that the 2nd picture is a different sheet (and that the first sheet is named Sheet1. So, for the formulas in Sheet2:

• A2:

  =IF(MOD(ROW(A2)-2;4)=0;INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/4;0)+2;1;;;"Sheet1"));INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/4;0)+2;9+MOD(ROW(A2)-3;4)*2;;;"Sheet1")))
  

• B2

  =IF(MOD(ROW(B2)-2;4)=0;INDIRECT(ADDRESS(FLOOR((ROW(B2)-2)/4;0)+2;2;;;"Sheet1"));INDIRECT(ADDRESS(FLOOR((ROW(B2)-2)/4;0)+2;10+MOD(ROW(B2)-3;4)*2;;;"Sheet1")))
  

• C2

  =INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;3;;;"Sheet1"))
  

• D2

  =INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;4;;;"Sheet1"))
  

• E2

  =INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;6;;;"Sheet1"))
  

• F2

  =INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;7;;;"Sheet1"))
  

• G2

  =INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;8;;;"Sheet1"))
  

After inputting the formulas, select the 2 row and drag down. It should work (as I've tested it). I'm not (currently) in the mood to explain every detail of the functions, but they hang on very simple math operations that end up mapping the rows on Sheet2 to the correct columns on Sheet1.

^{1 - I use ; as the argument separator, which is due to a regional setting. Will reformat later.}