Wednesday, October 17, 2012

EXCEL Macro - Compare Column A to Column B

Recently a friend asked me to fix a macro that she was working on to compare two columns in Excel and if the cell in Column A did not exist in any cell of Column B, then add it to Column C.

You can make check both columns for miss matches by copy/pasting Column B to Column A and vice versa to check both lists and maybe someone will find it handy =)

Sub Subset()

Dim Acell As Range 'Acell is a range of 1 or more cells
Dim Bcell As Range 'Bcell is a range of 1 or more cells
Dim Ccell As Range 'Ccell is a range of 1 or more cells
Dim Index As Integer 'Index is the current row we are saving to in the Ccell

Index = 1 'Start the Index at 1
Dim found As Boolean

   'Remove the hidden space 160
   Range("A1:A1000").Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
   Range("B1:B1000").Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
   For Each Acell In Range("A1:A1000")
    Acell.value = Application.WorksheetFunction.Clean(Application.WorksheetFunction.Trim(Trim(Acell.value)))
   Next Acell
   For Each Bcell In Range("B1:B1000")
    Bcell.value = Application.WorksheetFunction.Clean(Application.WorksheetFunction.Trim(Trim(Bcell.value)))
   Next Bcell
   ' Done Cleaning
  
   'Iterate through all cells from A1 to A1000

   For Each Acell In Range("A1:A1000")
    'If the current cell is null stop iterating

    If Acell.value = Null Then

        Exit For

    End If

    'Set found to false incase it was true previously

    found = False

    'Iterate through all cells from B1 to B1000

    For Each Bcell In Range("B1:B1000")

        'If the current Bcell is null stop iterating and progress to next Acell
        If Bcell.value = Null Then
            Exit For
        End If

        'If the Acell equals the Bcell then we have found a duplicate entry
        If Acell.value = Bcell.value Then
            found = True
        End If

    Next Bcell

    'If we havent found Acell in any Bcells of B1 to B1000 then add the Acell value to a new Ccell row

    If found = False Then
        Range("C1:C1000").Cells(Index, 1) = Acell.value
        'Increment the current Ccell index to we store in a new row and don't overwrite the previous row
        Index = Index + 1
    End If

   Next Acell

End Sub

No comments:

Post a Comment