Compare two different size lists
 

(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 (a
name and a number respectively) are in AB, but AB, obviously, has more
sets of data than CD.

Is there a function, which I can enter in E10:E400, for example, that
will compare each value in A with _all_ the values in C, and if the A
value is not in C, it will write the missing value(s) in E (and F), in
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
alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=466041

one way:

E10: =IF(COUNTIF(C:C,A10),"",A10)
F10: =IF(E10="","",B10)


In article ,
Ingeniero1
wrote:

(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 (a
name and a number respectively) are in AB, but AB, obviously, has more
sets of data than CD.

Is there a function, which I can enter in E10:E400, for example, that
will compare each value in A with _all_ the values in C, and if the A
value is not in C, it will write the missing value(s) in E (and F), in
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
alex

JE,

That works great!

I thought that I was well familiar with the 'COUNTIF' function, but had
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 you
briefly explain it?

Thanks X2

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=466041

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.



In article ,
Ingeniero1
wrote:

I thought that I was well familiar with the 'COUNTIF' function, but had
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 you
briefly explain it?

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

Thanks!!!!!!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=466041