Author: Oscar Cronquist Article last updated on December 02, 2018

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

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


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

Download excel example file

Duplicate words.xls
(Excel 97-2003  Workbook *.xls)

Download Rick Rothstein´s excel example file

Word Listing Code.xls
(Excel 97-2003  Workbook *.xls)

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