0

I have a Google form that I'm using to collect info for user account creations. I wanted to make it so that the person filling out could just let me know about multiple users at an office in one entry instead of resubmitting and inputting the office info over and over. I was able to do some research and found a great post on here that helped me create an ArrayFormula to break a part the single rows into multiple rows for each user account. The only thing I need help with is there are a few cells that could potentially be left blank and when they are, the formula skips/shifts the data over, ignoring the blank cells. How/where do I add an If statement in order to get it to not shift/ignore the blank cells?

Here's what it currently looks like: enter image description here

And this is what it should look like: enter image description here Here's my crazy formula (it's so big because you can request up to 26 user accounts)

={'Form Responses'!B1:O1; 
ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN({
IF(LEN('Form Responses'!K2:K), "♣"&'Form Responses'!K2:K&"♣"&'Form Responses'!L2:L&"♣"&'Form Responses'!M2:M&"♣"&'Form Responses'!N2:N&"♣"&'Form Responses'!O2:O,), 
IF(LEN('Form Responses'!Q2:Q), "♣"&'Form Responses'!Q2:Q&"♣"&'Form Responses'!R2:R&"♣"&'Form Responses'!S2:S&"♣"&'Form Responses'!T2:T&"♣"&'Form Responses'!U2:U,),
IF(LEN('Form Responses'!W2:W), "♣"&'Form Responses'!W2:W&"♣"&'Form Responses'!X2:X&"♣"&'Form Responses'!Y2:Y&"♣"&'Form Responses'!Z2:Z&"♣"&'Form Responses'!AA2:AA,),
IF(LEN('Form Responses'!AC2:AC), "♣"&'Form Responses'!AC2:AC&"♣"&'Form Responses'!AD2:AD&"♣"&'Form Responses'!AE2:AE&"♣"&'Form Responses'!AF2:AF&"♣"&'Form Responses'!AG2:AG,),
IF(LEN('Form Responses'!AI2:AI), "♣"&'Form Responses'!AI2:AI&"♣"&'Form Responses'!AJ2:AJ&"♣"&'Form Responses'!AK2:AK&"♣"&'Form Responses'!AL2:AL&"♣"&'Form Responses'!AM2:AM,),
IF(LEN('Form Responses'!AO2:AO), "♣"&'Form Responses'!AO2:AO&"♣"&'Form Responses'!AP2:AP&"♣"&'Form Responses'!AQ2:AQ&"♣"&'Form Responses'!AR2:AR&"♣"&'Form Responses'!AS2:AS,),
IF(LEN('Form Responses'!AU2:AU), "♣"&'Form Responses'!AU2:AU&"♣"&'Form Responses'!AV2:AV&"♣"&Sheet1AW2:AW&"♣"&'Form Responses'!AX2:AX&"♣"&'Form Responses'!AY2:AY,),
IF(LEN('Form Responses'!BA2:BA), "♣"&'Form Responses'!BA2:BA&"♣"&'Form Responses'!BB2:BB&"♣"&'Form Responses'!BC2:BC&"♣"&'Form Responses'!BD2:BD&"♣"&'Form Responses'!BE2:BE,),
IF(LEN('Form Responses'!BG2:BG), "♣"&'Form Responses'!BG2:BG&"♣"&'Form Responses'!BH2:BH&"♣"&'Form Responses'!BI2:BI&"♣"&'Form Responses'!BJ2:BJ&"♣"&'Form Responses'!BK2:BK,),
IF(LEN('Form Responses'!BM2:BM), "♣"&'Form Responses'!BM2:BM&"♣"&'Form Responses'!BN2:BN&"♣"&'Form Responses'!BO2:BO&"♣"&'Form Responses'!BP2:BP&"♣"&'Form Responses'!BQ2:BQ,),
IF(LEN('Form Responses'!BS2:BS), "♣"&'Form Responses'!BS2:BS&"♣"&'Form Responses'!BT2:BT&"♣"&'Form Responses'!BU2:BU&"♣"&'Form Responses'!BV2:BV&"♣"&'Form Responses'!BW2:BW,),
IF(LEN('Form Responses'!BY2:BY), "♣"&'Form Responses'!BY2:BY&"♣"&'Form Responses'!BZ2:BZ&"♣"&'Form Responses'!CA2:CA&"♣"&'Form Responses'!CB2:CB&"♣"&'Form Responses'!CC2:CC,),
IF(LEN('Form Responses'!CE2:CE), "♣"&'Form Responses'!CE2:CE&"♣"&'Form Responses'!CF2:CF&"♣"&'Form Responses'!CG2:CG&"♣"&'Form Responses'!CH2:CH&"♣"&'Form Responses'!CI2:CI,),
IF(LEN('Form Responses'!CK2:CK), "♣"&'Form Responses'!CK2:CK&"♣"&'Form Responses'!CL2:CL&"♣"&'Form Responses'!CM2:CM&"♣"&'Form Responses'!CN2:CN&"♣"&'Form Responses'!CO2:CO,),
IF(LEN('Form Responses'!CQ2:CQ), "♣"&'Form Responses'!CQ2:CQ&"♣"&'Form Responses'!CR2:CR&"♣"&'Form Responses'!CS2:CS&"♣"&'Form Responses'!CT2:CT&"♣"&'Form Responses'!CU2:CU,),
IF(LEN('Form Responses'!CW2:CW), "♣"&'Form Responses'!CW2:CW&"♣"&'Form Responses'!CX2:CX&"♣"&'Form Responses'!CY2:CY&"♣"&'Form Responses'!CZ2:CZ&"♣"&'Form Responses'!DA2:DA,),
IF(LEN('Form Responses'!DC2:DC), "♣"&'Form Responses'!DC2:DC&"♣"&'Form Responses'!DD2:DD&"♣"&'Form Responses'!DE2:DE&"♣"&'Form Responses'!DF2:DF&"♣"&'Form Responses'!DG2:DG,),
IF(LEN('Form Responses'!DI2:DI), "♣"&'Form Responses'!DI2:DI&"♣"&'Form Responses'!DJ2:DJ&"♣"&'Form Responses'!DK2:DK&"♣"&'Form Responses'!DL2:DL&"♣"&'Form Responses'!DM2:DM,),
IF(LEN('Form Responses'!DO2:DO), "♣"&'Form Responses'!DO2:DO&"♣"&'Form Responses'!DP2:DP&"♣"&'Form Responses'!DQ2:DQ&"♣"&'Form Responses'!DR2:DR&"♣"&'Form Responses'!DS2:DS,),
IF(LEN('Form Responses'!DU2:DU), "♣"&'Form Responses'!DU2:DU&"♣"&'Form Responses'!DV2:DV&"♣"&'Form Responses'!DW2:DW&"♣"&'Form Responses'!DX2:DX&"♣"&'Form Responses'!DY2:DY,),
IF(LEN('Form Responses'!EA2:EA), "♣"&'Form Responses'!EA2:EA&"♣"&'Form Responses'!EB2:EB&"♣"&'Form Responses'!EC2:EC&"♣"&'Form Responses'!ED2:ED&"♣"&'Form Responses'!EE2:EE,),
IF(LEN('Form Responses'!EG2:EG), "♣"&'Form Responses'!EG2:EG&"♣"&'Form Responses'!EH2:EH&"♣"&'Form Responses'!EI2:EI&"♣"&'Form Responses'!EJ2:EJ&"♣"&'Form Responses'!EK2:EK,),
IF(LEN('Form Responses'!EM2:EM), "♣"&'Form Responses'!EM2:EM&"♣"&'Form Responses'!EN2:EN&"♣"&'Form Responses'!EO2:EO&"♣"&'Form Responses'!EP2:EP&"♣"&'Form Responses'!EQ2:EQ,),
IF(LEN('Form Responses'!ES2:ES), "♣"&'Form Responses'!ES2:ES&"♣"&'Form Responses'!ET2:ET&"♣"&'Form Responses'!EU2:EU&"♣"&'Form Responses'!EV2:EV&"♣"&'Form Responses'!EW2:EW,),
IF(LEN('Form Responses'!EY2:EY), "♣"&'Form Responses'!EY2:EY&"♣"&'Form Responses'!EZ2:EZ&"♣"&'Form Responses'!FA2:FA&"♣"&'Form Responses'!FB2:FB&"♣"&'Form Responses'!FC2:FC,),
IF(LEN('Form Responses'!FE2:FE), "♣"&'Form Responses'!FE2:FE&"♣"&'Form Responses'!FF2:FF&"♣"&'Form Responses'!FG2:FG&"♣"&'Form Responses'!FH2:FH&"♣"&'Form Responses'!FI2:FI,)}),

"♦"&'Form Responses'!B2:B&"♣"&'Form Responses'!C2:C&"♣"&'Form Responses'!D2:D&"♣"&'Form Responses'!E2:E&"♣"&'Form Responses'!F2:F&"♣"&'Form Responses'!G2:G&"♣"&'Form Responses'!H2:H&"♣"&'Form Responses'!I2:I&"♣"&'Form Responses'!J2:J&{ 
IF(LEN('Form Responses'!K2:K), "♣"&'Form Responses'!K2:K&"♣"&'Form Responses'!L2:L&"♣"&'Form Responses'!M2:M&"♣"&'Form Responses'!N2:N&"♣"&'Form Responses'!O2:O,), 
IF(LEN('Form Responses'!Q2:Q), "♣"&'Form Responses'!Q2:Q&"♣"&'Form Responses'!R2:R&"♣"&'Form Responses'!S2:S&"♣"&'Form Responses'!T2:T&"♣"&'Form Responses'!U2:U,),
IF(LEN('Form Responses'!W2:W), "♣"&'Form Responses'!W2:W&"♣"&'Form Responses'!X2:X&"♣"&'Form Responses'!Y2:Y&"♣"&'Form Responses'!Z2:Z&"♣"&'Form Responses'!AA2:AA,),
IF(LEN('Form Responses'!AC2:AC), "♣"&'Form Responses'!AC2:AC&"♣"&'Form Responses'!AD2:AD&"♣"&'Form Responses'!AE2:AE&"♣"&'Form Responses'!AF2:AF&"♣"&'Form Responses'!AG2:AG,),
IF(LEN('Form Responses'!AI2:AI), "♣"&'Form Responses'!AI2:AI&"♣"&'Form Responses'!AJ2:AJ&"♣"&'Form Responses'!AK2:AK&"♣"&'Form Responses'!AL2:AL&"♣"&'Form Responses'!AM2:AM,),
IF(LEN('Form Responses'!AO2:AO), "♣"&'Form Responses'!AO2:AO&"♣"&'Form Responses'!AP2:AP&"♣"&'Form Responses'!AQ2:AQ&"♣"&'Form Responses'!AR2:AR&"♣"&'Form Responses'!AS2:AS,),
IF(LEN('Form Responses'!AU2:AU), "♣"&'Form Responses'!AU2:AU&"♣"&'Form Responses'!AV2:AV&"♣"&Sheet1AW2:AW&"♣"&'Form Responses'!AX2:AX&"♣"&'Form Responses'!AY2:AY,),
IF(LEN('Form Responses'!BA2:BA), "♣"&'Form Responses'!BA2:BA&"♣"&'Form Responses'!BB2:BB&"♣"&'Form Responses'!BC2:BC&"♣"&'Form Responses'!BD2:BD&"♣"&'Form Responses'!BE2:BE,),
IF(LEN('Form Responses'!BG2:BG), "♣"&'Form Responses'!BG2:BG&"♣"&'Form Responses'!BH2:BH&"♣"&'Form Responses'!BI2:BI&"♣"&'Form Responses'!BJ2:BJ&"♣"&'Form Responses'!BK2:BK,),
IF(LEN('Form Responses'!BM2:BM), "♣"&'Form Responses'!BM2:BM&"♣"&'Form Responses'!BN2:BN&"♣"&'Form Responses'!BO2:BO&"♣"&'Form Responses'!BP2:BP&"♣"&'Form Responses'!BQ2:BQ,),
IF(LEN('Form Responses'!BS2:BS), "♣"&'Form Responses'!BS2:BS&"♣"&'Form Responses'!BT2:BT&"♣"&'Form Responses'!BU2:BU&"♣"&'Form Responses'!BV2:BV&"♣"&'Form Responses'!BW2:BW,),
IF(LEN('Form Responses'!BY2:BY), "♣"&'Form Responses'!BY2:BY&"♣"&'Form Responses'!BZ2:BZ&"♣"&'Form Responses'!CA2:CA&"♣"&'Form Responses'!CB2:CB&"♣"&'Form Responses'!CC2:CC,),
IF(LEN('Form Responses'!CE2:CE), "♣"&'Form Responses'!CE2:CE&"♣"&'Form Responses'!CF2:CF&"♣"&'Form Responses'!CG2:CG&"♣"&'Form Responses'!CH2:CH&"♣"&'Form Responses'!CI2:CI,),
IF(LEN('Form Responses'!CK2:CK), "♣"&'Form Responses'!CK2:CK&"♣"&'Form Responses'!CL2:CL&"♣"&'Form Responses'!CM2:CM&"♣"&'Form Responses'!CN2:CN&"♣"&'Form Responses'!CO2:CO,),
IF(LEN('Form Responses'!CQ2:CQ), "♣"&'Form Responses'!CQ2:CQ&"♣"&'Form Responses'!CR2:CR&"♣"&'Form Responses'!CS2:CS&"♣"&'Form Responses'!CT2:CT&"♣"&'Form Responses'!CU2:CU,),
IF(LEN('Form Responses'!CW2:CW), "♣"&'Form Responses'!CW2:CW&"♣"&'Form Responses'!CX2:CX&"♣"&'Form Responses'!CY2:CY&"♣"&'Form Responses'!CZ2:CZ&"♣"&'Form Responses'!DA2:DA,),
IF(LEN('Form Responses'!DC2:DC), "♣"&'Form Responses'!DC2:DC&"♣"&'Form Responses'!DD2:DD&"♣"&'Form Responses'!DE2:DE&"♣"&'Form Responses'!DF2:DF&"♣"&'Form Responses'!DG2:DG,),
IF(LEN('Form Responses'!DI2:DI), "♣"&'Form Responses'!DI2:DI&"♣"&'Form Responses'!DJ2:DJ&"♣"&'Form Responses'!DK2:DK&"♣"&'Form Responses'!DL2:DL&"♣"&'Form Responses'!DM2:DM,),
IF(LEN('Form Responses'!DO2:DO), "♣"&'Form Responses'!DO2:DO&"♣"&'Form Responses'!DP2:DP&"♣"&'Form Responses'!DQ2:DQ&"♣"&'Form Responses'!DR2:DR&"♣"&'Form Responses'!DS2:DS,),
IF(LEN('Form Responses'!DU2:DU), "♣"&'Form Responses'!DU2:DU&"♣"&'Form Responses'!DV2:DV&"♣"&'Form Responses'!DW2:DW&"♣"&'Form Responses'!DX2:DX&"♣"&'Form Responses'!DY2:DY,),
IF(LEN('Form Responses'!EA2:EA), "♣"&'Form Responses'!EA2:EA&"♣"&'Form Responses'!EB2:EB&"♣"&'Form Responses'!EC2:EC&"♣"&'Form Responses'!ED2:ED&"♣"&'Form Responses'!EE2:EE,),
IF(LEN('Form Responses'!EG2:EG), "♣"&'Form Responses'!EG2:EG&"♣"&'Form Responses'!EH2:EH&"♣"&'Form Responses'!EI2:EI&"♣"&'Form Responses'!EJ2:EJ&"♣"&'Form Responses'!EK2:EK,),
IF(LEN('Form Responses'!EM2:EM), "♣"&'Form Responses'!EM2:EM&"♣"&'Form Responses'!EN2:EN&"♣"&'Form Responses'!EO2:EO&"♣"&'Form Responses'!EP2:EP&"♣"&'Form Responses'!EQ2:EQ,),
IF(LEN('Form Responses'!ES2:ES), "♣"&'Form Responses'!ES2:ES&"♣"&'Form Responses'!ET2:ET&"♣"&'Form Responses'!EU2:EU&"♣"&'Form Responses'!EV2:EV&"♣"&'Form Responses'!EW2:EW,),
IF(LEN('Form Responses'!EY2:EY), "♣"&'Form Responses'!EY2:EY&"♣"&'Form Responses'!EZ2:EZ&"♣"&'Form Responses'!FA2:FA&"♣"&'Form Responses'!FB2:FB&"♣"&'Form Responses'!FC2:FC,),
IF(LEN('Form Responses'!FE2:FE), "♣"&'Form Responses'!FE2:FE&"♣"&'Form Responses'!FF2:FF&"♣"&'Form Responses'!FG2:FG&"♣"&'Form Responses'!FH2:FH&"♣"&'Form Responses'!FI2:FI,)},))
,,999^99)),,999^99)), "♦")), "♣")))}

