matching data on 2 worksheets?

tinkythomas

Active Member
Joined
Dec 13, 2006
Messages
432
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

tinkythomas

Active Member
Joined
Dec 13, 2006
Messages
432
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 :oops: The data in my worksheets is as follows:
TASK MANAGEMNT SCHED.xls
ABCDEF
1EIACODXALSACONXBALTLCNXBLCNTYPXBTASKCDCATASKIDCA
2VVVV620PABONEAAINSPECTFILLETINGBEADS(DI)
3VVVV62110010PABONEAAINSPECTMAINROTORBLADE(DI)
4VVVV621100199PABONEAAINSPECTMAINROTORBLADE(DI)
5VVVV62110010PABONEABINSPECTMRBBUTTSTRAP(SDI)
6VVVV621100199PABONEABINSPECTMRBBUTTSTRAP(SDI)
7VVVV62110010PABONEACINSPECTMRBATTACHBUSHES(DI)
8VVVV621100199PABONEACINSPECTMRBATTACHBUSHES(DI)
9VVVV62110010PABONEADINSPECTMAINROTORBLADE(GVI)
10VVVV621100199PABONEADINSPECTMAINROTORBLADE(GVI)
11VVVV62110030PABONEAAINSPECTMAINROTORBLADE(DI)
12VVVV62110030PABONEABINSPECTMRBBUTTSTRAP(SDI)
13VVVV62110030PABONEACINSPECTMRBATTACHBUSHES(DI)
14VVVV62110030PABONEADINSPECTMAINROTORBLADE(GVI)
15VVVV62110050PABONEAAINSPECTMAINROTORBLADE(DI)
16VVVV62110050PABONEABINSPECTMRBBUTTSTRAP(SDI)
17VVVV62110050PABONEACINSPECTMRBATTACHBUSHES(DI)
18VVVV62110050PABONEADINSPECTMAINROTORBLADE(GVI)
19VVVV62110070PABONEAAINSPECTMAINROTORBLADE(DI)
20VVVV62110070PABONEABINSPECTMRBBUTTSTRAP(SDI)
21VVVV62110070PABONEACINSPECTMRBATTACHBUSHES(DI)
22VVVV62110070PABONEADINSPECTMAINROTORBLADE(GVI)
23VVVV62110090PABONEAAINSPECTMAINROTORBLADE(DI)
24VVVV62110090PABONEABINSPECTMRBBUTTSTRAP(SDI)
Sheet2

Thanks once again for your help.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

tinkythomas

Active Member
Joined
Dec 13, 2006
Messages
432

ADVERTISEMENT

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

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

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
 

tinkythomas

Active Member
Joined
Dec 13, 2006
Messages
432
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 :biggrin:

Thanks once again to you and others who have contributed.

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,168
Members
417,129
Latest member
geekzilla

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top