google-sheetsformulaarray-formulasgoogle-forms

Collapsing Wide Data into Tall Data in Google Sheets

I have a Google Sheets workbook that captures input from a Google form. The workbook contains two sheets:

  1. WIDE DATA (which captures input from the Google Form)
  2. TALL DATA (which is used to restructure and simplify the form input)

The WIDE DATA sheet contains a series of nine column headers:

1st Funding Request | Funding Amount | Additional Details | 2nd Funding Request | Funding Amount | Additional Details | 3rd Funding Request | Funding Amount | Additional Details

These same nine column headers repeat in eleven sequences, from S1 through DM1.

The TALL DATA sheet contains only one sequence of those nine column headers, from G1 through O1.

Each sequence of nine column headers, as described above, contains three segments:

If the WIDE DATA sheet contains data on a particular row underneath any specific segment of any particular sequence, what formula would I use to FLATTEN it onto the corresponding row underneath the corresponding segment on the TALL DATA sheet?

So far, the closest I've come to a solution is with this formula:

=ARRAYFORMULA(TRANSPOSE(FLATTEN(IF('WIDE DATA'!$S2:$DM2 <> "", 'WIDE DATA'!$S2:$DM2, ""))))

The issue with the above formula is that it returns data sequences / segments from WIDE DATA to TALL DATA in the same relative position that they're found on the WIDE DATA sheet.

The goal is to return each data sequence as described above to the corresponding row on TALL DATA only within columns G through O. WIDE DATA should contain data within only one sequence of the eleven sequences on each row of that sheet, so there should be no issue with overlap.

You can view the test workbook here: TESTS: Contractor Funding Request (Simplified)

Solution

  • Here's one possible approach you may test out:

    =reduce(tocol(,1),sequence(counta('WIDE DATA'!A2:A)),lambda(a,c,vstack(a,hstack(
       chooserows('WIDE DATA'!A2:E,c),
       torow(chooserows('WIDE DATA'!F2:R,c),1),
       let(Σ,chooserows('WIDE DATA'!S2:DM,c),if(counta(Σ)=0,wraprows(,9,),choosecols(Σ,sequence(9,1,index(xmatch("?*",to_text(Σ),2)))))),
       chooserows('WIDE DATA'!DN2:EH,c)))))

    enter image description here