Non-unique first column, multiple matches in second column

[Iainn]

Hi all,

I'm sure the solution to this is easy, but I cannot work it out nor find the solution on the board.
I have 2 columns...

Staff	Element
A1	1z
A1	2y
A1	3x
A1	4w
A1	5v
A1	6u
A1	7t
A1	8s
B2	1z
B2	2y
B2	3x
B2	4w
B2	5v
B2	6u
B2	7t
B2	8s
C3	1z
C3	2y
C3	4w
C3	5v
C3	7t
C3	8s

What I need to do is:
For each staff member
- If they have element 1z, they must also have element 3x
- If they have 2y, they must have 4w
- If they have 5v, they must have 6u

So staff number C3 would fail as they do not have "1z and 3y" or "5v and 6u"

Thank you muchly for any help!

 

[Andrew Poulsom]

There's probably an easier way, but try:

=IF(SUMPRODUCT(--(A$2:A$23=A2),--(B$2:B$23="1z")),IF(SUMPRODUCT(--(A$2:A$23=A2),--(B$2:B$23="3x"))=0,"Fail",IF(SUMPRODUCT(--(A$2:A$23=A2),--(B$2:B$23="2y")),IF(SUMPRODUCT(--(A$2:A$23=A2),--(B$2:B$23="4w"))=0,"Fail",IF(SUMPRODUCT(--(A$2:A$23=A2),--(B$2:B$23="5v")),IF(SUMPRODUCT(--(A$2:A$23=A2),--(B$2:B$23="6u"))=0,"Fail","Pass"))))))
[/code]

 

[Iainn]

Thanks for the quick reply, your solution kind of works but the problem is that the employees don't need to have all 3, but can have a combination. For instance, if A1 didn't have element 2y or 5v, it should still pass because they aren't missing any of the matching pairs. If an employee had none of the matching pairs they would still pass.
They should only fail if they are missing a match.

 

[Andrew Poulsom]

How about?

=IF(SUMPRODUCT(--(A$2:A$23=A2),--((B$2:B$23="1z")+(B$2:B$23="3x")))=1,"Fail",IF(SUMPRODUCT(--(A$2:A$23=A2),--((B$2:B$23="2y")+(B$2:B$23="4w")))=1,"Fail",IF(SUMPRODUCT(--(A$2:A$23=A2),--((B$2:B$23="5v")+(B$2:B$23="6u")))=1,"Fail","Pass")))

It doesn't allow for a more than one of each element.

 

[Iainn]

Also, I need to run this over 100K+ rows and it is in need of some enhancement as the current one just killed Excel. Could there be a more efficient way of doing this?

 

[Iainn]

How about?

=IF(SUMPRODUCT(--(A$2:A$23=A2),--((B$2:B$23="1z")+(B$2:B$23="3x")))=1,"Fail",IF(SUMPRODUCT(--(A$2:A$23=A2),--((B$2:B$23="2y")+(B$2:B$23="4w")))=1,"Fail",IF(SUMPRODUCT(--(A$2:A$23=A2),--((B$2:B$23="5v")+(B$2:B$23="6u")))=1,"Fail","Pass")))

It doesn't allow for a more than one of each element.

Perfect. Although running this over so many rows is a killer...

 

[Andrew Poulsom]

Also, I need to run this over 100K+ rows and it is in need of some enhancement as the current one just killed Excel. Could there be a more efficient way of doing this?

Make a list of the unique entries in column A and use those in your formula. You don't need a result for each of your 100k entries.