Filter duplicate words from a cell range in excel (udf)
AJ Serrano asks:
I have a column where each rows contains different values and I wanted to obtain the duplicate values in each rows.
SAMPLE
COLUMN A contains these data:
3M - Asia
3M South America
3M - Africa
3M - US
3M - ASIA
3M - Us
3M - South AMERICA
3M - Europe
3M Australia
3M Australia
3M Europe
3M aSIA
3M US
How do I get duplicate values and put them on the next column say column B? 3M is one of the duplicate values.
Answer:
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
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 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 FunctionHow to implement user defined function in excel
- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Type your user defined function
- Exit visual basic editor
- Select a sheet
- Select a cell range
- 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)!!
Related posts:
Filter unique distinct words from a cell range in excel (udf)
Filter unique words from a range in excel (udf)
User defined function to split words in a cell range into a cell each in excel
Excel udf: Filter emails from an excel range
Filter unique distinct and duplicate values from a large data set in excel 2007
Recommended categories
Hi,
I want to thank you for the great fuction you created. They are very handy.
One technical question. I have some problem to make array that are include more than 500 cells. Do you know how this can be fixed. My guess will be that this is some memory limitation, but I am speculating. I look in the vbs code and did not see any size limitations there.
Your reply will be highly appreciated.
Krassy
@Krassy,
Off the top of my head, I do not see where there should be any limitation as your are reporting. If you would like, you can email me your workbook along with a description of the steps you take that produces the problem (so I can duplicate them here) and any error messages you get (so I know what to look for) and I will see if I can uncover the problem. My email address is rickDOTnewsATverizonDOTnet... just replace the upper case letters with the words they spell out.