Filter unique strings from a cell range
This blog post describes how to create a list of unique words from a cell range. Unique words are all words that exist once in a given cell range.
Cell range B3:B15 contains values, see picture above.
What's on this page
1. Filter unique strings in a range - UDF
Rick Rothstein (MVP - Excel) helped me out here with a powerful user defined function (udf).
Array formula in cell B2:B23
This is how you 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.
Array formula in cell C2:C23
Select all the cells to be filled, then type the above formula into the Formula Bar and press CTRL+SHIFT+ENTER
User defined function
Instructions
- You can select far more cells to load the formulas in than are required by the list. The empty text string will be displayed for cells not having an entry.
- You can specify a larger range than the there are filled in cells as the argument to these macros to allow for future entries in the column.
- You can specify whether the listing is to be case sensitive or not via the optional second argument with the default value being FALSE, meaning duplicated entries with different casing like One, one, ONE, onE, etc.. will all be treated as if they were the same word with the same spelling. If you pass TRUE for that optional second argument, then those words would all be treated as if they were different words.
- For all the "Case Insensitive" listing, the words are listed in Proper Case (first letter upper case, remaining letters lower case). The reason being if you had One, one and ONE then there is not reason to prefer one version over another, so I solved the problem by using Proper Case throughout.
VBA Code:
Function UniqueWords(Rng As Range, Optional CaseSensitive As Boolean) As Variant Dim X As Long, WordCount As Long, List As String, Uniques As Variant, Words() As String List = WorksheetFunction.Trim(Replace(Join(WorksheetFunction.Transpose(Rng)), Chr(160), " ")) Words = Split(List) For X = 0 To UBound(Words) If CaseSensitive Then If UBound(Split(" " &; List & " ", " " & Words(X) & " ")) = 1 Then Uniques = Uniques & Words(X) & " " List = Replace(List, Words(X), "") End If Else If UBound(Split(" " & UCase(List) & " ", " " & UCase(Words(X)) & " ")) = 1 Then Uniques = Uniques & StrConv(Words(X), vbProperCase) & " " List = Replace(List, Words(X), "") End If End If Next Uniques = WorksheetFunction.Trim(Uniques) Words = Split(Uniques) If Application.Caller.Count > UBound(Words) Then Uniques = Uniques & Space(Application.Caller.Count - UBound(Words)) End If UniqueWords = WorksheetFunction.Transpose(Split(Uniques)) End Function
How to implement user defined function in excel
- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor
- Select a sheet
- Select a cell range
- Type =UniqueWords($A$2:$A$18, TRUE) into formula bar and press CTRL+SHIFT+ENTER
Get Rick Rothstein's Excel example file
Many thanks to Rick Rothstein (Mvp - Excel)!!
2. Filter unique strings in a range - Excel 365
The image above demonstrates a formula in cell D3 that extracts unique strings based on the space character as a delimiting character. A unique string is a string that exists only once in a cell range.
Note, string "Us" is equal to "US", in other words, this is not a case-sensitive formula.
Excel 365 formula in cell D3:
Explaining formula
Step 1 - Join cell values
The TEXTJOIN function combines text strings from multiple cell ranges and also use delimiting characters if you want.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
TEXTJOIN(" ", TRUE, B3:B15)
returns
"3M - Asia 3M South America 3M - Africa 3M - US 3M - ASIA 3M - Us 3M - South AMERICA 3M - Europe 3M Australia 3M Australia 3M Europe 3M aSIA 3M US".
Step 2 - Split strings based on space as a delimiting character
The TEXTSPLIT function splits a string into an array across columns and rows based on delimiting characters.
TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])
TEXTSPLIT(TEXTJOIN(" ", TRUE, B3:B15), , " ", TRUE, 0)
becomes
TEXTSPLIT("3M - Asia 3M South America 3M - Africa 3M - US 3M - ASIA 3M - Us 3M - South AMERICA 3M - Europe 3M Australia 3M Australia 3M Europe 3M aSIA 3M US", , " ", TRUE, 0)
and returns
{"3M"; "-"; "Asia"; "3M "; "South"; "America"; "3M"; "-"; "Africa"; "3M"; "-"; "US"; "3M"; "-"; "ASIA"; "3M"; "-"; "Us"; "3M"; "-"; "South"; "AMERICA"; "3M"; "-"; "Europe"; "3M "; "Australia"; "3M"; "Australia"; "3M"; "Europe"; "3M"; "aSIA"; "3M"; "US"}.
Step 3 - List unique strings
The UNIQUE function extracts both unique and unique distinct values and also compare columns to columns or rows to rows.
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(TEXTSPLIT(TEXTJOIN(" ", TRUE, B3:B15), , " ", TRUE, 0), , TRUE)
becomes
UNIQUE({"3M"; "-"; "Asia"; "3M "; "South"; "America"; "3M"; "-"; "Africa"; "3M"; "-"; "US"; "3M"; "-"; "ASIA"; "3M"; "-"; "Us"; "3M"; "-"; "South"; "AMERICA"; "3M"; "-"; "Europe"; "3M "; "Australia"; "3M"; "Australia"; "3M"; "Europe"; "3M"; "aSIA"; "3M"; "US"}, , TRUE)
and returns "Africa".
Unique values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
The formula in cell E14 adds a number from column C if the corresponding value in column B is unique […]
A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]
This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]
User defined function category
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
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
Paste image link to your comment.