Compare two column from two different sheets

Question

I need to compare two columns in different worksheets.

First sheet (WITH 500 ROWS)

ColumnA(NUMBER) ColumnB(STRING) ColumnC(NUMBER)ColumnD(NUMBER)ColumnE(NUMBER)ColumnF(NUMBER)

Second Sheet

ColumnA(NUMBER) ColumnB(STRING) ColumnC(NUMBER)ColumnD(NUMBER)ColumnE(NUMBER) with out ColumnF

I want to print out in sheet3 the SITESID(COLUMNA) from the second sheet that do not exist in the first sheet .

Option Explicit
Sub Compare()

Dim Row1Crnt As Long
Dim Row2Crnt As Long
Dim Row3Crnt As Long    
Dim Row1Last As Long
Dim Row2Last As Long    

Dim ValueSheet1
Dim ValueSheet2
Dim duplicate As Boolean    
Dim maxColmn As Long
Dim i
maxColmn = 10  ' number of column to compare
For i = 1 To maxColmn

With Sheets("Sheet1")
     Row1Last = .Cells(Rows.Count, i).End(xlUp).Row
End With

With Sheets("Sheet2")
     Row2Last = .Cells(Rows.Count, i).End(xlUp).Row
End With

Row1Crnt = 2
Row2Crnt = 2
Row3Crnt = 2    
maxColmn = 10

Do While Row2Crnt <= Row2Last

duplicate = False
Row1Crnt = 2

With Sheets("Sheet2")
ValueSheet2 = .Cells(Row2Crnt, i).Value
End With
Do While Row1Crnt <= Row1Last

With Sheets("Sheet1")
 ValueSheet1 = .Cells(Row1Crnt, i).Value
End With

If ValueSheet1 = ValueSheet2 Then
   duplicate = True
Exit Do

End If
Row1Crnt = Row1Crnt + 1
Loop

If duplicate = False Then
With Sheets("Sheet3")
   .Cells(Row3Crnt, i).Value = ValueSheet2
    Row3Crnt = Row3Crnt + 1
  End With

End If

Row2Crnt = Row2Crnt + 1
Loop
Next

End Sub

But I'll take that as a result all columnA (SITESID) sheet2 and ColumnB(NAMES) from the second sheet that do not exist in the first sheet

Unknown Unknown · Accepted Answer · 2017-07-01T13:17:23

The quickest 'check for exist' is application.match.

sub compare
    dim a as long, arr as variant, chk as variant

    with worksheets("sheet1")
        arr = .range(.cells(2, "A"), .cells(.rows.count, "A").end(xlup)).value2
    end with

    with worksheets("sheet3")
        for a = lbound(arr, 1) to ubound(arr, 1)
            if iserror(application.match(arr(a, 1), worksheets("sheet2").columns("A"), 0)) then
                .cells(.rows.count, "A").end(xlup).offset(1, 0) = arr(a, 1)
            end if
        next a
    end with
end sub

Compare two column from two different sheets

1 Answers