Filter unique words from a range in excel (udf)

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Unique values > Filter unique words from a range in excel (udf)

← Previous post -

Filed in Excel, Unique values, User defined functions (udf) on Sep.21, 2010. Email This article to a Friend

Overview

This blog post describes how to create a list of unique words from a cell range. Unique words are all words not having duplicates.

Cell range A2:A14 contains words, see picture.

Rick Rothstein (MVP - Excel) helped me out here with a powerful user defined function (udf).

Array formula in cell B2:B23

=UniqueWords($A$2:$A$18, TRUE) + CTRL + SHIFT + ENTER

Select all the cells to be filled, then type the above formula into the Formula Bar and press CTRL+SHIFT+ENTER

Array formula in cell C2:C23

=UniqueWords($A$2:$A$18, FALSE) + CTRL + SHIFT + ENTER

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