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 ")
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.
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.