Author: Oscar Cronquist Article last updated on October 08, 2018

The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters.

How to use udf

  1. Select cell range D3:D10
  2. Type =CSUnique(B3:B10) in formula bar.
  3. Press and hold CTRL + SHIFT
  4. Press Enter once
  5. Release all keys

Excel user defined function:

Function CSUnique(rng As Range)
Dim cell As Range, temp() As String, i As Single, iRows As Integer
ReDim temp(0)
For Each cell In rng
  For i = LBound(temp) To UBound(temp)
    If temp(i) = cell Then
       i = i + 1
       Exit For
    End If
  Next i
  i = i - 1
  If temp(i) <> cell Then
    temp(UBound(temp)) = cell
    ReDim Preserve temp(UBound(temp) + 1)
  End If
Next cell
iRows = Range(Application.Caller.Address).Rows.Count
If iRows < UBound(temp) Then
  temp(iRows - 1) = "More values.."
  For i = UBound(temp) To iRows
    ReDim Preserve temp(UBound(temp) + 1)
    temp(UBound(temp)) = ""
  Next i
End If
CSUnique = Application.Transpose(temp)
End Function
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

Download excel example file

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