Comparing 2 Spreadsheets to Identify duplicate rows

**eagle670** · 06-11-2012, 02:53 PM

I know that this has been discussed at some point but for the life of me I cannot find a solution to my issue. If someone could help me with this I would appreciated it.

I have 2 spreadsheets in 1 workbook. One tab is named MASTER and the other tab is named WORKSHEET. Both spreadsheets are identically setup with 3 columns titled UPC, Stockcode, Division. What I want to do is match the rows on the worksheet spreadsheet to the master spreadsheet.

The formula or macro needs to take the first row in the worksheet spreadsheet and then look for a match in the master spreadsheet, all 3 columns of data must match exactly when compared to each spreadsheet.

Example

MASTER
UPC STOCKCODE DIVISION
1234567890 123456 KANSAS CITY
2222222222 323232 FORT WORTH
3333333333 414141 OKLAHOMA

WORKSHEET
UPC STOCKCODE DIVISION
1234567890 123456 MEMPHIS
2222222222 323232 FORT WORTH
3333333333 414141 NASHVILLE

Note: In this example there is only one true match where all 3 columns are in the same row.

Basically all I want done is to have the matched rows highlighted or identified in some way on the "WORKSHEET", so as too make them easily identifiable.

Hope that makes sense.

**NBVC** · 06-11-2012, 03:04 PM

Since you are using Excel 2003, as per your profile... i would suggest that you first make a helper column on each sheet, concatenating the 3 columns together.

e.g. =A1&"_"&B2&"_"&C2 (the underscore is to ensure the fields are separated correctly).

Then you can use a formula like:

=Isnumber(Match(D2,Sheet2!D:D,0))

copied down where column D is your helper in both sheets.

TRUE means match found.

**eagle670** · 06-11-2012, 03:28 PM

I see what you are getting at, but my columns contain text and numeric values, so that approach will not work.

**NBVC** · 06-11-2012, 03:30 PM

It should still work... Isnumber() is not checking for numeric.. it is checking if MATCH() returned a numeric result (meaning match found at a certain position). Give it a try.

**eagle670** · 06-12-2012, 09:46 AM

That worked. I appreciate the help!

**Doubolplay1** · 04-08-2013, 10:17 PM

Originally Posted by eagle670

That worked. I appreciate the help!

While this may work, I am using excel 2010; How would you perform this same exact function in 2010.

I am only asking because it was noted when answer was provided.

Thank you in advance.

Edit : Also, wouldn't be that be A2 in the formula?

**NBVC** · 04-09-2013, 10:13 AM

You are correct that should have been =A2&"_"&B2&"_"&C2

you can use the same in 2010, but you can also use COUNTIFS without helper column.

e.g. =COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2,Sheet2!C:C,C2)>0 copied down.