Google-sheets – How to filter several pages of data and ignore those that are blank in the process and return the rest

formulasgoogle sheetsgoogle-sheets-arrays

Each day I have several tabs that update different football leagues data from websites using importhtml. I filter each individual tab onto one separate tab. When one tab is empty and contains no data, it fails to return the others tabs data. Is there a way of asking this to check if its empty first or ignore that it is empty and continue to return the others? This is currently what I use which works when all tabs contain data. It doesn't when one tab is empty.

={Filter('Spain Review'!BN2:BQ15,'Spain Review'!$BN2:BN15<>"");Filter('French Review'!BN2:BQ15,'French Review'!$BN2:BN15<>"");Filter('German Review'!BN2:BQ15,'German Review'!$BN2:BN15<>"");Filter('Italian Review'!BN2:BQ15,'Italian Review'!$BN2:BN15<>"");Filter('Prem Review'!BN2:BQ15,'Prem Review'!$BN2:BN15<>"")}

Best Answer

Instead of using multiple FILTER functions, use only one:

=Filter({
'Spain Review'!BN2:BQ15;
'French Review'!BN2:BQ15;
'German Review'!BN2:BQ15;
'Italian Review'!BN2:BQ15;
'Prem Review'!BN2:BQ15
},{
'Spain Review'!$BN2:BN15<>"";
'French Review'!$BN2:BN15<>"";
'German Review'!$BN2:BN15<>"";
'Italian Review'!$BN2:BN15<>"";
'Prem Review'!$BN2:BN15<>""
})
Related Topic