## Filter unique distinct values (case sensitive) [UDF]

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

A User defined Function in Excel is a custom function that anyone can use, simply copy the code to your workbook and you are good to go, see details below.

Array formula in cell D3:D10:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Have you read the article that extracts unique distinct values (case sensitive) using an array formula?

### User defined Function Syntax

CSUnique(*rng*)

### Arguments

Parameter |
Text |

rng |
Required. The range you want to use. |

### VBA

'Name function and argument Function CSUnique(rng As Range) 'Declare variables and data types Dim cell As Range, temp() As String, i As Single, iRows As Integer 'Redimension array variable so it can grow using Redim Preserve statement ReDim temp(0) 'Iterate through each cell in range For Each cell In rng 'Iterate through values in array variable temp For i = LBound(temp) To UBound(temp) 'If value is equal to cell value If temp(i) = cell Then 'Add one to variable i i = i + 1 'Stop For ... Next statement Exit For End If Next i 'Subtract variable i with 1 i = i - 1 'If value in array variable temp is not equal to cell value If temp(i) <> cell Then 'Save cell value to array variable temp temp(UBound(temp)) = cell 'Add another container to array variable temp ReDim Preserve temp(UBound(temp) + 1) End If Next cell 'Count how many cells have been used when entering UDF iRows = Range(Application.Caller.Address).Rows.Count 'To prevent error value the UDF adds blanks to remaining containers 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 'Return array variable temp to worksheet CSUnique = Application.Transpose(temp) End Function End Function

### Where to copy vba code?

- Press Alt-F11 to open visual basic editor
- Right click on your workbook in 'Project Explorer' window
- Click 'Insert'
- Click 'Module'
- Copy above VBA code
- Paste VBA code to the code module
- Exit visual basic editor

### Download Excel file

Enter your email to receive the workbook.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 […]

How to count word frequency in a cell range [UDF]

This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

List files in a folder and subfolders [UDF]

This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Filter unique distinct words from a cell range [UDF]

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

Count comma separated values [UDF]

I received an email from one of my seven blog readers. In Excel, I have a column, say A, with some […]

Lookup multiple values in one cell [UDF]

Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]

The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]

List permutations without repetition [UDF]

This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

Find positive and negative amounts that net to zero [UDF]

Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]

### One Response to “Filter unique distinct values (case sensitive) [UDF]”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

[…] Excel udf: Filter unique distinct values (case sensitive) […]