Author: Oscar Cronquist Article last updated on November 21, 2019

AJ Serrano asks:

I have a column where each rows contains different values and I wanted to obtain the duplicate values in each rows.


COLUMN A contains these data:

3M - Asia
3M  South America
3M - Africa
3M - US
3M - Us
3M - South AMERICA
3M - Europe
3M  Australia
3M Australia
3M Europe

How do I get duplicate values and put them on the next column say column B? 3M is one of the duplicate values.


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

Array formula in cell B2:B9

=DuplicatedWords($A$2:$A$18, TRUE)

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

=DuplicatedWords($A$2:$A$18, FALSE)

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

User defined function


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


Get Rick Rothstein's Excel example file

Get the Excel file


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