This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words are all words but duplicate words are only listed once.

Cell range A2:A14 contains words, see picture below.

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

Array formula in cell B2:B23

=ListOfWords($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

=ListOfWords($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

1. 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.
2. 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.
3. 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.
4. 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 ListOfWords(Rng As Range, Optional CaseSensitive As Boolean) As Variant
Dim X As Long, Index As Long, List As String, Words() As String, LoW As Variant
With WorksheetFunction
Words = Split(.Trim(Replace(Join(.Transpose(Rng)), Chr(160), " ")))
LoW = Split(Space(.Max(UBound(Words), Application.Caller.Count) + 1))
For X = 0 To UBound(Words)
If InStr(1, Chr(1) & List & Chr(1), Chr(1) & Words(X) & Chr(1), 1 - Abs(CaseSensitive)) = 0 Then
List = List & Chr(1) & Words(X)
If CaseSensitive Then
LoW(Index) = Words(X)
Else
LoW(Index) = StrConv(Words(X), vbProperCase)
End If
Index = Index + 1
End If
Next
ListOfWords = .Transpose(LoW)
End With
End Function

How to copy above code to your workbook

1. Press Alt-F11 to open visual basic editor
2. Press with left mouse button on Module on the Insert menu
3. Copy and paste above user defined function to code module
   
4. Exit visual basic editor
5. Select a sheet
6. Select a cell range
7. Type =ListOfWords($A$2:$A$18, TRUE) into formula bar and press CTRL+SHIFT+ENTER

Get the Excel file

Unique-distinct-words1.xls

Get Rick Rothstein´s excel example file

Get the Excel file

Word-Listing-Code1.xls

Many thanks to Rick Rothstein (Mvp - Excel)!!

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Setting up your work hours in a weekly schedule

The image above demonstrates conditional formatting highlighting hours outside work hours, those cells are filled with grey except weekends. Conditional formatting […]

Vlookup with multiple matches returns a different value

Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above […]

Extract a unique distinct list based on a condition [Pivot Table]

Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]

Count digits and ignore duplicates

Question: I have a question that I can’t seem to find an answer to: I want to make a full […]

Extract unique distinct values sorted based on sum of adjacent values

Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]

Compare two columns and highlight values in common

A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]