0

I am using a query to import the data from multiple sheets, however, the formula is skipping empty cells. Is it possible to not skip empty cells and keep the data in the correct cell on the Master? Here is formula I am using:

=query({
'nff1'!A2:A;
'nff2'!A2:A;
'nff3'!A2:A;
'nff4'!A2:A;
'nff5'!A2:A;
'nff6'!A2:A;
'nff7'!A2:A},
"Select * where Col1 is not null ")
1
1

To keep all rows, including completely blank rows, just leave out the query() wrapper, like this:

={ nff1!A2:A; nff2!A2:A; nff3!A2:A; nff4!A2:A; nff5!A2:A; nff6!A2:A; nff7!A2:A }

To merge the data, including phone numbers, use an { array expression } like this:

=query( 
  { 
    { 
      'ff1'!G2:G;
      'ff2'!G2:G;
      'ff3'!A2:A;
      'ff4'!E2:E 
    }, 
    { 
      'ff1'!A2:A;
      'ff2'!A2:A;
      'ff3'!B2:B;
      'ff4'!A2:A 
    }, 
    { 
      'ff1'!C2:C;
      'ff2'!C2:C;
      'ff3'!C2:C;
      'ff4'!B2:B 
    }, 
    { 
      'ff1'!X2:X;
      'ff2'!D2:D;
      'ff3'!D2:D;
      'ff4'!X2:X 
    } 
  },
  "where Col1 is not null", 
  0 
)

See the new Solution sheet in your sample spreadsheet.

Going forward, you may want to standardize the layout on individual sheets.

4
  • Or, if the OP wants the query for some reason, either delete the entire select clause OR delete the where clause. Either way, as you point out, there is a big risk of large gaps between data if there are lots of blank rows at the bottom of sheets.
    – Tedinoz
    Oct 26 at 1:05
  • not working still Oct 26 at 3:50
  • I need a phone but it's not coming right don't leave empty cell where no entry here is my sheet link docs.google.com/spreadsheets/d/… Oct 26 at 4:04
  • Edited the answer. If you need more help, show your hand-entered expected results in the sample spreadsheet. Oct 26 at 8:55

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.