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!
--
Just remember, wherever you go...there you are.

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.


"Skippy Howenstein" wrote:

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!
--
Just remember, wherever you go...there you are.

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?

--
Rick (MVP - Excel)


"Skippy Howenstein" wrote in
message ...
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!
--
Just remember, wherever you go...there you are.