Probably Very Simple but please help a neophyte

[marcbaron]

I have two lists of companies, column A and Column B. I need a quick and easy way of finding out if any of the companies listed in column A are also in column B.

Thanks so much, in advance.
This message was edited by marcbaron on 2002-08-29 13:43

 

[Russell Hauf]

On 2002-08-29 13:33, marcbaron wrote:
I have two lists of companies, column A and Column B. I need a quick and easy way of finding out if any of the companies listed in column A are also in column B.

Thanks so much, in advance.

Welcome to the board!

What I would do is to either insert a column between a and b, or put the formulas in column C. Let's assume that the following formula is in cell C1, and you have not inserted a column between A and B.

=(COUNTIF($B$1:$B$20,A1)>0)

Change the $B$1:$B$20 accordingly, and drag the formula down as far as you have data in column A.

Note that this is one of SEVERAL ways to do this. The result in column C will be TRUE or FALSE.

HTH,

Russell
This message was edited by Russell Hauf on 2002-08-29 13:49

 

[Brian from Maui]

In addition to Russell's suggestion, you can also highlight duplicates in columns A and B.

Highlight your range in column A and B

Go to Format/Conditional Formatting, enter in formula is

=countif($a$1:$b$10,a1)>1, click format/pattern choose a color, click OK OK.

$a$1:$b$10 is the range highlighted, adjust your range

 

[Russell Hauf]

On 2002-08-29 14:03, Brian from Maui wrote:
In addition to Russell's suggestion, you can also highlight duplicates in columns A and B.

Highlight your range in column A and B

Go to Format/Conditional Formatting, enter in formula is

=countif($a$1:$b$10,a1)>1, click format/pattern choose a color, click OK OK.

$a$1:$b$10 is the range highlighted, adjust your range

Actually, this is not technically correct. If you have duplicate values in Column A, they will be highlighted. If you just want to see which ones from A are in B, then just highlight column A and adjust the formula to:

=countif($b$1:$b$10,a1)>1

-rh

 

[Brian from Maui]

Mahalo Russell for the correction. Yes the answer I gave highlights both columns

 

[PaddyD]

Note that this is one of SEVERAL ways to do this.

Just for the hell of it:

=if(isnumber(match(a1,$b$1:$b$20,0)),"There","Not there")

Paddy

 

[Juan Pablo González]

{=IF(SUM(COUNTIF(A1:A4,B1:B3))>0,"Duplicates","Non duplicates")}

Array entered. Assumed A1:A4 to be the first column, B1:B3 the second one.