UNIQUE-VSTACK function and removing unwanted 0

[thewiseguy]

Hi all,

Currently using the following formula:

=UNIQUE(VSTACK(Input!R4:R503,Input!W4:W503,Input!Y4:Y503,Input!AA4:AA503,Input!AC4:AC503,Input!AE4:AE503))

Formula is working fine but giving me a 0 as one of the entries. I can't seem to find where this is coming from (not a visible 0 anywhere).

Can I add text to this formula so it does not give me the 0 as part of the output?

Any help is greatly appreciated!

 

[StephenCrump]

What happens if you try:

=LET(u,UNIQUE(VSTACK(Input!R4:R503,Input!W4:W503,Input!Y4:Y503,Input!AA4:AA503,Input!AC4:AC503,Input!AE4:AE503)),FILTER(u,u<>""))

 

[thewiseguy]

worked perfectly! thank you.

 

[Cubist]

You can also use TOCOL to remove blanks (not empty strings).

=TOCOL(UNIQUE(VSTACK(Input!R4:R503,Input!W4:W503,Input!Y4:Y503,Input!AA4:AA503,Input!AC4:AC503,Input!AE4:AE503)),1)