Article updated on February 12, 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.."
Else
  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)

Recommended posts

Filter unique distinct records (case sensitive)

The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. How to use the […]

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Case sensitive lookup and return multiple values

The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]

How to extract a case sensitive unique list from a column

My definition of unique values are values that exist only once in a cell range. The image below shows you […]

INDEX MATCH – Case sensitive

The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]

Filter unique distinct and duplicate values from a large data set in excel 2007

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]