Here's the link to a copy of my spreadsheet

Thanks so much for any direction on this! :) I actually plan to use this as a base for several forms/sheets! super helpful!

1
  • Welcome. Would you be willing to change your formula? – marikamitsos Nov 1 '20 at 16:40
0

Try this formula:

=query({
query({
  "Type of request?","Practice Address","Practice City","Practice State","Practice Zip Code","Practice Phone","Enter Last Name","Enter First Name","Enter e-mail Address","Enter Cell Phone Number","Enter the User's Role at the practice";
  query({
    query('Form Responses'!A1:FJ3,"Select E, F, G, H, I, J, K, L, M, N, O where E contains 'New Practice'",0);
    ifna(query('Form Responses'!A1:FJ3,"Select E, F, G, H, I, J, Q,R,S,T,U where E contains 'New Practice' and P is NOT NULL and (P contains 'Yes')",0),{"","","","","","","","","","",""});
    ifna(query('Form Responses'!A1:FJ3,"Select E, F, G, H, I, J, Q,R,S,T,U where V is NOT NULL and (E contains 'New Practice' and V contains 'Yes')",0),{"","","","","","","","","","",""});
    ifna(query('Form Responses'!A1:FJ3,"Select E, F, G, H, I, J, AC, AD, AE, AF, AG where AB is NOT NULL and (E contains 'New Practice' and AB contains 'Yes')",0),{"","","","","","","","","","",""});
    ifna(query('Form Responses'!A1:FJ3,"Select E, F, G, H, I, J, W, AI, AJ, AK, AL, AM where AH is NOT NULL and (E contains 'New Practice' and AH contains 'Yes')",0),{"","","","","","","","","","",""});
    ifna(query('Form Responses'!A1:FJ3,"Select E, F, G, H, I, J, W, AI, AJ, AK, AL, AM where AH is NOT NULL and (E contains 'New Practice' and AH contains 'Yes')",0),{"","","","","","","","","","",""});
    ifna(query('Form Responses'!A1:FJ3,"Select E, F, G, H, I, J, W, AO, AP, AQ, AR, AS where AN is NOT NULL and (E contains 'New Practice' and AN contains 'Yes')",0),{"","","","","","","","","","",""});
    ifna(query('Form Responses'!A2:FJ4,"Select E, F, G, H, I, J, W, AU, AV, AW, AX, AY where AT is NOT NULL and (E contains 'New Practice' and AT contains 'Yes')",0),{"","","","","","","","","","",""});

    query('Form Responses'!A1:FJ3,"select E,' ','  ','   ','    ','     ', K, L, M, N, O where E contains 'existing' LABEL ' ' '', '  ' '', '   ' '', '    ' '', '     ' ''",0);
    IFNA(query('Form Responses'!A1:FJ3,"select E,' ','  ','   ','    ','     ', Q, R, S, T, U where  P is NOT NULL and  (E contains 'existing' and P contains'Yes') LABEL ' ' '', '  ' '', '   ' '', '    ' '', '     ' ''",0),{"","","","","","","","","","",""});
    IFNA(query('Form Responses'!A1:FJ3,"select E,' ','  ','   ','    ','     ', W, X, Y, Z, AA where  V is NOT NULL and  (E contains 'existing' and V contains 'Yes') LABEL ' ' '', '  ' '', '   ' '', '    ' '', '     ' ''",0),{"","","","","","","","","","",""});
    IFNA(query('Form Responses'!A1:FJ3,"select E,' ','  ','   ','    ','     ', AC, AD, AE, AF, AG where  AB is NOT NULL and  (E contains 'existing' and AB contains 'Yes') LABEL ' ' '', '  ' '', '   ' '', '    ' '', '     ' ''",0),{"","","","","","","","","","",""});
    IFNA(query('Form Responses'!A1:FJ3,"select E,' ','  ','   ','    ','     ', AI, AJ, AK, AL, AM where  AH is NOT NULL and  (E contains 'existing' and AH contains 'Yes') LABEL ' ' '', '  ' '', '   ' '', '    ' '', '     ' ''",0),{"","","","","","","","","","",""});
    IFNA(query('Form Responses'!A1:FJ3,"select E,' ','  ','   ','    ','     ', AO, AP, AQ, AR, AS where  AN is NOT NULL and  (E contains 'existing' and AN contains 'Yes') LABEL ' ' '', '  ' '', '   ' '', '    ' '', '     ' ''",0),{"","","","","","","","","","",""});
    IFNA(query('Form Responses'!A1:FJ3,"select E,' ','  ','   ','    ','     ', AU, AV, AW, AX, AY where  AT is NOT NULL and  (E contains 'existing' and AT contains 'Yes') LABEL ' ' '', '  ' '', '   ' '', '    ' '', '     ' ''",0),{"","","","","","","","","","",""}
  )}
   )}
    )},
"select * where Col1 is not null order by Col1 asc")

Logic

  • a set of bespoke headers:

    • "Type of request?","Practice Address"...
  • Two sets of query statements:

    • where E contains 'New Practice': 8 queries to evaluate each of the possible user details for a New Practise with New users
    • where E contains 'existing': 8 queries to evaluate each of the possible user details for an Existing Practise with new users
    • these include IFNA functions to manage a no-result outcome
  • a query on the result of the above which includes blank lines for the no-result outcomes.

    • select * where Col1 is not null order by Col1 asc: this eliminates the blank lines and also sorts the results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.