Hello, 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 HatAny help would be greatly appreciated

Thanks

Try This: 1. Type the following formula in cell C3:

=IF(ISERROR(MATCH(A3,$B$3:$B$34,0)),A3,"")

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

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

Thanks

Hi, 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.

Regards

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 HatIf you want to see those items that are matched in both, then modify the formula to:

=IF(ISERROR(MATCH(A3,$B$3:$B$34,0)),"",A3)

And using your data I get:

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

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

K

If you are on Sheet 1 and your data in column A looks like: Pants

Shirt

Shorts

Hat

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

Top

Blouse

Pants

Toques

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

=IF(ISERROR(MATCH(A3,Sheet2!$A$3:$A$7,0)),"",A3)

On sheet 1 you should get:

Pants Pants Shirt Shorts Hat Hat Top TopMIKE

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History