matching data on 2 worksheets?

[tinkythomas]

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

 

[Datsmart]

This previous thread has a code solution for a similar question:
http://www.mrexcel.com/board2/viewtopic.php?p=1298106

 

[tinkythomas]

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:

TASK MANAGEMNT SCHED.xls
	A	B	C	D	E	F
1	EIACODXA	LSACONXB	ALTLCNXB	LCNTYPXB	TASKCDCA	TASKIDCA
2	VVV	V62	0	P	ABONEAA	INSPECTFILLETINGBEADS(DI)
3	VVV	V6211001	0	P	ABONEAA	INSPECTMAINROTORBLADE(DI)
4	VVV	V6211001	99	P	ABONEAA	INSPECTMAINROTORBLADE(DI)
5	VVV	V6211001	0	P	ABONEAB	INSPECTMRBBUTTSTRAP(SDI)
6	VVV	V6211001	99	P	ABONEAB	INSPECTMRBBUTTSTRAP(SDI)
7	VVV	V6211001	0	P	ABONEAC	INSPECTMRBATTACHBUSHES(DI)
8	VVV	V6211001	99	P	ABONEAC	INSPECTMRBATTACHBUSHES(DI)
9	VVV	V6211001	0	P	ABONEAD	INSPECTMAINROTORBLADE(GVI)
10	VVV	V6211001	99	P	ABONEAD	INSPECTMAINROTORBLADE(GVI)
11	VVV	V6211003	0	P	ABONEAA	INSPECTMAINROTORBLADE(DI)
12	VVV	V6211003	0	P	ABONEAB	INSPECTMRBBUTTSTRAP(SDI)
13	VVV	V6211003	0	P	ABONEAC	INSPECTMRBATTACHBUSHES(DI)
14	VVV	V6211003	0	P	ABONEAD	INSPECTMAINROTORBLADE(GVI)
15	VVV	V6211005	0	P	ABONEAA	INSPECTMAINROTORBLADE(DI)
16	VVV	V6211005	0	P	ABONEAB	INSPECTMRBBUTTSTRAP(SDI)
17	VVV	V6211005	0	P	ABONEAC	INSPECTMRBATTACHBUSHES(DI)
18	VVV	V6211005	0	P	ABONEAD	INSPECTMAINROTORBLADE(GVI)
19	VVV	V6211007	0	P	ABONEAA	INSPECTMAINROTORBLADE(DI)
20	VVV	V6211007	0	P	ABONEAB	INSPECTMRBBUTTSTRAP(SDI)
21	VVV	V6211007	0	P	ABONEAC	INSPECTMRBATTACHBUSHES(DI)
22	VVV	V6211007	0	P	ABONEAD	INSPECTMAINROTORBLADE(GVI)
23	VVV	V6211009	0	P	ABONEAA	INSPECTMAINROTORBLADE(DI)
24	VVV	V6211009	0	P	ABONEAB	INSPECTMRBBUTTSTRAP(SDI)
Sheet2

Thanks once again for your help.

 

[Datsmart]

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]

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

 

[Datsmart]

I have put together his code to copy unique "Rows" from Sheets 1 and 2 to Sheets 3 and 4 respectively.

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]

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]

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

 

[Datsmart]

Thank you for the response.
Glad it worked for you.