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
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
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
If UBound(y) < 1 Then
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.
Missing = y ' otherwise just return the array horizontally.