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

.csharpcode, .csharpcode pre
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
background-color: #f4f4f4;
width: 100%;
margin: 0em;
.csharpcode .lnum { color: #606060; }

Leave a Reply

Your email address will not be published. Required fields are marked *