Need a true/false or 1/0 for if a given index has a specific set of values across multiple rows

[Blizzisme]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Users[/TD]
[TD]Colors[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD]Blue
[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD]Pink[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD]Pink[/TD]
[/TR]
[TR]
[TD]User 3[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]User 4[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]User 4[/TD]
[TD]Pink[/TD]
[/TR]
[TR]
[TD]User 4[/TD]
[TD]Green[/TD]
[/TR]
</tbody>[/TABLE]

I have another table which has a list of users and I want it to say yes/no as to whether they have the full set of colors (Blue, Pink, AND Green).

i.e.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Users[/TD]
[TD]Full Color Allocation[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]User 3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]User 4[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

What formula do I use to parse the first table to fill in the second one?


Thanks.

 

[Scott Huish]

Assuming each user will appear only 1 with each individual color:

=SUM(COUNTIFS(A:A,E1,B:B,{"Blue","Pink","Green"}))=3

Excel 2010
	A	B	C	D	E	F
1	User 1	Blue			User 1	TRUE
2	User 1	Pink			User 2	FALSE
3	User 1	Green			User 3	FALSE
4	User 2	Blue			User 4	TRUE
5	User 2	Pink
6	User 3	Blue
7	User 4	Blue
8	User 4	Pink
Sheet1

Cell Formulas
Range	Formula
F1	=SUM(COUNTIFS(A:A,E1,B:B,{"Blue","Pink","Green"}))=3
F2	=SUM(COUNTIFS(A:A,E2,B:B,{"Blue","Pink","Green"}))=3
F3	=SUM(COUNTIFS(A:A,E3,B:B,{"Blue","Pink","Green"}))=3
F4	=SUM(COUNTIFS(A:A,E4,B:B,{"Blue","Pink","Green"}))=3

 

[Blizzisme]

Thanks Scott, that looks like what I need, now I just need to make it work.
I am testing with values that I know go together, and it is returning 0.
Please see the attached image, maybe you can see what I am doing wrong.

Also, before manually entering the search criteria, I tried to create a series of cells where I could put the patterns I wanted to match and it would not allow me to select a cell in place of a text string. Any way to make it work with something like "=SUM(COUNTIFS(A:A,E1,B:B,{L1,L2,L3}))=3"

 

[Blizzisme]

Just realised the row numbers were cut off. K11 is the cell my formula is in right now, and K10 is where I put the name of the user.

 

[Blizzisme]

The plot thickens... If I type in the name, and type all of the matching cases, then it returns the correct count. If I try and point it to other cells it returns 0 if I use the username that way, and gives an error if I try and point the matching cases to cells.

 

[Aladin Akyurek]

Try to post your sample in an Excel readable form directly here in the form along with the results which must obtain from that sample.

 

[Blizzisme]

Try to post your sample in an Excel readable form directly here in the form along with the results which must obtain from that sample.

Thank you all for your assistance. I updated my query to clear all formatting, and now the comparison works. Got everything running (more or less) now... The remaining problems are ones of logic. I need to actually figure out the workflow for what I want it to do.

Much appreciated.