Answer:

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

Array formula in cell B2:B9

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:C9

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 DuplicatedWords(Rng As Range, Optional CaseSensitive As Boolean) As Variant
  Dim X As Long, WordCount As Long, List As String, Duplicates 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
        Duplicates = Duplicates & Words(X) & " "
        List = Replace(List, Words(X), "", 1, -1, vbBinaryCompare)
      End If
    Else
      If UBound(Split(" " & UCase(List) & " ", " " & UCase(Words(X)) & " ")) > 1 Then
        Duplicates = Duplicates & StrConv(Words(X), vbProperCase) & " "
        List = Replace(List, Words(X), "", 1, -1, vbTextCompare)
      End If
    End If
  Next
  Duplicates = WorksheetFunction.Trim(Duplicates)
  Words = Split(Duplicates)
  If Application.Caller.Count > UBound(Words) Then
    Duplicates = Duplicates & Space(Application.Caller.Count - UBound(Words))
  End If
  DuplicatedWords = WorksheetFunction.Transpose(Split(Duplicates))
End Function

How to implement user defined function in excel

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

Get the Excel file

Duplicate-words1.xls

Get Rick Rothstein's Excel example file

Get the Excel file

Word-Listing-Code1.xls

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

User defined function category

Analyze word frequency in a cell range
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

Extract unique distinct values in a filtered list
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]

Excel categories

Comments (6)