Auto-identifying duplicate individual words in different cells

[masternomore]

Identifying duplicate cells is easy with the conditional layout function.

But not if you are looking for individual words.

Example: "Sample Product" and "Sample Item" should result in the word "Sample" as duplicate.

Box of virtual cigars for the one solving this.

Many thanks in advance!

[Kaper]

I'm a bit skeptical if it can be done as much "on-the-fly" as the conditional formatting. May be. I haven't tried. It depends mostly on the size of the range where it shall be applied.

So I develped the code which at the moment is just called by the user (could be made automatic later on). If some range (more than 1 cell is selected, the code is executed only in this range. If just one cell is selected, the code is executed on the whole usedrange of the sheet.

The file contains macro mark_word_duplicates - to call it use Alt+F8 and runt this macro

Try it on your data.

[masternomore]

Many thanks, Kaper, but I am a rookie.
Can you please let me know how to make this work on my excel file?

Best,
NMNM

[Kaper]

1) see yellow banner at the top of page

2) general concept:
Open your file,
Open my excel file,
Press Alt+F11 - you shall see the code in Visual Basic Editor.
Copy it .
Close VBE
Close my file
Press Alt+F11 to open Visual Basic Editor again.
Alt+I M will insert new module
Paste the code
Close VBE
Save your file in macro-enabled format (like xlsm).


press again

[masternomore]

Many thanks, it works.
BUT: Excel is not responding after few moments. Only few words have been recognized. See attached test screenshot. (i cannot share the original file because of the blocked program).
If you would have time to look at this that would be magnificent!

Best,
MNM

[Kaper]

Let's see if I can make a small improvement against this "Excel not-responding problem".
I suppose it could happen when for some reason an empty string has been passed to nested procedure.
I included measures against it in the code.

Unfortunately, I cannot publish the code just in text - the forum software (Sucuri) recognizes parts of it as an attempt to inject SQL code, so let's do it the same way as previously: transfer the code from my file to yours.

Try it. Shall be better

[masternomore]

FABULOUS!!!!!
This is extremely smart, helpful and time saving for one of my reoccurring tasks.
Don't know how to start thanking you, Kaper!

All the best,
MNM

[masternomore]

Ooops.
Just started working and found some bugs still, such as false positives and false negatives.
I attach part of the worksheet fyi, in case you still have time.

many thanks,
MNM

[Kaper]

Interesting. I'll look into the code. For sure there is a bug. What's worse - only some cases are false pos/negs. I'll try to go step by step through isolated cases, but at earliest this evening. Sorry.

[Kaper]

Before starting digging into code, I've opened the file and run the code (not selecting any range, so all non-empty cells were taken into account) and … seems it is working fine.
All words in "A. Schulman Plastics" were highlighted red (while you had in a file one not highlighted), same with Casa International, etc. The only thing - the code recognizes at the moment upper and lower case letters so For is different than for or Exmar is different than EXMAR. Shall it be changed, the lcase (or ucase) function shall be added to three places of code. I did it.

But let me emphasize - doing it Range by Range (like selecting column A and running the code then selecting column B and running the code) will result to duplicate checking only within selected range.

Enclosed is version with is letter case insensitive. (it's the only change, so if case sensitivity is needed - you may stick to previous version)

And final comment - code does not care about cells being visible or hidden by applying filter. It does the coloring and changes anyway. If it is the source of the problem, so you would like to apply code only to visible cells - see the second version of attached file

[masternomore]

Many thanks once more.

Diving in to the work, I noticed that the only issue remaining is that the system does not discriminate between full and part of words.
So 'Hool' is also part of 'School'. Here Hool is then colored red.

Also I found a formula error 5 popup as follows: If InStr(" .,"":!", Mid(theCell.Value, j - 1, 1)) = 0 Then
This was highlighted yellow.
("Invalid procedure call or invalid argument")

Thanks in advance if you still have some minutes.

Best,
MNM

[Kaper]

Tried to generate such errors. but couldn't
May be there are some special "features" in your data (invisible character between Sc and hool ?.

As for code execution also haven't got breaks.
anyway added one security measure here and changed in a row above the one highlighted yellow <> to > in if instruction.

See the test with School and Hool properly recognized (also the code inside has the above mentioned correction

[masternomore]

Runs like a dream now. Many thanks for your time and expertise once more!! Very very helpful.
MNM

[Kaper]

Glad to hear that.

As it seems, that it takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.