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

## Leave a Reply