Excel compare Two colums in different sheets and uncompared/unmatched result should be stored in other worsheet

Question

Please post VBA code for below.

I need to compare two columns in different worksheets (e.g.: column c in sheet1 and Column c in sheet2).
Sheet1 and sheet2 contains 17 columns. and I want result of unmatched items( items which arein sheet2 and not in sheet1) in sheet3.
Sheet3 should containn all 17 columns.
all columns are in text format.

columnD columnF 
1       5       9
2       6       10
3       7       11
4       8       12
5       9
6       10
7       11
8       12
sheet1  sheet2  sheet3

Tony Dallimore Tony Dallimore · Accepted Answer · 2012-02-11T23:25:46

I will be kind and assume you do not know where to start. We sometimes suggest people try using the macro recorder to get a first idea of the code they need. Unfortunately, you problem is not one for which the macro recorder will help.

Comparing two lists like this is not the easiest problem to have as a first problem. I have tried to do everthing in little steps so you can understand them. The trouble is there are a number of possible situations each of which must be tested for and actioned:

Value in Sheet1 but not Sheet2. Get new value from Sheet1.
Value in Sheet2 but not Sheet1. Record non-match. Get new value from Sheet2.
Values match. Get new values from both Sheet1 and Sheet2.
Sheet1 has run out values before Sheet2. Record all remaining values in Sheet2 as non matches.
Sheet2 has run out values. Finish.

I have explained all the steps but I am sure you will need to use F8 to step down the code one statement at a time. If you hover over a variable you can see its value.

Ask if you do not understand but try F8 first. I will not answer questions unless you tell me what you have tried and what went wrong.

Option Explicit         ' This means I cannot use a variable I have not declared
Sub Compare()

  ' Declare all the variables I need
  Dim Row1Crnt As Long
  Dim Row2Crnt As Long
  Dim Row3Crnt As Long
  Dim Row1Last As Long
  Dim Row2Last As Long

  Dim ValueSheet1 As Long
  Dim ValueSheet2 As Long

  Dim NeedNewValueSheet1 As Boolean
  Dim NeedNewValueSheet2 As Boolean

  With Sheets("Sheet1")
    ' This goes to the bottom on column D, then go up until a value is found
    ' So this finds the last value in column D
    Row1Last = .Cells(Rows.Count, "D").End(xlUp).Row
  End With
  ' I assume Row 1 is for headings and the first data row is 2
  Row1Crnt = 2

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

  ' You do not say which column to use in Sheet 3 so I assume "H".
  ' You do not same in the column in Sheet 3 is empty so I place
  ' the values under any existing value
  With Sheets("Sheet3")
    Row3Crnt = .Cells(Rows.Count, "H").End(xlUp).Row
  End With
  Row3Crnt = Row3Crnt + 1   ' The first row under any existing values in column H

  ' In Sheet1, values are on rows Row1Crnt to Row1Last
  ' In Sheet2, values are on rows Row2Crnt to Row2Last
  ' In Sheet3, non-matching values are to be written to Row3Crnt and down

  ' In your questions, all the values are numeric and are in ascending order.
  ' This code assumes this is true for the real data.

    ' Load first values.  This will give an error if the values are not numeric.
    ' If the values are decimal, the decimal part will be lost.
    With Sheets("Sheet1")
      ValueSheet1 = .Cells(Row1Crnt, "D").Value
    End With
    With Sheets("Sheet2")
      ValueSheet2 = .Cells(Row2Crnt, "F").Value
    End With

  ' Loop for ever.  Code inside the loop must decide when to exit
  Do While True
    ' Test for each of the possible situations.
    If Row1Crnt > Row1Last Then
      ' There are no more values in Sheet1.  All remaining values in
      ' Sheet2 have no match
      With Sheets("Sheet3")
        .Cells(Row3Crnt, "H").Value = ValueSheet2
        Row3Crnt = Row3Crnt + 1
      End With
      'I need a new value from Sheet2
      NeedNewValueSheet2 = True
    ElseIf ValueSheet1 = ValueSheet2 Then
      ' The two values are the same.  Neither are required again.
      ' Record I need new values from both sheets.
      NeedNewValueSheet1 = True
      NeedNewValueSheet2 = True
    ElseIf ValueSheet1 < ValueSheet2 Then
      ' Have value in Sheet1 that is not in Sheet2.
      ' In the example in your question you do not record such values
      ' in Sheet3.  That is, you do not record 1, 2, 3 and 4 which are
      ' in Sheet1 but not Sheet3.  I have done the same.
      'I need a new value from Sheet1 but not Sheet2
      NeedNewValueSheet1 = True
      NeedNewValueSheet2 = False
    Else
      ' Have value in Sheet2 that is not in Sheet1.
      ' Record in Sheet3
      With Sheets("Sheet3")
        .Cells(Row3Crnt, "H").Value = ValueSheet2
        Row3Crnt = Row3Crnt + 1
      End With
      'I need a new value from Sheet2 but not Sheet1
      NeedNewValueSheet1 = False
      NeedNewValueSheet2 = True
    End If
    ' I have compared the two values and if a non match was found
    ' it has been recorded.

    ' Load new values as required
    If NeedNewValueSheet1 Then
      ' I need a new value from Sheet1
      Row1Crnt = Row1Crnt + 1
      If Row1Crnt > Row1Last Then
        ' There are no more in Sheet1. Any remaining values
        '  in Sheet2 are not matched.
      Else
        With Sheets("Sheet1")
         ValueSheet1 = .Cells(Row1Crnt, "D").Value
        End With
      End If
    End If

    If NeedNewValueSheet2 Then
      ' I need a new value from Sheet2
      Row2Crnt = Row2Crnt + 1
      If Row2Crnt > Row2Last Then
        ' There are no more in Sheet2.  Any remaining
        ' values in Sheet1 are ignored
        Exit Do
      End If
      With Sheets("Sheet2")
       ValueSheet2 = .Cells(Row2Crnt, "F").Value
      End With
    End If
  Loop

