Obviously these tasks do different things! But I am using them to find out if I have duplicates :)

In excel I have a column which is a concatenation of all of the other columns. (The formula is copied down across the entire dataset)

If I select this column and get it to 'conditionally highlight duplicates' hardly anything is highlighted i.e. it cannot identify them as duplicates.

but if I use the 'remove duplicates' option, it finds all of them without problem

What is causing this behaviour and how can I prevent it?

There are about 40,000 rows and 51 columns that include strings, dates, etc


concat string length is about 1000 characters,
example snippets from concat string= 01102991666920130No0EUR0DEFAULTDEFAULT11 another example= 107219No818.517149URA2001686920140Yes0GBP

  • How do you create the CF rule? Could you provide a sample about CF cannot find the duplicate but Remove Duplicate option can? What's the file format? – Lee Oct 3 '18 at 7:51
  • 3
    Can you provide more detail about the data, some examples of duplicates that are found by one but not the other, etc.? There isn't much to go on here. – fixer1234 Oct 3 '18 at 8:11
  • it's just using the built in 'conditional formatting' "highlight cells" "duplicate values" – adolf garlic Oct 3 '18 at 8:34
  • updated question with more info – adolf garlic Oct 3 '18 at 8:40
  • 2
    Still not much for us to go on. I suggest you read this then come back and edit your question. – cybernetic.nomad Oct 3 '18 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.