Find duplicate words within a single cell

R

r121a947

New member
Joined
Jun 29, 2019
Messages
49
Reaction score
0
Points
0
Excel Version(s)
Office 365
I would like to be able to find duplicate words within a single cell. The sample data are in the J column. The max number of words is around 25; 1 is the minimum. The words will be sorted alphabetically, first, to make spotting the dupes easier.



I have a very basic idea of what might work, but no clue as to how to actually implement something like this. Any and all help is greatly appreciated.


My thoughts are:



  • Count the words in the J cell.
  • If the count is <2, move on to the next row.
  • If 2 or >, compare each word (from 1 to n-1) with its n+1 word; if no match, move on to the next n word; if a match, paste the n word being checked in the N column, then move on to the rest of n-1 words.


Once it works for a single cell, I can adapt it in a For loop to do all the rows. It can also be adapted to do the same chore in the L column.


Again, any and all help is greatly appreciated. Thanks.
 
Last edited by a moderator:
In the attached two routines, a udf and a plain sub:
Code: 
Function RemoveDups(cll)
  RemoveDups = Join(Application.WorksheetFunction.Unique(Split(cll.Value), True))
End Function

Sub blah() '..and used in a regular sub to make changes in situ:
For Each cll In Selection.Cells
  cll.Value = RemoveDups(cll)
Next cll
End Sub
To run the second on cells in situ, select the cells which need processing before running the sub blah.

edit post posting:
it would be a bit better if that line was:
Code: 
RemoveDups = Join(Application.WorksheetFunction.Unique(Split(Application.Trim(cll.Value)), True))
 

Attachments

  • ExcelGuru10925chartEx.xlsm
    17.2 KB · Views: 10
Last edited:
Thank you, very much.

The sub works perfectly.

When I try to use the udf, I get a #NAME error . . .

I copied your code, and inserted it as a Function in my Personal.xslb. When I tried to insert it as RemoveDups(cll), it said Invalid function name, so I used RemoveDups for its name. Excel added Public . . .

Because the sub works, which calls the udf, I can't understand why the udf is not working.

Having the sub work is a good enough solution.

Thanks again.
 
you may have to use something like:
=PERSONAL.XLSB!RemoveDups(C2)
in a cell.

If you use the Insert Function dialogue box (Shift + F3 on the keyboard), you can choose the User Defined ategory and find RemoveDups there prefixed by the personal workbook's name.

…or do something fancy such as: Creating a Reference to PERSONAL.XLSB for UDF's
https://www.myonlinetraininghub.com...personal-xlsb-for-user-defined-functions-udfs

or create an add-in.
 
Last edited:
Thank you.

I will try your suggestion, and I will try copying it to the proper docs.

Thanks, again.
 
Something seems to have gone wrong . . .

The sub was working, before, but now is not.

I get the same debug message whether Personal.xlsb! is added, or not.

I looked at the link you provided, but I am unable to follow the instructions, which seem to be for earlier versions of Excel.

Any and all help is greatly appreciated. Thanks.
 
I saved personal as .xlam. Got it to be an Active Application Add-In, but the sub is still not working.

The debug message is: RemoveDups = Join(Application.WorksheetFunction.Unique(Split(Application.Trim(cll.Value)), True)) in the udf

When I try to run the udf, I get: This function takes no arguments. Formula result = and RemoveDups() is inserted in the cell.

Any and all help is greatly appreciated. Thanks.
 
I tried saving as xlam (I cleared the sheet1 of everything first)and making it an active addin and it worked out of the box. Office 365.
If you've also left remnants of the udf in personal.xlsb it might be worth deleting it from there too.
 
I removed the udf from personal.xlsb, Saved, exited.

Restarted, and tried to run the sub on selected range . . . Compile error: Sub or function not defined, with RemoveDups highlighted. Adding Personal.xlam! didn't work.

Move the sub to personal. xlam? That didn't work . . .
 
personal.xlam ??
I strongly suspect anything like that shouldn't be there. Leave personal.xlsb as that and nothing similar, but remove any similar named udfs from personal.xlsb.
If still a problem I could look at it for you using TeamViewer if you want.
 
I am attaching personal.xlsb.

Please take a look at it . . .

Any and all help is greatly appreciated.

Thanks.
 
Last edited by a moderator:
That personal.xlsb contains none of my code which is good if you're going down the add-in route. Again, I suggest TeamViewer.
 
I recopied your code, and reinserted it into personal. xlsb, and the sub is working, again . . .

When I run the udf, I get a #Name! error, The formula contains unrecognized text.

The sub is perfectly fine.

Thank you for all your assistance.
 
Are you using the technique in msg#4 to insert the function into a cell?
 
I used Shift-F3 and it seems to be working . . .

Thanks, again.
 
You must log in or register to reply here.
Back
Top