#### marcbaron

##### New Member
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.

This message was edited by marcbaron on 2002-08-29 13:43

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.

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

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.

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.

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

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

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")

{=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.

Replies
1
Views
316
Replies
1
Views
293
Replies
11
Views
736
Replies
4
Views
375
Replies
6
Views
425

1,218,699
Messages
6,143,960
Members
450,519
Latest member
nashat

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

### Which adblocker are you using?

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

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