Filter unique distinct values (case sensitive) [Excel UDF]
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
- Select cell range D3:D10
- Type =CSUnique(B3:B10) in formula bar.
- Press and hold CTRL + SHIFT
- Press Enter once
- 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?
- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste above vba code
- Exit visual basic editor
Download excel example file
Filter case sensitive unqiue distinct values.xls
(Excel 97-2003 Workbook *.xls)
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
You have quite a few options to choose from if you are looking for a way to create a unique […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
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 […]
This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]
This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]
One Response to “Filter unique distinct values (case sensitive) [Excel UDF]”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
[…] Excel udf: Filter unique distinct values (case sensitive) […]