0

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.

Spdream
  • 11
  • 2
  • 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 Jun 04 '15 at 21:56
  • If there are only three columns then I would suggest a VBA implementation of a [COUNTIFS function](https://support.office.com/en-us/article/COUNTIFS-function-53C4DC8E-0E5B-4E32-93DF-9CA5E7DA89ED). 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. –  Jun 04 '15 at 22:00
  • The issue is that it any 1 row from sheet1 will have to go through the entire worksheet of sheet2 and see if there is a match or not. its not garunteed that sheet1 row1 will match sheet2 row1 To Jeeped's comment, I have written there are 31 columns and I have not been able to come up with anything because what I have only deals with it cell by cell. For example this is what I have =IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),"","Duplicate I would have to use this formula for every cell in Sheet1 and again this would match Sheet1 A to Sheet2 A when I need it to look through the entire worksheet2 – Spdream Jun 04 '15 at 22:02
  • 1
    http://stackoverflow.com/questions/22685622/fastest-way-to-find-a-row-in-excel-range-using-excel-macro-vba/22704708#22704708 – Tim Williams Jun 04 '15 at 22:16
  • Thanks tim, but I am getting a Type Mismatch error when I try to run the macro.. I did change the macro to fir my workbook – Spdream Jun 04 '15 at 22:22
  • 1
    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 Jun 04 '15 at 23:00
  • 1
    @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. –  Jun 04 '15 at 23:56

1 Answers1

0

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.

gokool108
  • 56
  • 3