### 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