Comparison of two lists of text and finding commonalities

P

PayPaul

I have two columns of text where I'm trying to find out if one of those
columns contains the same text in individual rows as the other column. One of
the columns has more entries/rows than the other. I tried the EXACT function
and tried using the first column entry as an array down the column and the
second column entry as the row that I'm checking it against. Like this
=EXACT(A2:A366, B2).
This didn't produce the result correctly.

Here's a small portion of the columns so you get the idea.
Company Name from Unique TCN Company Name from Pivot
A.G. Edwards & Sons, Inc. A.G. Edwards & Sons, Inc.
AB Chalmersinvest AB Chalmersinvest
Abraham Trading Company ABN Amro Incorporated
Advanced Investment Partners Acadian Asset Management
Alliance Capital Management LP Acta Realty Corporation
Alliance Capital Management LP AEW Capital Management LP
Allstate Insurance Company Afton Capital Management LLC
American Century Invest. Mgmt. AGF Management Ltd.
AMICA Mutual Insurance Co. AIG Global Investment Corp
Analytic Investors, Inc. Aim Advisors, Inc.
Andor Capital Management LLC Alberta Finance
Applied Finance Group Alexandra Investment Management Ltd
Archer Capital Management Alson Capital Partners

As you can see not all of them match but some may in the second colum. The
second column is the longer one. How do I find out which names in the second
column are the same as the ones in the first without manual proofreading?

Thanks in advance,

Paul
 
F

Fleone

One quick way would be to use Conditional Formatting. In Excel 2007 select
the range of cells you want to compare, click on the down arrow on the
Conditional Formatting button and select Highlight Cells Rules and Duplicate
Values. All the matching values will be displayed in the formatting that you
select. When I am searching a list looking for dupes and I want to do it
quickly, this is the method I use.
 
P

PayPaul

I'm using Excel 2003. Is there a way to do this in that version?

I also have two worksheets in which I need to find out if the names listed
in both in the CONTACTS column matdh or don't match. I'm thinking it's a
vlookup solution but can't quite figure out the best way to set that up.

Thank you,

Paul
 
F

Fleone

Marcelo's link shows what is probably the best way to accomplish this using
Excel 2003. I did that process in the past.
 
P

PayPaul

I appreciate the tip. However it tells me to "name" the ranges. When I try
that I'm told the name I picked was "incorrect" Then I tried to apply the
formula and got a formula error message.

Instead of naming the range, which I was unable to do, I simply chose the
appropriate range. That appeared in the formula but the cell indication I put
in was deemed to be in error. I made this attempt on a non filtered worksheet
with headers. The only way it seems to work for me is if I select the entire
column as a range.
 
Top