Compare text Excel 2007

Microsoft Office 2007 home and student
April 25, 2010 at 10:47:16
Specs: Windows XP
 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 CPants Top PantsShirt Blouse TopShorts Pants HatHat ToquesTop HatAny help would be greatly appreciatedThanks

See More: Compare text Excel 2007

#1
April 25, 2010 at 13:58:00
 Try This:1. Type the following formula in cell C3: =IF(ISERROR(MATCH(A3,\$B\$3:\$B\$34,0)),A3,"")3. Select cells C3:C344. 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.MIKEhttp://www.skeptic.com/

Report •

#2
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 writtenThanks

Report •

#3
April 25, 2010 at 16:06:20
 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

Report •

Related Solutions

#4
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 BSo 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:=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 Top ```MIKEhttp://www.skeptic.com/

Report •

#5
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 againK

Report •

#6
April 27, 2010 at 11:12:23
 If you are on Sheet 1 and your data in column A looks like:Pants Shirt ShortsHat Top While on Sheet 2 column A, you have the data like:TopBlousePantsToquesHatThen 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 Top ```MIKEhttp://www.skeptic.com/

Report •