Use VSTACK with other functions?

[smking204]

I just discovered VSTACK, and I love the concept, but I'm running into problems using it.

for instance, in K1 I have
=FILTER(VSTACK(A2:A35,B2:B35,C2:C35,D2:D35), VSTACK(A2:A35,B2:B35,C2:C35,D2:D35)<>"")
and this works as expected

and in L1 I have
=UNIQUE(FILTER(K1#,COUNTIF(K1#,K1#)>1))
and this works as expected

but when I try to combine them in one formula, replacing "K1#" with the formula in K1, like this
=UNIQUE(FILTER(FILTER(VSTACK(A2:A35,B2:B35,C2:C35,D2:D35), VSTACK(A2:A35,B2:B35,C2:C35,D2:D35)<>""),COUNTIF(FILTER(VSTACK(A2:A35,B2:B35,C2:C35,D2:D35), VSTACK(A2:A35,B2:B35,C2:C35,D2:D35)<>""),FILTER(VSTACK(A2:A35,B2:B35,C2:C35,D2:D35), VSTACK(A2:A35,B2:B35,C2:C35,D2:D35)<>""))>1))
I get the error saying, "there's a problem with this formula." (I did try toggling the VSTACK arguments to absolute, as well as relative, with no better results.)

Similarly, I have a conditional formatting rule
=MATCH(A2,UNIQUE(FILTER($A$2:$A$35,COUNTIF($A$2:$A$35,$A$2:$A$35)>1)),0)=1
that works as expected, but when I try to replace $A$2:$A$35 with a simple VSTACK, like this
=MATCH(A2,UNIQUE(FILTER(VSTACK($A$2:$A$35,$B$2:$B$35,$C$2:$C$35,$D$2:$D$35),COUNTIF(VSTACK($A$2:$A$35,$B$2:$B$35,$C$2:$C$35,$D$2:$D$35),VSTACK($A$2:$A$35,$B$2:$B$35,$C$2:$C$35,$D$2:$D$35))>1)),0)=1
I get the same error.

What am I missing?

Thanks!

 

[Vogateer]

I think this is caused by Excel treating ranges and arrays as different things, such that a range and an array are not interchangeable. For example, COUNTIF accepts ranges but absolutely will not work with arrays, and I think VSTACK produces an array, which explains why COUNTIF is returning an error. You would have to come up with an alternative way to achieve what COUNTIF does, and that's not a lot of fun.

If you can give us same sample data for copying and pasting, I might be able to help, but I'm not the best at coming up with alternatives to things like COUNTIF. I have done it before, but it was a lot of trial and error.

 

[MountainFog]

I think this is caused by Excel treating ranges and arrays as different things, such that a range and an array are not interchangeable. For example, COUNTIF accepts ranges but absolutely will not work with arrays, and I think VSTACK produces an array, which explains why COUNTIF is returning an error. You would have to come up with an alternative way to achieve what COUNTIF does, and that's not a lot of fun.

If you can give us same sample data for copying and pasting, I might be able to help, but I'm not the best at coming up with alternatives to things like COUNTIF. I have done it before, but it was a lot of trial and error.

That is correct. Alternatively, you can use =COUNT(FILTER())

 

[Fluff]

As has been said, countifs needs a range. How about

=LET(a,TOCOL(A2:D35,,1),f,FILTER(a,a<>""),UNIQUE(FILTER(f,MAP(f,LAMBDA(m,SUM(--(f=m))))>1)))

 

[smking204]

Ok, cool. So I don't have a solution yet, but I'm glad the problem is identified. It's strange, because it seems like a key benefit of VSTACK is to take an array and turn it into a range. Maybe I'm understanding those terms wrong. I'm interested in Fluff's suggestion, but I don't want to use it without understanding it, and I won't have time to parse it for a while. I'm content to use my workaround, with extra columns, until I can get my head around a substitute for COUNTIF. Thanks, y'all!

 

[Fluff]

Glad we could help & thanks for the feedback.