# matching data on 2 worksheets?

#### tinkythomas

##### Active Member
Hello,

I have 2 worksheets both with identical columns, but sheet 2 has more but similar rows of data. Is there a way to find all unmatched rows of data on sheet 2 compared to that of sheet 1?

Thanks

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Thank-you John for the link. The code works but I need to match more than 1 column. I need columns B, C, E, and F matched also, is this possible? I can't figure out how to modify the code The data in my worksheets is as follows:
ABCDEF
5VVVV62110010PABONEABINSPECTMRBBUTTSTRAP(SDI)
6VVVV621100199PABONEABINSPECTMRBBUTTSTRAP(SDI)
7VVVV62110010PABONEACINSPECTMRBATTACHBUSHES(DI)
8VVVV621100199PABONEACINSPECTMRBATTACHBUSHES(DI)
12VVVV62110030PABONEABINSPECTMRBBUTTSTRAP(SDI)
13VVVV62110030PABONEACINSPECTMRBATTACHBUSHES(DI)
16VVVV62110050PABONEABINSPECTMRBBUTTSTRAP(SDI)
17VVVV62110050PABONEACINSPECTMRBATTACHBUSHES(DI)
20VVVV62110070PABONEABINSPECTMRBBUTTSTRAP(SDI)
21VVVV62110070PABONEACINSPECTMRBATTACHBUSHES(DI)
24VVVV62110090PABONEABINSPECTMRBBUTTSTRAP(SDI)
Sheet2

Thanks once again for your help.

Pulling unique ROWs between worksheets and multiple columns becomes very complecated using code.
You might try using conditional formatting to highlight matched or unmatched items between the worksheets. Not an elegant solution, but as I mentioned, you are comparing a lot more data than two columns.

Ok I will give that a try, thanks again it's greatly appreciated

I have put together his code to copy unique "Rows" from Sheets 1 and 2 to Sheets 3 and 4 respectively.
Code:
``````Sub CopyUniqueItems()
Dim Rng As Range, c As Long
Dim Rng2 As Range, i As Long
Dim LR1 As Integer, LR2 As Integer
Dim LR3 As Integer, LR4 As Integer

LR2 = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False

'List Unique Rows for Sheet1 by "X" in column G
For c = 1 To 6
Sheets("Sheet2").Select
Set Rng = Sheets("Sheet2").Range(Cells(2, c), Cells(LR2, c))
Sheets("Sheet1").Select
For i = 2 To Rng.Rows.Count
If Application.WorksheetFunction.CountIf(Rng, Cells(i, c)) = 0 Then
Cells(i, 7).Value = "X"
End If
Next i
Next c

'List Unique Rows for Sheet2 by "X" in column G
LR1 = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
For c = 1 To 6
Sheets("Sheet1").Select
Set Rng = Sheets("Sheet1").Range(Cells(2, c), Cells(LR1, c))
Sheets("Sheet2").Select

For i = 2 To Rng.Rows.Count
If Application.WorksheetFunction.CountIf(Rng, Cells(i, c)) = 0 Then
Cells(i, 7).Value = "X"
End If
Next i
Next c
'Exit Sub
'Copy rows that contain "X"'s in column G to Sheet3
Sheets("Sheet1").Select
Set Rng1 = Sheets("Sheet1").Range("C1", Range("C65536").End(xlUp))
For i = 1 To Rng1.Rows.Count
If Sheets("Sheet1").Cells(i, 7).Value = "X" Then
Sheets("Sheet1").Cells(i, 7).Value = ""
LR3 = Sheets("Sheet3").Cells(Rows.Count, "B").End(xlUp).Row + 1
Rows(i).EntireRow.Copy Destination:=Sheets("Sheet3").Range("A" & LR3)
End If
Next i
'Copy rows that contain "X"'s in column G to Sheet4
Sheets("Sheet2").Select
Set Rng1 = Sheets("Sheet2").Range("C1", Range("C65536").End(xlUp))
For i = 1 To Rng1.Rows.Count
If Sheets("Sheet2").Cells(i, 7).Value = "X" Then
Sheets("Sheet2").Cells(i, 7).Value = ""
LR4 = Sheets("Sheet4").Cells(Rows.Count, "B").End(xlUp).Row + 1
Rows(i).EntireRow.Copy Destination:=Sheets("Sheet4").Range("A" & LR4)
End If
Next i

Application.ScreenUpdating = True
End Sub``````
The code compares each column between Sheets 1 and 2, if it finds a Unique item an X is placed in column G. Then the code copies all rows with an X to sheets 3 and 4, clearing the "X" in the process.

Hope this is of some help.

Or concatenate data from the relevant columns into one new field. Using this new key, take whatever approach suits you. The one you have, using formulas (such as MATCH), using code, using arrays, using advanced filters, using SQL. There are many ways. Personally, I'd go with some SQL. Cheers, Fazza

Thank-you so much John, I can see you have put a lot of effort into this! Your help is greatly appreciated. This is just what I needed as I have huge amounts of data to match. The code runs flawlessly and will save me huge amounts of time

Thanks once again to you and others who have contributed.

Regards

Thank you for the response.

Replies
1
Views
86
Replies
1
Views
104
Replies
0
Views
274
Replies
6
Views
202
Replies
1
Views
132

1,219,811
Messages
6,150,363
Members
450,955
Latest member
rose8693

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back