Article updated on February 12, 2018

The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters.

How to use the UDF (array formula)

  1. Select cell range F3:G6
  2. Type =UniqueRecords((C3:D8) in formula bar.
  3. Press and hold CTRL + SHIFT
  4. Press Enter once
  5. Release all keys

Excel user defined function:

Function UniqueRecords(rng As Variant) As Variant()
' This udf filters unique distinct records (case sensitive)
Dim r As Single, c As Single, temp() As Variant, k As Single
Dim rt As Single, ct As Single, a As Single, b As Single
Dim i As Single, j As Integer, iCols As Single, iRows As Single

rng = rng.Value
ReDim temp(UBound(rng, 2) - 1, 0)
For r = 1 To UBound(rng, 1)
  For rt = LBound(temp, 2) To UBound(temp, 2)
    For c = 1 To UBound(rng, 2)
      If temp(c - 1, rt) = rng(r, c) Then a = a + 1
    Next c
    If a = UBound(rng, 2) Then
      a = 0
      b = 0
      Exit For
    Else
      a = 0
      b = b + 1
    End If
  Next rt
  If b = UBound(temp, 2) + 1 Then
    For c = 1 To UBound(rng, 2)
      temp(c - 1, UBound(temp, 2)) = rng(r, c)
    Next c
    ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
    b = 0
  End If
Next r

k = Range(Application.Caller.Address).Rows.Count
If Range(Application.Caller.Address).Columns.Count < UBound(rng, 2) Then
  MsgBox "There are more columns, extend user defined function to the right"
End If
If k < UBound(temp, 2) Then
  MsgBox "There are more rows, extend user defined function down"
Else
  For i = UBound(temp, 2) To k
    ReDim Preserve temp(UBound(temp, 1), i)
    For j = 0 To UBound(temp, 1)
      temp(j, i) = ""
    Next j
  Next i
End If

UniqueRecords = Application.Transpose(temp)
End Function

Where to copy vba code?

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste above vba code
  4. Exit visual basic editor

Recommended blog posts:

Download excel example file

unqiue distinct records case sensitive.xls
(Excel 97-2003  Workbook *.xls)