Find duplicates with a string of text

[chrisellis250]

Hi all,

I have a table, and in columns H:M each cell is filled with numerous postcodes separated by a comma (i.e. DY8 3NQ, DY8 3NQ, DY8 4TN, DY5 XYZ).

Is there a way to highlight where duplicate values appear within the string of text in a cell, across all columns?

Many thanks,

Chris

[nigelog]

Found a code that deletes duplicates if any use to you

Please Login or Register  to view this content.

https://stackoverflow.com/questions/...cells-in-excel

[chrisellis250]

Hi Nige,

Thanks for the response. I am looking to keep the duplicates, so I am looking for something to highlight them when they appear in a string within a cell - as this is for a comparison when looking at when particular postcodes appear within the table.

[jindon]

See if this is how you wanted.
If not, need to see your workbook with before/after...

Please Login or Register  to view this content.

Edit: Code has been replaced for multiple dup/tripl etc.

[chrisellis250]

Hi Jindon,

Thanks for your code, much appreciated. I have tested it and it does not do what I am looking for.

[jindon]

You said

each cell is filled with numerous postcodes separated by a comma (i.e. DY8 3NQ, DY8 3NQ, DY8 4TN, DY5 XYZ).

So, my code will hi-lite the duplicate post code within a cell.
Obviously not such...

[chrisellis250]

Sorry Jindon, my description is misleading, I apologise for this.

I am looking for duplicates across the whole worksheet, as opposed to within one cell.

So for example if H19 has DY8 3NQ, DY8 3NG, DY8 3NF and J22 has DY8 3NQ in it then it would highlight DY8 3NQ - similar to how your code does, but not in one single cell.

There should not be any duplicates within one single cell, but there should be multiple duplicates in the whole table itself.

[jindon]

Dups in whole range?

Please Login or Register  to view this content.

[chrisellis250]

Almost (again my description has been poor) so looking at H:M but only colouring duplicates on each row.

So if H60 has DY8 3NQ, DY8 3NQ, DY8 4TF and J60 has DY8 3NQ then DY8 3NQ is coloured.

[jindon]

Duplicate by row,

Please Login or Register  to view this content.