Compare rows between 2 worksheets

0
votes

I have 2 worksheets in the same workbook, Sheet1 and Sheet2. Both sheets have the same number of columns, 31 columns and about 100 rows.

Both sheets have the same column values

Example: 

 colA    colB       colC
 Date    Prj Name   Prj Status

I need to see if the rows in sheet 1 match the rows in sheet 2. The entire row, I've looked many places and all of them are matching cell to cell, I need to match the entire ROW.

For example:

Look at row 10 in sheet1 and go through all rows in sheet 2 if there is a match, do nothing. If there is not a match highlight that row in sheet1 to indicate there is no match for this in Sheet2.

1
excelvba
If you have found information on how to match two cells using vba, then you can just have a condition that checks if two pairs of cells match using and. For example, if worksheets("Sheet1").cells(1,1)=worksheets("Sheet2").cells(1,1) and worksheets("Sheet1).cells(2,1)=worksheets("Sheet2").cells(2,1) then ... – Matt Cremeens
If there are only three columns then I would suggest a VBA implementation of a COUNTIFS function. If there are more columns then edit your question to reflect the actual conditions of the comparison. It would help to know the scope (numberof rows) of hte comparison test. In either event, show what you've come up with so far. – user4039065
stackoverflow.com/questions/22685622/… – Tim Williams
If you changed the code and you need help getting it working, you'll need to post your edited version, and indicate exactly which line is the problem. – Tim Williams
@pnuts - No, I was judging the scope of the comparison. A small data set like 2 @ 100 rows × 30 columns opens up UDF and even native formula possibilities that larger data sets would discourage. – user4039065

1 Answers

0
votes

2 Approaches I would take here:

Approach A:

Step1 - Use a Concatenate formula to create one string out of the 31 columns. Assuming row 1 is your headers the formula should be:

=CONCATENATE(A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2, L2, M2, N2, O2, P2, Q2, R2, S2, T2, U2, V2, W2, X2, Y2, Z2, AA2, AB2, AC2, AD2, AE2)

Paste this formula next to each row at the end in probably COL AF on both sheets

Step2 - Copy and Paste COL AF from one sheet into its own sheet, paste only values and sort ascending. Apply either VLOOKUP OR INDEX(MATCH) method to check if there is match between any rows. NOTE: you probably need to copy and paste rows numbers in your VLOOKUP array/data. This will serve as a Unique Identifier or Primary Key. It will help determine the row number of matching rows. Highlighting of the Row can than be achieved using conditional formatting.

Approach B:

VBA Code, do this if you have some experience in VBA, otherwise stick with formulas. May take some playing around till the formulas work just right. If you post your Code I'll help you repair it.