Comparing entire row on seperate sheets

[DejaEntendu]

Hi Everyone.

I have what seems to be a difficult task as I can't even think on how to approach it.

I need to compare entire rows from one sheet to another another in order to see what was left off the new SHEET and highlight or copy them to another sheet. For example...

Sheet 1

COL A COL B COL C COL D​

ROW 1 ABC1 ABC2 ABC3 ABC4
ROW 2 DEF1 DEF2 DEF3 DEF4
ROW 3 GHI1 GHI2 GHI3 GHI4

Sheet 2

COL A COL B COL C COL D​

ROW 1 ABC1 ABC2 ABC3 ABC4
ROW 2 DEF1 DEF2 DEF3 DEF4
ROW 3


Where Row 3 on sheet 1 isn't anywhere on sheet 2 i'd like it to be highlighted or copied to a new sheet.

I greatly appreciate any help with this.

 

[NARAYANK991]

Hi ,

Even when you say compare an entire row , I am sure you don't mean that ; can you be more precise ?

1. How many columns will your comparison span ?

2. Will the data in any row in Sheet1 appear in the same row in Sheet2 ? Thus , the data DEF1 , DEF2 ,... which is in row 2 in Sheet1 , can it appear in say row 57 in Sheet2 , or will it appear only in row 2 in Sheet2 ?

Narayan

 

[Smallman]

Hi Deja

Welcome to the forum

The simpliest solution to this one would be to concatenate all of the items into a single column on each sheet and compare the two columns with conditional formatting. Say your data was in Cols A to D. Then you would concatenate into Col E for both sheets then in F put this

=COUNTIF(Sheet2!$E$2:$E$10,$E2)>0

All items which are False are unique. Pull the Filter by False, Copy and paste trigger and you are done.

A more complex solution would be to use vba.

The following will

Option Explicit
 
Sub CompareIt()
    Dim ar As Variant
    Dim arr As Variant
    Dim Var As Variant
    Dim i As Long
    Dim n As Long
    Dim j As Long
    Dim str As String
 
    ar = Sheet1.Cells(1).CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        ReDim w(1 To UBound(ar, 2))
        For i = 2 To UBound(ar, 1)
            For n = 1 To UBound(ar, 2)
                str = str & Chr(2) & ar(i, n)
                w(n) = ar(i, n)
            Next
            .Item(str) = w: str = ""
        Next
        ar = Sheet2.Cells(1).CurrentRegion.Resize(, UBound(w)).Value
        For i = 2 To UBound(ar, 1)
            For n = 1 To UBound(ar, 2)
                str = str & Chr(2) & ar(i, n)
                w(n) = ar(i, n)
            Next
            If .exists(str) Then
                .Item(str) = Empty
            Else
                .Item(str) = w
            End If
            str = ""
        Next
        For Each arr In .keys
            If IsEmpty(.Item(arr)) Then .Remove arr
        Next
        Var = .items: j = .Count
    End With
    With Sheet3.Range("a1").Resize(, UBound(ar, 2))
        .CurrentRegion.ClearContents
        .Value = ar
        If j > 0 Then
            .Offset(1).Resize(j).Value = Application.Transpose(Application.Transpose(Var))
        End If
    End With
End Sub

File attached to show workings. Pull the trigger on Sheet1 and the results are on Sheet3.

Take care

Smallman

 

[Marc L]

Hi,

you can also concatenate by using Join & Index functions …
And maybe storing keys with a hash value like in this code …

Regards !
​

 

[DejaEntendu]

Hi Deja

Welcome to the forum

The simpliest solution to this one would be to concatenate all of the items into a single column on each sheet and compare the two columns with conditional formatting. Say your data was in Cols A to D. Then you would concatenate into Col E for both sheets then in F put this

=COUNTIF(Sheet2!$E$2:$E$10,$E2)>0

All items which are False are unique. Pull the Filter by False, Copy and paste trigger and you are done.

A more complex solution would be to use vba.

The following will

Option Explicit

Sub CompareIt()
    Dim ar As Variant
    Dim arr As Variant
    Dim Var As Variant
    Dim i As Long
    Dim n As Long
    Dim j As Long
    Dim str As String

    ar = Sheet1.Cells(1).CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        ReDim w(1 To UBound(ar, 2))
        For i = 2 To UBound(ar, 1)
            For n = 1 To UBound(ar, 2)
                str = str & Chr(2) & ar(i, n)
                w(n) = ar(i, n)
            Next
            .Item(str) = w: str = ""
        Next
        ar = Sheet2.Cells(1).CurrentRegion.Resize(, UBound(w)).Value
        For i = 2 To UBound(ar, 1)
            For n = 1 To UBound(ar, 2)
                str = str & Chr(2) & ar(i, n)
                w(n) = ar(i, n)
            Next
            If .exists(str) Then
                .Item(str) = Empty
            Else
                .Item(str) = w
            End If
            str = ""
        Next
        For Each arr In .keys
            If IsEmpty(.Item(arr)) Then .Remove arr
        Next
        Var = .items: j = .Count
    End With
    With Sheet3.Range("a1").Resize(, UBound(ar, 2))
        .CurrentRegion.ClearContents
        .Value = ar
        If j > 0 Then
            .Offset(1).Resize(j).Value = Application.Transpose(Application.Transpose(Var))
        End If
    End With
End Sub

File attached to show workings. Pull the trigger on Sheet1 and the results are on Sheet3.

Take care

Smallman

Thank you very much for your help. Sorry I haven't been back sooner to thank you. Busy with work and home and then I've been trying to access chandoo.com which is down(and an unrelated site). Anyways, I went with your first solution I never would have thought to just concatenate the cells and just compare one column to another, works beautifully.

I went and purchased a book on VBA and am just getting into it.

Again thank you and everyone else for you're suggestions! I really do appreciate it.