UNIQUE/VSTACK/FILTER

[thewiseguy]

Hi everyone,

I am having problems with this formula and was hoping someone could spot the problem:

=LET(u,UNIQUE(VSTACK(SORT(FILTER('BOM (Products)'!B7:B50,SORT('BOM (Rental)'!B7:B26))))),FILTER(u,u<>""))

I am getting a #VALUE! error but I believe there is an error in my formula.

Any help is appreciated!

 

[HongRu]

I believe there is at least more than one mistake.
First, B7:B50 must correspond one-to-one with B7:B26. That is, maybe 'BOM (Rental)'!B7:B26 should be modified to 'BOM (Rental)'!B7:B50 .
Second, usually the second argument of the filter function does not use SORT, but instead uses something like 'BOM (Rental)'!B7:B50 = 1.
Third, VSTACK is clearly redundant here.

 

[Fluff]

Is this what you want

=LET(u,UNIQUE(VSTACK('BOM (Products)'!B7:B50,'BOM (Rental)'!B7:B26)),SORT(FILTER(u,u<>"")))

 

[thewiseguy]

Is this what you want

=LET(u,UNIQUE(VSTACK('BOM (Products)'!B7:B50,'BOM (Rental)'!B7:B26)),SORT(FILTER(u,u<>"")))

thank you fluff - as always, right on target!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

thewiseguy,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.