Remove duplicates with formula where data meets two or more critera!! ahh!!

[robbenjmain]

Hi Guys, I am having real trouble getting the second criteria in to my formula. Effectively I want to do the following (which works):


Return list of unique values in K where N1 appears in column E.

=IFERROR(INDEX($K$2:$K$3000, MATCH(0, IF($N$1=$E$2:$E$3000, COUNTIF($O$1:$O1, $K$2:$K$3000), ""), 0)),"")

but I am trying to add this so I have two searches.

=IFERROR(INDEX($K$2:$K$3000, MATCH(0, IF(AND($N$1=$E$2:$E$3000,$N$2=$L$2:$L$3000), COUNTIF($O$1:$O6, $K$2:$K$3000), ""), 0)),"")

But the if and function just returns blanks.

I would really appreciate any help with this. I am trying not to do it with filters as there is a ton of people accessing and I want them to all just be able to select from a dropdown on N1 and N2.

Many thanks

Rob

 

[Eric W]

Welcome to the forum.

You usually can't use AND in array formulas, since it tends to evaluate at the wrong point in the formula. Instead use Boolean operators to simulate an AND. * (times) is equivalent to AND, and + (plus) is equivalent to OR. So try:

=IFERROR(INDEX($K$2:$K$3000, MATCH(0, IF(($N$1=$E$2:$E$3000)*($N$2=$L$2:$L$3000), COUNTIF($O$1:$O6, $K$2:$K$3000), ""), 0)),"")

 

[robbenjmain]

WOWOWZAZ

Thanks so much! Super fast and it works perfectly. I have seen that "boolean" thing before but had no idea what it was so will look more in to it. For now though this is great, thanks very much!

Rob

 

[Aladin Akyurek]

In N3 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(1-(Krange=""),IF(Erange=$N$1,IF(Lrange=$N$2,MATCH(Krange,Krange,0)))),ROW(Krange)-ROW(INDEX(Krange,1,1))+1),1))

In O1 control+shift+enter, not just enter, and copy down:

=IF(ROWS($O$1:O1)>$N$3,"",INDEX(Krange,SMALL(IF(FREQUENCY(1-(Krange=""),IF(Erange=$N$1,IF(Lrange=$N$2,MATCH(Krange,Krange,0)))),ROW(Krange)-ROW(INDEX(Krange,1,1))+1),ROW(Krange)-ROW(INDEX(Krange,1,1))+1),ROWS($O$1:O1)))

 

[Aladin Akyurek]

In N3 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(1-(Krange=""),IF(Erange=$N$1,IF(Lrange=$N$2,MATCH(Krange,Krange,0)))),ROW(Krange)-ROW(INDEX(Krange,1,1))+1),1))

In O1 control+shift+enter, not just enter, and copy down:

=IF(ROWS($O$1:O1)>$N$3,"",INDEX(Krange,SMALL(IF(FREQUENCY(1-(Krange=""),IF(Erange=$N$1,IF(Lrange=$N$2,MATCH(Krange,Krange,0)))),ROW(Krange)-ROW(INDEX(Krange,1,1))+1),ROW(Krange)-ROW(INDEX(Krange,1,1))+1),ROWS($O$1:O1)))

Edit:

=SUM(IF(FREQUENCY(IF(1-(Krange=""),IF(Erange=$N$1,IF(Lrange=$N$2,MATCH(Krange,Krange,0)))),ROW(Krange)-ROW(INDEX(Krange,1,1))+1),1))

=IF(ROWS($O$1:O1)>$N$3,"",INDEX(Krange,SMALL(IF(FREQUENCY(1-(Krange=""),IF(Erange=$N$1,IF(Lrange=$N$2,MATCH(Krange,Krange,0)))),ROW(Krange)-ROW(INDEX(Krange,1,1))+1),ROW(Krange)-ROW(INDEX(Krange,1,1))+1),ROWS($O$1:O1)))