Compare two different size lists

I

Ingeniero1

(I searched, but the key words yield way too many non-related hits)

I have two lists, A10:B400 and C10:D300. All the sets of data in CD (
name and a number respectively) are in AB, but AB, obviously, has mor
sets of data than CD.

Is there a function, which I can enter in E10:E400, for example, tha
will compare each value in A with _all_ the values in C, and if the
value is not in C, it will write the missing value(s) in E (and F), i
the same row as the A 'source'?

What I trying to find are the sets of data in AB that are not in CD.

Please let me know if I didn't make my query clear enough...

Thanks
ale
 
I

Ingeniero1

JE,

That works great!

I thought that I was well familiar with the 'COUNTIF' function, but ha
never used it with this specific notation - {C:C,A10}, as in {E10
=IF(COUNTIF(C:C,A10),"",A10)}

I can see what it does, but when you get a minute (or two) could yo
briefly explain it?

Thanks X2

Ale
 
J

JE McGimpsey

The conditional:

COUNTIF(C:C,A10)

returns a positive number if the contents of cell A10 are found at least
once in column C ("C:C" is equivalent to "C1:C65536"), 0 if not. XL
interprets zero values as FALSE, non-zero numbers as TRUE.

=IF(<T/F>,"",A10)

will return a null string if the conditional is TRUE, the contents of
A10 if not.

So

=IF(COUNTIF(C:C,A10),"",A10)

will return a null string if the value in A10 is found in column C one
or more times, otherwise the contents of A10 are returned.
 
I

Ingeniero1

Excellent explanation!
What I learned:
• C:C = C1:C64636
• That "COUNTIF" can return ">0=True" and "0=False", and not jus
'counts'
• The use of "True" and "False" within "IF(test,if-true,if-false)"

Thanks!!!!!!

Ale
 
Top