Google-sheets – How to include blank cells when using an Array Formula

google sheetsgoogle-sheets-arrayformula

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:

And this is what it should look like:

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!

Best Answer

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.

Best Answer

Related Solutions

Google-sheets – Count blank cells in query formula

Google Sheets – Fixing ‘An Array Literal Was Missing Values for One or More Rows’ Error

Related Topic