Error while getting Unique Values using let & Stack

[ravi2628]

Dear Friends,

good morning, afternoon, night

i was using a formula to get the unique array Values from multiple columns using Let & stack.

so please me out how to resolve the error.

=LET(v,VSTACK(a!F3:G27,b!F3:G26,'c'!F3:G27,d!F3:G26),UNIQUE(FILTER(v,v<>"")))

Error: #value!

Regards,
Ravi

 

[Joe4]

I think the issue may be the condition on your filter.
Your data is returning two column (F and G), so in your filter, I believe you need to designate which of those two column you want to check for your condition of not being empty.

Out of curiosity, what happens when you try it without the filter, i.e.

=LET(v,VSTACK(a!F3:G27,b!F3:G26,'c'!F3:G27,d!F3:G26),UNIQUE(v))

 

[Joe4]

If you can just use column F to filter out the blanks, then you could use this formula to get what you want:

=UNIQUE(VSTACK(FILTER(a!F3:G27,a!F3:F27<>""),FILTER(b!F3:G26,b!F3:F26<>""),FILTER('c'!F3:G27,'c'!F3:F27<>""),FILTER(d!F3:G26,d!F3:F26<>"")))

 

[ravi2628]

If you can just use column F to filter out the blanks, then you could use this formula to get what you want:

=UNIQUE(VSTACK(FILTER(a!F3:G27,a!F3:F27<>""),FILTER(b!F3:G26,b!F3:F26<>""),FILTER('c'!F3:G27,'c'!F3:F27<>""),FILTER(d!F3:G26,d!F3:F26<>"")))

thank you joe your logic works fine

 

[Joe4]

You are welcome.
Glad I was able to help!