Compare two different size lists

[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 (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 McGimpsey]

one way:

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


In article <Ingeniero1.1v23ii_1126209949.271@excelforum-nospam.com>,
Ingeniero1 <Ingeniero1.1v23ii_1126209949.271@excelforum-nospam.com>
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

[Ingeniero1]

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

[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.



In article <Ingeniero1.1v3hij_1126274792.978@excelforum-nospam.com>,
Ingeniero1 <Ingeniero1.1v3hij_1126274792.978@excelforum-nospam.com>
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?

[Ingeniero1]

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