highlight duplicate for all cells but words are separated with comma and no space

[Christopher7878]

Hi

I need to find duplicating words in all cells of a worksheet but the words are separated by comma only, no space after the comma, and in many column, so the usual "select all", "conditional format", "highlight duplicate" doesn't catch the duplicates, and all searches only show me that way, pretty much.

I'm learning VBA so your help with that would be great thanks

In the enclosed file you can see the duplicate "C6" as two different capacitors. That cannot be in a bill of material.

Christopher

 

[vletm]

Christopher7878 - - case C6 ...
How that is duplicated?
E18 has C6 and ... where is another C6?
E7 has C6-8, but that's different, isn't it?
I quickly manually checked by columns...
> B-column has 10 duplicates
> D&G-column has 2 duplicates
Ideas?

 

[Christopher7878]

Hi vletm

The "C6-8" means C6,C7,C8 and I/engineer need to fix first before doing the comparison, but I made the other C6 in E18 deliberately so we can catch it.

As it is now there are two C6 that don't "look" like separate words and the comparison should catch that anyway.

 

[vletm]

Christopher7878
Of course, "C6-8" could mean as You write in Your second message ...
but not in Your 1st message!
the words are separated by comma only, no space after the comma
How about cell F7 .. C75-108 ...
then that should understand C75...C108 = 44words?
How about cell E10 ... there is only one 'word' or three 'words'?
Those 'minor' things should know before start to solve this.
> next You wrote that You made the other C5 in E18 ... Yes
> but You didn't upload a new file. Is there any other changes?
or only few empty rows?

 

[Christopher7878]

Hi vletm

So sorry for the confusion....

The real file is a lot larger of course and will need to be fixed first of all those things you mentioned, but I was thinking that if we can't catch the two "C6" however they appear then it wouldn't work with the real file yes?

The two "C6" are in E6 and E7 but I see your point so I also added a third "C6" in F9, no comma but with leading space from word "C59"

Yes items are separated with space, comma without space and comma with space after... just a mess.

Here's the updated file v2. Three "C6".

 

[vletm]

Christopher7878
It's important to know 'all even minor details' with clear rules
before even start to make code.
Especially, in this kind of cases!
Even 'for Your minor missing detail' would cause that all code have to rewrite!
Did You miss my 'manually checks'?
or are You try to find only and only E&F-columns data?
... eg. B-column has also duplicates ... many!
I need to find duplicating words in all cells of a worksheet...
hmm...?
Yes items are separated with space, comma without space and comma with space after...
case E7 ... maybe same C6-8 ... still not match to Your above sentence!
E6 & F9 ... match!
And one 'minor' thing for user, a larger file means longer waiting!

 

[Christopher7878]

Hi vletm

Let me rework the test excel file to include other ways data can be in column refdes, refdes2, refdes3... since if you make a more robust code then it can find other errors so that the original database can be fixed too. (Such as having different partno but same descript would indicate duplicate parts in the system and they should be combined)

I thought that to save time I just asked about duplicates in refdes, refdes2...

 

[vletm]

Christopher7878
You skipped my comments ...
Maybe as You wrote from Yourself (I/engineer) do so?
Why do You changes those rules all the time?
Press [ Do It ]-button and ... You would see something

 

[vletm]

Christopher7878
How do that sample work?

 

[Christopher7878]

It worked great in the test file but not quite in the real file.

You know what here's the real file. Sorry I should have just started with it.

Output as is from the MRP system. I added 3 more C6 and I made sure 1 spilled out to another column so that they're not all in refdes column. I left the format alone, as in C6-8 that should be C6,C7,C8 and C6 C7 C8 should be C6,C7,C8.

I'm only concern with column descript, partno, refdes, refdes2, refdes3 and refdes4.

The set of descript and partno (in a row) should be different every row since if we have different partno but same descript for 2 rows the we know that those 2 parts should be merged.

Everything in refdes, refdes2, refdes3, refdes4 must be different, only 1 instance, because they point to locations on PCB board and so can not be duplicated. You won't know which part to install in the duplicated locations.

Thanks
(There's no "Thanks" button here...)

 

[vletm]

Christopher7878 - It worked great in the test file but not quite in the real file.
Yes, that's normal! ... if the test file isn't as same as needed with the real file.
You changed a lot ... a lot!
Now, You can select by mouse those columns which You would like to check.
Select that columns header by mouse;
if it's yellow then it will check otherwise no.
... I also notice that You haven't read my comments (#2Reply) or
You haven't figure what have You written to compare Your thoughts.
If You write this and think that, then You'll get this not that.

I didn't test this much!​