VSTACK not ignoring blanks

[ollyhughes1982]

Hi,

I have a VSTACK formula, that had up until now appeared to have been working. However, now that my data has changed slightly I have encountered an issue. My formula is as below:

=IFERROR(SORT(VSTACK(FILTER('All Completed Runs - EC (SA)'!A4:A2003,'All Completed Runs - EC (SA)'!A4:A2003<>""),FILTER('All Completed Runs - FS (SA)'!A4:A2003,'All Completed Runs - FS (SA)'!A4:A2003<>""),FILTER('All Completed Runs - GT (SA)'!A4:A2003,'All Completed Runs - GT (SA)'!A4:A2003<>""),FILTER('All Completed Runs - KZN (SA)'!A4:A2003,'All Completed Runs - KZN (SA)'!A4:A2003<>""),FILTER('All Completed Runs - LI R (SA)'!A4:A2003,'All Completed Runs - LI R (SA)'!A4:A2003<>""),FILTER('All Completed Runs - MP R (SA)'!A4:A2003,'All Completed Runs - MP R (SA)'!A4:A2003<>""),FILTER('All Completed Runs - NW R (SA)'!A4:A2003,'All Completed Runs - NW R (SA)'!A4:A2003<>""),FILTER('All Completed Runs - NC R (SA)'!A4:A2003,'All Completed Runs - NC R (SA)'!A4:A2003<>""),FILTER('All Completed Runs - WC (SA)'!A4:A2003,'All Completed Runs - WC (SA)'!A4:A2003<>"")),,,-1),"")

I thought that I had catered for ranges with no data via the <>"" in each filter function, but it doesn't seem to be working. Now that KZN (SA)'!A4:A2003 and NW R (SA)'!A4:A2003 don't currently have any data, the formula is not working. Do I need some sort filter wrapped around the entire thing, perhaps before the '(VSTACK' part of the formula?

If I remove the IFERROR function, I can see that it's a CALC error that I'm getting.

Thanks in advance!

Olly.

 

[Fluff]

Stack the data & then filter it like

=let(v,VSTACK('All Completed Runs - EC (SA)'!A4:A2003,'All Completed Runs - FS (SA)'!A4:A2003,'All Completed Runs - GT (SA)'!A4:A2003)SORT(FILTER(v,v<>""),,-1))

 

[Anonymous1378]

Try using the third argument in each of the FILTER functions, which will be useful for when a particular filter range has completely no data at all.
You might want to wrap the entire VSTACK with another FILTER to exclude blanks, probably after assigning the result of the VSTACK function to a variable in LET.

Perhaps something like

IFERROR(SORT(LET(_a,VSTACK(FILTER('All Completed Runs - EC (SA)'!A4:A2003,'All Completed Runs - EC (SA)'!A4:A2003<>"",""),FILTER('All Completed Runs - FS (SA)'!A4:A2003,'All Completed Runs - FS (SA)'!A4:A2003<>"",""),FILTER('All Completed Runs - GT (SA)'!A4:A2003,'All Completed Runs - GT (SA)'!A4:A2003<>"",""),FILTER('All Completed Runs - KZN (SA)'!A4:A2003,'All Completed Runs - KZN (SA)'!A4:A2003<>"",""),FILTER('All Completed Runs - LI R (SA)'!A4:A2003,'All Completed Runs - LI R (SA)'!A4:A2003<>"",""),FILTER('All Completed Runs - MP R (SA)'!A4:A2003,'All Completed Runs - MP R (SA)'!A4:A2003<>"",""),FILTER('All Completed Runs - NW R (SA)'!A4:A2003,'All Completed Runs - NW R (SA)'!A4:A2003<>"",""),FILTER('All Completed Runs - NC R (SA)'!A4:A2003,'All Completed Runs - NC R (SA)'!A4:A2003<>"",""),FILTER('All Completed Runs - WC (SA)'!A4:A2003,'All Completed Runs - WC (SA)'!A4:A2003<>"")),FILTER(_a,_a<>"")),,,-1),"")

 

[ollyhughes1982]

Stack the data & then filter it like

=let(v,VSTACK('All Completed Runs - EC (SA)'!A4:A2003,'All Completed Runs - FS (SA)'!A4:A2003,'All Completed Runs - GT (SA)'!A4:A2003)SORT(FILTER(v,v<>""),,-1))

Thanks. I am trying the below formula (which includes all of the ranges), but I'm getting an error. Have I made a typo?

=let(v,VSTACK('All Completed Runs - EC (SA)'!A4:A2003,'All Completed Runs - FS (SA)'!A4:A2003,'All Completed Runs - GT (SA)'!A4:A2003,'All Completed Runs - KZN (SA)'!A4:A2003,'All Completed Runs - LI R (SA)'!A4:A2003,'All Completed Runs - MP R (SA)'!A4:A2003,'All Completed Runs - NW R (SA)'!A4:A2003,'All Completed Runs - NC R (SA)'!A4:A2003,'All Completed Runs - WC (SA)'!A4:A2003)SORT(FILTER(v,v<>""),,-1))

Thanks.

 

[Fluff]

It's missing a comma before the Sort.

 

[ollyhughes1982]

It's missing a comma before the Sort.

Great, that's working now. Thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.