getting #Value error when using FILTER to remove blank rows with VSTACK

[Glainer]

I can't figure out why I get a #Value error when using Filter to remove blank rows. the vstack works fine without the filter, and I have used the filter the same way before and it worked, but not this time. Please help?

=FILTER(VSTACK('TsysInvoiceData:TsysInvoiceData (8)'!A2:K934),VSTACK('TsysInvoiceData:TsysInvoiceData (8)'!A2:K934)<>"")

Thank you!
gg

 

[Alex Blakenburg]

The filter function is not happy that you have specified multiple columns in the filter condition.
Do you need to check all columns for blank or can you just use one column to use as a criteria ?

eg this will work

=FILTER(VSTACK('TsysInvoiceData:TsysInvoiceData (8)'!A2:K934),VSTACK('TsysInvoiceData:TsysInvoiceData (8)'!A2:A934)<>"")

ChooseColumns in the filter condition will work too.
(If you use ChooseColumns then Vstack is common and you could use the Let command so you only refer to it the once.)

=FILTER(VSTACK('TsysInvoiceData:TsysInvoiceData (8)'!A2:K934), CHOOSECOLS(VSTACK('TsysInvoiceData:TsysInvoiceData (8)'!A2:K934), 1)<>"")

 

[Glainer]

both of those solutions worked! thank you so much!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

PS: with the ChooseCols version you can reduce it to this:

=LET(vs,VSTACK('TsysInvoiceData:[TsysInvoiceData (8)]TsysInvoiceData (8)'!A2:K934),
FILTER(vs,CHOOSECOLS(vs,1)<>""))

 

[Glenn Kennedy]

I normally use INDEX instead...


=LET(vs,VSTACK('TsysInvoiceData:[TsysInvoiceData (8)]TsysInvoiceData (8)'!A2:K934),
FILTER(vs,INDEX(vs,,1)<>""))