How to use 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)