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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.