Conditional Formatting Partially Working

[raychow22]

Hi Forum,

I am having trouble getting conditional formatting for duplicate values. I have approx 15XX unique IDs which followed

"1A-1", "1A-2, "1A-3...................."; "1B-1", "1B-2", 1B-3............." ... last digit goes up to 4; letter goes up to E, first digit goes to up 70.

So I'm experiencing duplicates on some of these mostly from the letter "E" digits from 1-7 and 10-70 (incremented in 10's).



What is the problem here? is Excel not reading the zero's thus it counts it as a duplicate? All these unique IDs should not produce duplicates.

[shukla.ankur281190]

Welcome to excel forum ...


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[raychow22]

Welcome to excel forum ...


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Okay will do. Attached!

[shukla.ankur281190]

Surprise to see it, but I wish to let you know that whenever we need to check how many entries are duplicate we do use countif function which can allow us to know actual duplicate numbers. I tried the same countif function and it is producing 2 no matter why but you can read a post here in link below.

https://exceljet.net/formula/count-l...ithout-countif

However you can use below formula for duplicate value in conditional formatting.

Please Login or Register  to view this content.