Unique Distinct List with Criteria

[Jnrrpg11]

Hello All,

I think I'm quite close with this one but I'm drawing a blank.

I have a list like the one below only much bigger. For the example here i need a Unique Distinct list of just Meat and Vegetable.

The formula i have is

=INDEX($B$3:$B$17,MATCH(0,IF(OR(="Meat",="Vegetable")$C$3:$C17,COUNTIF($E$2:$E$2,$B$3:$B$17),""),0))

Im think maybe my issue lies between the red brackets as I'm not telling Excel what should equal "Meat" or "Vegetable".
Is there supposed to be a cell reference before the "=" and is it supposed to be the top cell in the table e.g. $E3 without an absolute on Row Number?

Any help is greatly appreciated

Richard

 

[a_songaila]

In all ways these brackets (="Meat",="Vegetable") gives the TRUE conditicion, correct? What answer you get from formula? And what you need to get?

-Follow me on Twitter @jakeromcod

 

[Marcelo Branco]

It seems there are no duplicates in B3:B17, so try this array formula

E3 copied down
=IFERROR(INDEX(B$3:B$17,SMALL(IF((C$3:C$17="Meat")+(C$3:C$17="Vegetable"),ROW(B$3:B$17)-ROW(B$3)+1),ROWS(E$3:E3))),"")
Ctrl+Shift+Enter

M.

 

[Jnrrpg11]

Excel won't let me commit the Formula saying there is an error and then diverts to this screen suggesting it is with the OR function.



So essentially I need it to use "Meat" and "Vegetable" as a trigger to add to the list(which would then leave everything under "Fruit" OFF the list.)

I know it would probably be easier to Not include "fruit" instead of including "Meat" and "Vegetable" but the actual list I'm working has many categories and I only need 2 of them.

Hope this sheds a bit of light on the situation.

 

[Marcelo Branco]

Have you tried the array formula in post #3 ?

M.

 

[Jnrrpg11]

Hey Marcelo,

Thanks for your reply, the formula works perfectly so thanks for taking the time to post that.

It also highlights a serious flaw in my example as my actual list does indeed have multiples so my apologies for that.

Is there an edit that could turn your formula into a unique distinct list?

Ive included maybe a better example with 2 small changes.

 

[Marcelo Branco]

Try

Array formula in E3 copied down
=IFERROR(INDEX(B$3:B$17,MATCH(0,IF((C$3:C$17="Meat")+(C$3:C$17="Vegetable"),COUNTIF(E$2:E2,B$3:B$17)),0)),"")
Ctrl+Shift+Enter

To use an OR condition in an array formula you should add the conditions.

M.

 

[Jnrrpg11]

Thanks for this.

I think i was getting bogged down with the OR function but this works perfectly.

This one is solved.


Thanks Again and Kind Regards

Richard

 

[Marcelo Branco]

You are welcome. Glad to help.

M.