End Sub

New section in response to change to original question

I do not understand what you are trying to do and I assume you must have made changes to my original code. Below I explain statements that appear relevant to your requirement. You should be able to combine them to create the routine you want.

Issue 1

You say column C is now the column you wish to use for comparisons. You also say that rows are not in ascending sequence which my code assumes. The obvious solution is to sort the worksheets by column C.

I created the following code by:

Switching the macro recorder on.
Selecting all of Sheet1, saying I had a header row and sorting it by column C.
Switching the macro recorder off.

Using the macro recorder is the easiest way of discovering how to do something but the code will need some adjustment. The code saved by the macro recorder is:

  Cells.Select
  Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
      DataOption1:=xlSortNormal

I make the following changes:

Add With Sheets("Sheet1") before this code and End With after it. The saved code sorts the active sheet. My changes say I want to sort Sheet1 whichever sheet is active.
Merge the two statements by deleting .Select Selection. I do not want to select the range to be sorted because this slows the macro.
Place a dot before the Cells and the Range. This links them to the With Statement.
Finally I replace Header:=xlGuess by Header:=xlYes.

The result is:

With Sheets("Sheet1")
  .Cells.Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
      DataOption1:=xlSortNormal
End With

Select Help from the VBA Editor and search for "sort method". You will get several results of which one will be "Sort Method". This will explain what all the other parameters are. However, you probably do not need to. If you have sorted Sheet1 the way you want, the other parameters will be as you need.

Make a copy and replace Sheet1 with Sheet2 to give:

With Sheets("Sheet1")
  .Cells.Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
      DataOption1:=xlSortNormal
End With
With Sheets("Sheet2")
  .Cells.Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
      DataOption1:=xlSortNormal
End With

Place these new code just after the last of the Dim statments.

Issue 2

Originally you wanted to use column D in Sheet1 and column F in Sheet 2. Now you want to use column C in both these sheets.

Replace all references to "D" and "F" by "C".

Issue 3

You now want to copy 17 columns from Sheet2 to Sheet3. You do not say which 17 columns in Sheet2 you want to copy or which 17 columns in Sheet3 are to receive the 17 columns. In the following code I assume you want to copy columns A to Q to the 17 columns starting with columns B. You should find it easy to change to the columns you require.

Replace:

With Sheets("Sheet3")
  .Cells(Row3Crnt, "H").Value = ValueSheet2
  Row3Crnt = Row3Crnt + 1
End With

by

With Sheets("Sheet3")
  Worksheets("Sheet2").Range("A" & Row2Crnt & ":Q" & Row2Crnt).Copy _
                              Destination:=.Range("B" & Row3Crnt)
  Row3Crnt = Row3Crnt + 1
End With

Summary

I think these are the statements you need to modify my original routine to get the routine you require.

Excel compare Two colums in different sheets and uncompared/unmatched result should be stored in other worsheet

3 Answers