Useful Excel Macros #1â€“compare two columns – Tales from the Crypto

# Useful Excel Macros #1â€“compare two columns

I often need to compare two columns, and get a list in a third column of the items that are in one column, but not the other.

Every solution I find online has one common problem â€“ the third column is full of blanks in between the items. I donâ€™t want blanks. I want items.

So I wrote this function, which returns an array of the missing items â€“ items which are in the first parameter, but not in the second.

Iâ€™m probably missing a trick or two (Iâ€™m particularly not happy with the extra element in the array that has to be deleted before the end), so please feel free to add to this in the comments.

```Public Function Missing(ByRef l_ As Range, ByRef r_ As Range) As Variant()
' Returns a list of the items which are in l_ but not in r_
' Note that you need to put this formula into a range of cells as an array formula.
' So select a range, then type =Missing(\$A:\$A,\$B:\$B), and press Ctrl-Shift-Enter
' If the range is too big, you'll get lots of N/A cells
Dim i As Long ' loop through l_
Dim l_value As Variant ' current value in l_
Dim y() As Variant ' Temp array to store values found
ReDim y(0)

For i = 1 To l_.Count ' Loop through input

l_value = l_.Cells(i, 1) ' Get current value

If Len(l_value) = 0 Then ' Exit when current value is empty
GoTo exitloop
End If

If r_.Find(l_value) Is Nothing Then ' Can't find current value => add it to the missing
ReDim Preserve y(UBound(y) + 1) ' Change array size
y(UBound(y) - 1) = l_value ' Add current value to end
End If
Next i
exitloop:
If UBound(y) < 1 Then
Return
End If
ReDim Preserve y(UBound(y) - 1)
If Application.Caller.Rows.Count > 1 Then ' If we were called from a vertical selection
Missing = Application.Transpose(y) ' Transpose the array to a vertical mode.
Else
Missing = y ' otherwise just return the array horizontally.
End If
End Function```