Rearrange columns in Google Sheets

Question

I have data in Google Sheets that I want to rearrange.

In each row, starting from the 4th column onwards, there are repeating column pairs of Favorite Fruit # & Favorite Color # where # indicates the index of the pair relative to other pairs. For example, Favorite Fruit 1 and Favorite Color 1 are followed by Favorite Fruit 2 and Favorite Color 2, etc.

1. I would like to reorganize the data so that each column pair is in its row.
2. The column pairs in all rows share the same two columns.
3. The first three columns from the original row that contained a particular column pair are repeated on the new rows where that column pair is now located.

I can't figure out how to do it.

For example:

Date	Class	Name	Fruit 1	Color 1	Fruit 2	Color 2
2/27/2024	Class 1	Alice	Apple	Red	Grapes	Black

Would become:

Date	Class	Name	Fruit	Color
2/27/2024	Class 1	Alice	Apple	Red
2/27/2024	Class 1	Alice	Grapes	Black

Answer 1

Formula

=LET(mRng,A2:C, dByX,2, dRng,D2:G, mCols,COLUMNS(mRng),
   mRows,MAX(FILTER(ROW(mRng), LEN(INDEX(mRng,,1)))),
   idx,TOCOL(MAKEARRAY(mRows, COLUMNS(dRng)/dByX, LAMBDA(r, c, r))),
   mArr,BYROW(idx, LAMBDA(r, INDEX(mRng, r))),  
   dArr,WRAPROWS(TOROW(QUERY(dRng, "LIMIT "&mRows)), dByX),
   qryTxt,JOIN(" OR ", ARRAYFORMULA("Col" & 
     SEQUENCE(1, dByX, mCols+1) & " <> ''")),
   QUERY({mArr,dArr}, "WHERE "& qryTxt))

Explanation

1. The LET function is used to enable the storing of values and intermediary calculations in arbitrarily named variables for reuse.
2. mRng stores the meta range (columns A to C), which contains the values common to each row's column pairs. Width of mRng can be any non-zero number of columns.
3. dByX stores the number of data columns to group by. For your column pairs, that would be a group of 2. This variable makes the formula more extensible should your needs change as you could wrap by any number of columns you want.
4. dRng contains the column groups data range (columns D to G in your example). Width can be expanded/contracted to any non-zero multiple of dByX. For your column pairs, that would be 2, 4, 6, etc.
5. mCols calculates the column count for mRng.
6. mRows returns the number of the last populated row in column 1 of mRng to be used to constrain the range's:
   1. FILTER is applied to an array of mRng's row numbers.
   2. LEN is used for the filter condition to return only those rows that contain a non-zero-length value.
   3. MAX is then applied to the results to return the highest row number.
7. idx stores a single-column array of row indexes used later to retrieve the correct meta row from mRng. idx is created as follows:
   1. MAKEARRAY is used to create a 3-dimensional array that contains the same number of rows as mRows and the same number of columns as there are column groups in dRng using the formula COLUMNS(dRng)/dByX.
      1. For each position in the new array, MAKEARRAY passes its position (row number and column number), one-by-one, into a LAMBDA function that stores them in r and c respectively.
      2. The LAMBDA's formula returns the current row number r in every position of the array (column number is completely ignored.
         
         For example, where x is any number, if the current position is any of R1C1:R1Cx the formula always returns 1, for R3C1:R3Cx it would be 3, etc. Here is an example 2 x 3 array that MAKEARRAY could return

         | 1 | 1 | 1 |
         | 2 | 2 | 2 |
         | 3 | 3 | 3 |
         

   2. TOCOL is used to transform the 3-dimensional array returned by MAKEARRAY into a single-column array. idx is now an array of numbers that match the correct row index of mRng that should go with each new row created from the column groups in mRng.
8. mArr returns a meta array built using BYROW:
   1. BYROW passes each value from idx, one-by-one, into a LAMBDA function that stores the current value in r.
   2. The LAMBDA's formula uses INDEX to return the row in mRng whose row index is equal to r.
   3. This is repeated until BYROW has passed in every value from idx.
9. dArr returns a data array built using WRAPROWS:
   1. QUERY is first used to constrain the open range dRng to mRows using the LIMIT parameter.
   2. The array returned by QUERY is transformed by TOROW to a single row containing all the column groups.
   3. WRAPROWS transforms the single-row into a dByX-column array by wrapping it after every dByX columns (2 in your example). For example,
      This row:

      | Apple | Red | Grape | Blue | Plum | Taupe |
      

      Would be wrapped like this:

      | Apple | Red   |
      | Grape | Blue  |
      | Plum  | Taupe |
      

10. Another QUERY will be used to combine the two arrays and remove any rows that don't include a fruit or color (in case the 2nd, 3rd, etc. choices are not required fields). To this end, qryTxt returns a text string to be used in that final QUERY. It's created dynamically to allow for different values of dByX:
    1. SEQUENCE is used to create an array of column numbers starting from column mCols+1 (the first data column after the last meta column), and including dByX columns. For example, in your example mCols=3 and dByX=2 so the array of column numbers would be {4;5}. In other words 2 columns, 4 and 5.
    2. Inside an ARRAYFORMULA, "Col" is prepended to each column number, and " <> ''" is appended. Transforming the array to {"Col4 <> ''";"Col5 <> ''"}
    3. JOIN is then used to convert the array to a single text string with each array value now delimited by " OR ". So, the new text string is:
       "Col4 <> '' OR Col5 <> ''"
11. The final QUERY combines and returns the meta and data arrays {mArr, dArr} using qryTxt as the query in order to skip any rows that don't have values in at least one of the data columns.