Compare text Excel 2007

Microsoft Office 2007 home and student
April 25, 2010 at 10:47:16
Specs: Windows XP

I am trying to figure out a formula in Excel 2007 that compares text entries in two columns and if any entries from column A matches any entries in Column B, column C would have a list of all the duplicates. It would be some kind of array formula I'm sure. Ex:

Column A Column B Column C
Pants Top Pants
Shirt Blouse Top
Shorts Pants Hat
Hat Toques
Top Hat

Any help would be greatly appreciated


See More: Compare text Excel 2007

Report •

April 25, 2010 at 13:58:00
Try This:

1. Type the following formula in cell C3:


3. Select cells C3:C34

4. Not sure this works in 2007, but the idea is to then "Edit", select "Fill" on the Edit menu, and then click “Down”.

This will compare Column A against Column B
and if the contents of cell A equals the contents of any cell B
the contents of cell A are inserted in column C.

The columns do not have to be sorted, although it might be easier.
I Started at Row 3, change the formula if needed.
I used only 34 rows to check, again change if needed.


Report •

April 25, 2010 at 14:20:31
Hi Mike,

Thanks for the help. Although, the formula you wrote as it stands, =IF(ISERROR(MATCH(A3,$B$3:$B$34,0)),A3,""), is just concerned with cell A3 and places what is in cell A3 into cell C1. I am wondering if you copied the cell to this post correctly because I can see the logic and no errors occur, but it doesn't work exactly as written


Report •

April 25, 2010 at 16:06:20

When posting, it would be helpful to know what doesn't work.

Nobody can see what you can see on your worksheet !

A description, perhaps with a copy of the inputs and outputs would be great.


Report •

Related Solutions

April 26, 2010 at 08:23:15
As written, the formula shows those items in column A that do no not have a matching item in column B

So using your sample:

Pants	Top	
Shirt	Blouse	Shirt
Shorts	Pants	Shorts
Hat	Toques	
Top	Hat	

If you want to see those items that are matched in both, then modify the formula to:


And using your data I get:

Pants	Top	Pants
Shirt	Blouse	
Shorts	Pants	
Hat	Toques	Hat
Top	Hat	Top


Report •

April 27, 2010 at 10:16:34
Much appreciated Mike, thanks! Got it working now.

Essentially, I want it to work from Sheet 1 to Sheet 2. Ex. Compare Column A from Sheet 1 to Column A from Sheet 2. But this is a great start. I just didn't want to jump right into that seeing the trouble I was having.

Thanks again


Report •

April 27, 2010 at 11:12:23
If you are on Sheet 1 and your data in column A looks like:


While on Sheet 2 column A, you have the data like:


Then try this in Sheet 1 cell B3 and copy down:


On sheet 1 you should get:

Pants	Pants
Hat	Hat
Top	Top


Report •

Ask Question