Counting the lack of a union of two lists

[Skippy Howenstein]

I'm trying to come up with a way to find the non-union of two lists. In
other words, I want to figure out error cases. I tried some COUNTIFS but I'm
just banging my head against the wall at 2:13AM. There must be a simple
solution.

Here's what I roughly have. I'm looking for cases where one of the values
in the Name column is NOT present in the Owner column. If all is well, every
owner will have a valid name. I'm looking for cases where that isn't true.

Name Owner Expected Result = 2 for Skippy and Scooby
Jim Jim
Joe Jim
Bob Bob
Sue Skippy
Sandy Joe
Sandy
Scooby
Sandy
Bob

Thanks in advance!

 

[Mike H]

Hi,

Here's on Way to compare 2 lists. Lets assume your data are in columns A & B
starting in row 1. Put this in C1

=IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW()))

Put this in D1

=IF(ISERROR(SMALL(C:C,ROW(A1))),"",INDEX(B:B,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Now select C1 and d1 and drag down to the length of col B.

This gives a list of the differences in d1 down which you can now easily count
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Why isn't Sue in your list of expected results and, given your description,
I'm not entirely sure why Sandy is not in the expected list as well?