Probably Very Simple but please help a neophyte

marcbaron

New Member
Joined
Aug 28, 2002
Messages
1
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459

ADVERTISEMENT

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

:biggrin:
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
{=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.
 

Forum statistics

Threads
1,148,055
Messages
5,744,544
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top