PDA

View Full Version : How to catch the duplicate number in excel



tle2003
04-18-2015, 04:44 PM
I try to use conditional formatting and data validation for the below number, but it gave in correct answer (all change color even there are not any duplicate)



89011704252305482361


89011704252305482372


89011704252305482383


89011704252305482394


.....



Thank you ,

p45cal
04-19-2015, 07:30 AM
You're coming across numbers stored as text somehow being interpreted as numbers, see:
http://www.excelforum.com/excel-general/980281-different-long-numbers-formatted-as-text-are-considered-duplicates.html
http://stackoverflow.com/questions/14948531/find-duplicates-does-not-work


one solution is to use a formula in the conditional format instead, see attached workbook's conditional format.
Not as easy I know but it seems to be a work-around.

The formula I used for your sheet in the conditional format was:
=SUMPRODUCT(--(EXACT($A$2:$A$13,A2)))>1

post posting: it can be a simpler formula:
=SUMPRODUCT(--(($A$2:$A$13=A2)))>1