Excel Detecting Non-Duplicate Numbers as Duplicates

[Jermine]

Hi guys, I need a help in solving the below problem.

why are these text-formatted numbers detected as duplicates in Excel when they are not duplicates? how can I resolve this issue?

202401044089384060182096877
202401044089384060183839755

202401054249397060193533360
202401054249397060137115436

202401064357846060182283301
202401064357846060102835006

202401064369434060186618052
202401064369434060187770740

202401104877615060127341660
202401104877615060178458121

202401175639533060182780633
202401175639533060143340124

202402037665712060177203979
202402037665712060182991003

202402057888457060123314899
202402057888457060132914513

 

[RoryA]

I was clearly having a senior moment yesterday! I meant to type:

=SUMPRODUCT(--($A$2:$A$1000=$A2))>1

but Alex' version will also do the trick.

 

[Jermine]

Thanks for the XL2BB, it was a little tricky getting it transferred as text:
Sligh modification to Rory's formula, try this:

=SUMPRODUCT(--($A$2:$A$1000=$A2))-1

Hi Alex Blakenburg, thank you for your help.
I transfer it as text due to the long digits in the number. otherwise, Excel detected it as scientific notation, and will display smth like this: 2.02401E+26

however, i'm encountering errors when using the formula =SUMPRODUCT(--($A$2:$A$1000=$A2))-1.

1. it highlighted rows #630, #631, and #632 as duplicates, when in fact it should only be rows #631 and #632
2. when I attempted to further test the accuracy of the formula by duplicating rows #2 to #3, it returned a result like below (highlighting row #1, and #2)
   when in fact it should be rows #2 and #3

Highlight Duplicates.xlsx
	A
1	List of Text-formatted Numbers
2	202401013665081060182884989
3	202401013665081060182884989
4	202401013666681060164119689
5	2024010136689030601111492562
6	202401013669374060124267014
7	202401013669946060148243613
8	202401013670251060162716926
9	202401013670370060193830634
10	202401013670402060189530774
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=SUMPRODUCT(--($A$2:$A$1000=$A2))-1	text	NO

Highlight Duplicates.xlsx
	A
1	List of Text-formatted Numbers
2	202401013665081060182884989
3	202401013666597060175203718
4	202401013666681060164119689
5	2024010136689030601111492562
6	202401013669374060124267014
7	202401013669946060148243613
8	202401013670251060162716926
9	202401013670370060193830634
10	202401013670402060189530774
11	202401013673719060183740205
12	202401013673736060137789722
13	202401013673791060193540205
14	2024010136745150601128987145
15	202401013675293060143445496
16	2024010136756100601152394789
17	202401013675934060105505515
18	202401013676297060174092803
19	202401013676421060167892508
20	2024010136770210601133068004
21	2024010136776900601133279395
22	2024010136781610601111484743
23	202401013678224060103838286
24	202401013678798060197744221
25	202401013681889060127606494
26	2024010136827140601128632733
27	202401013682725060146976692
28	202401013683103060192873538
29	202401013683197060167120876
30	202401013683650060122908963
31	202401013683732060177089061
32	202401013684052060164287393
33	202401013684498060126801076
34	2024010136846810601110834898
35	202401013684907060136001491
36	202401013685141060194664460
37	202401013686010060192026888
38	202401013686094060182154210
39	202401013686569060163436660
40	202401013687538060177845501
41	202401013687689060197101416
42	202401013687931060167660138
43	202401013689127060167685872
44	202401013689578060183234178
45	2024010136896480601121598784
46	2024010136899150601139266486
47	202401013689990060137584613
48	202401013690500060137826681
49	202401013691531060166509034
50	202401013691931060122158274
51	2024010136920450601114956583
52	202401013692144060183131342
53	202401013692156060187795592
54	202401013692262060162214468
55	202401013692542060193382347
56	202401013692633060178274234
57	2024010136926580601121297237
58	202401013693175060192753603
59	202401013693344060167891012
60	202401013694398060135426875
61	202401013694446060183755840
62	202401013694888060126519316
63	202401013695210060189100982
64	202401013695218060187793436
65	202401013695306060132577951
66	2024010136953230601111479473
67	202401013695598060182995712
68	2024010136958270601128138448
69	202401013695864060123721788
70	202401013696611060127866840
71	202401013697504060176586012
72	202401013697930060126753142
73	202401013698018060183631889
74	202401013698279060197340217
75	202401013698702060196911543
76	2024010136992290601111103511
77	2024010136997560601111025535
78	2024010137000050601111887687
79	202401013700046060197989892
80	202401013700889060177114467
81	202401013701211060129703938
82	202401013701549060109286284
83	202401013702473060127184742
84	202401013702534060146790792
85	202401013702720060183297789
86	2024010137029220601116368228
87	2024010137030420601111001109
88	202401013703615060102001400
89	2024010137036320601111978280
90	2024010137037410601111941828
91	202401013704026060137637212
92	202401013704155060184724143
93	2024010137042300601127271655
94	202401013704482060187844087
95	202401013704594060168344696
96	202401013705825060123534607
97	202401013706164060122017359
98	202401013706403060123161269
99	202401013706578060177496960
100	202401013708321060183868968
101	202401013710992060127665060
102	202401013711597060178858952
103	202401013712414060136044228
104	202401013712726060102085449
105	202401013712858060137318383
106	202401013713062060167751058
107	202401013713596060134002035
108	202401013713788060107963221
109	202401013713968060183294316
110	202401013714648060195538376
111	202401013715254060126497439
112	202401013715282060133308441
113	202401013715953060175606325
114	202401013716115060182975338
115	202401013716352060189114829
116	202401013718437060179443744
117	2024010137189190601139286368
118	202401013719130060167703369
119	202401013719617060126711523
120	202401013720596060183825889
121	202401013720666060127731485
122	202401013720963060122885803
123	202401013722233060102766607
124	202401013722428060127990097
125	202401013722581060165313062
126	202401013722894060102164314
127	2024010137229510601111402735
128	202401013723986060187761022
129	202401013725055060182080328
130	202401013725716060136498597
131	202401013730071060122474364
132	202401013731640060143739510
133	202401013738852060179609932
134	202401023788665060186634916
135	202401023789894060137357890
136	202401023790370060132285425
137	2024010237921510601111945727
138	202401023792315060173386619
139	202401023792504060122029389
140	202401023792564060162089389
141	202401023794090060177960024
142	2024010237940920601163990324
143	2024010237942370601111484419
144	202401023794425060178228818
145	202401023794586060125425349
146	202401023794672060169779848
147	202401023794688060189774844
148	202401023794819060177227282
149	202401023794968060139567651
150	202401023795138060182554246
151	202401023795805060102206745
152	202401023795951060137793389
153	202401023796133060189555479
154	2024010237963250601129670099
155	202401023797023060142390310
156	2024010237974130601151348766
157	202401023797475060183714796
158	2024010237974890601155870771
159	202401023798630060177766959
160	202401023799170060138210407
161	202401023799437060125139251
162	202401023800483060182270028
163	202401023800628060104212165
164	2024010238006590601121776091
165	202401023800979060129563538
166	202401023801288060172334878
167	202401023801519060185758671
168	202401023801575060197328726
169	2024010238018970601123188766
170	202401023801905060172961220
171	2024010238023610601169326306
172	2024010238023990601110181991
173	202401023802864060197418818
174	202401023803049060169541440
175	202401023803348060183856932
176	202401023803782060125134002
177	202401023804316060125717915
178	2024010238044390601139510730
179	202401023804994060195947652
180	202401023805113060109107039
181	202401023805693060197778220
182	202401023806240060182479566
183	202401023806665060137561071
184	2024010238069940601111855406
185	202401023807048060197328725
186	202401023807228060193216278
187	202401023807345060194220920
188	202401023807461060126864501
189	202401023807587060177395905
190	202401023807595060125115543
191	202401023809500060126174709
192	202401023809758060122069152
193	202401023810572060187770505
194	202401023810801060123685034
195	202401023810840060198823414
196	202401023810846060183814793
197	202401023811056060187692003
198	202401023811111060182574250
199	202401023811344060137614248
200	202401023811631060197714866
201	202401023811655060183914793
202	202401023811818060134696031
203	202401023811932060186600802
204	202401023812025060149261854
205	202401023812069060184044689
206	202401023812142060183715013
207	202401023812164060135177855
208	202401023812258060127742240
209	2024010238131750601116223480
210	202401023813606060182699952
211	202401023813697060187726160
212	202401023813773060182073886
213	202401023814195060193469952
214	202401023814375060183664197
215	202401023815621060189653878
216	202401023816124060149376798
217	202401023816221060126040342
218	202401023816314060123470690
219	202401023816360060137280955
220	202401023816536060182251957
221	202401023817107060146819424
222	202401023817168060186646064
223	202401023817407060175364569
224	202401023817733060177188229
225	2024010238177530601111939137
226	2024010238179140601128615648
227	202401023818384060182129969
228	202401023818907060122625247
229	202401023819218060183584271
230	2024010238194330601123766880
231	2024010238197980601170180887
232	2024010238198150601161112631
233	202401023819974060166904489
234	202401023821090060167191611
235	2024010238216810601111330533
236	202401023821802060127551438
237	2024010238218470601111916093
238	2024010238221690601111472644
239	202401023822225060183769224
240	202401023822249060182097010
241	2024010238222680601129266959
242	202401023822493060122119990
243	202401023822631060175963322
244	2024010238230820601111551377
245	202401023823237060164218033
246	202401023823395060182318449
247	202401023824751060173701229
248	202401023824752060175465425
249	202401023824968060176767972
250	202401023825004060182052920
251	202401023825175060197480787
252	2024010238252070601114293287
253	202401023825737060163775901
254	202401023826033060192532157
255	202401023826359060187644531
256	202401023826388060183246537
257	202401023826439060192532157
258	2024010238268720601111145977
259	2024010238269900601111401782
260	202401023827684060143302755
261	2024010238278090601111497068
262	202401023827870060182051962
263	2024010238278940601133006525
264	202401023828120060129117770
265	202401023828362060189794175
266	202401023829864060124405710
267	202401023829917060174643171
268	202401023829932060176297292
269	202401023831263060142767955
270	202401023831469060193361667
271	202401023831528060107957518
272	202401023831686060173251934
273	2024010238318330601111894110
274	202401023832646060182179768
275	2024010238326890601136817309
276	202401023832759060177153148
277	202401023832777060189614842
278	202401023833095060167590981
279	202401023833173060177562905
280	202401023833737060187940772
281	202401023834052060195172058
282	2024010238342510601124174023
283	2024010238346760601161582108
284	202401023834934060133869856
285	2024010238349870601161582108
286	202401023836232060145070023
287	202401023836358060165996911
288	202401023836531060139942928
289	202401023837083060126674564
290	202401023837107060127501117
291	202401023837250060125872678
292	202401023837447060106683165
293	2024010238375640601111235724
294	2024010238375850601110736236
295	2024010238376060601119156062
296	2024010238376350601111235724
297	202401023837683060183954651
298	202401023837756060183954652
299	202401023837848060174312691
300	202401023837852060172681155
301	202401023838021060138253110
302	202401023838153060193367791
303	2024010238382720601155510093
304	202401023838406060127242617
305	2024010238384400601161127828
306	202401023838476060189784568
307	202401023839038060135800786
308	202401023839364060149641367
309	202401023839946060183721822
310	2024010238401970601119597915
311	202401023840305060193778950
312	202401023841350060143381919
313	202401023842006060127498865
314	202401023842721060126872858
315	2024010238439950601133934966
316	2024010238449560601121980935
317	202401023846270060132127043
318	202401023846594060183287874
319	2024010238468350601139639698
320	202401023846923060184014091
321	2024010238472320601111260135
322	2024010238473970601132001313
323	202401023847425060182307877
324	202401023847599060162827368
325	2024010238476480601141969605
326	2024010238479570601111801706
327	2024010238486470601163890803
328	2024010238486870601111653262
329	202401023849122060196155171
330	202401023849396060183899834
331	202401023851972060133335643
332	202401023852574060169142655
333	202401023852640060189600595
334	202401023855639060189096462
335	202401023855774060123703063
336	202401023856513060196005991
337	2024010238572690601116969105
338	202401023857602060195923262
339	202401023858343060162786238
340	2024010238688990601121580234
341	202401023875998060183686903
342	202401023876010060189069275
343	202401023882429060128290066
344	202401023882624060167708036
345	202401033922368060187707017
346	202401033922852060125993034
347	202401033923035060183694895
348	2024010339234870601170171946
349	202401033924687060189754538
350	202401033925272060182526221
351	202401033925739060126812165
352	202401033925891060169355923
353	202401033926282060142309482
354	2024010339266690601131355974
355	202401033927254060179058916
356	202401033927942060142145243
357	202401033927954060187615339
358	202401033928084060184724282
359	202401033928229060123907228
360	202401033928395060103788507
361	2024010339287100601123391085
362	202401033929111060183740201
363	2024010339292900601123391082
364	202401033929306060134488141
365	202401033929334060189880904
366	2024010339293770601111420348
367	2024010339294920601136151023
368	202401033929576060107609973
369	202401033929609060174703148
370	202401033929651060182091127
371	202401033929755060109052338
372	202401033929990060178405131
373	202401033930384060183674192
374	202401033930440060182474865
375	2024010339305870601111056015
376	202401033930679060163988317
377	202401033930778060165237760
378	2024010339314770601111454598
379	202401033931698060126105967
380	202401033932646060134119956
381	202401033932863060188726897
382	202401033932907060126537224
383	202401033932921060128820062
384	202401033933714060177519630
385	202401033933732060172118475
386	2024010339344660601119417242
387	202401033934681060165762536
388	202401033934807060176457357
389	202401033935663060172254255
390	2024010339357270601111357404
391	202401033936048060189634471
392	202401033936400060189409100
393	2024010339372670601111435940
394	202401033938724060127417159
395	2024010339393390601111424668
396	2024010339394630601111485640
397	202401033941471060167088911
398	202401033941652060177447278
399	202401033942257060195281770
400	2024010339433630601139258628
401	202401033943648060182617468
402	202401033943775060127149128
403	202401033943870060125370798
404	202401033943901060175719996
405	202401033944029060182247751
406	202401033944643060173097366
407	202401033944786060163616501
408	2024010339448890601125872207
409	202401033945096060184035699
410	202401033945311060177350788
411	2024010339455350601111469384
412	202401033947096060123979764
413	202401033948661060182327399
414	202401033948691060182333768
415	202401033948749060182999789
416	202401033948894060133958263
417	202401033949337060182434166
418	202401033949527060148455841
419	202401033949542060122523797
420	202401033949700060193841702
421	202401033949726060122668228
422	2024010339497580601126931649
423	202401033951017060189035188
424	202401033951429060162601105
425	2024010339514600601111809428
426	2024010339521770601128866932
427	202401033952735060138111375
428	202401033952784060129709362
429	2024010339532830601111858788
430	2024010339533700601111871883
431	202401033953615060142472570
432	202401033953622060135188636
433	202401033953644060166951130
434	202401033953700060126992693
435	202401033954039060193340261
436	202401033954141060179100862
437	202401033954151060137305279
438	202401033954261060186635466
439	202401033954364060174754160
440	202401033954469060187714321
441	202401033954563060123181034
442	202401033954655060189784470
443	2024010339548280601161708256
444	2024010339551280601111441947
445	202401033955962060122977726
446	2024010339563160601121996969
447	2024010339564200601111321547
448	202401033957017060183580258
449	202401033957524060124102212
450	202401033957895060192668551
451	202401033959083060143196006
452	2024010339591460601111810899
453	202401033959224060189578645
454	202401033959487060174113674
455	2024010339595240601133756046
456	2024010339596980601123086773
457	202401033959802060168853112
458	202401033960464060104414744
459	202401033961061060189099298
460	202401033961917060189683603
461	202401033962182060184747993
462	2024010339622980601111482831
463	202401033962346060165190218
464	202401033962535060182360919
465	202401033962574060187754558
466	202401033962592060183999219
467	2024010339627440601137855179
468	202401033962948060137073856
469	202401033963052060106659401
470	202401033963146060137567648
471	202401033963179060192658732
472	202401033963265060186654377
473	202401033963370060187744243
474	202401033963467060132606207
475	202401033963525060182280372
476	2024010339635350601111999855
477	2024010339635960601128394113
478	202401033963614060132119412
479	202401033964291060137678636
480	202401033964490060183934050
481	202401033964633060189003080
482	202401033964703060166186194
483	202401033964737060182284457
484	202401033964836060182314795
485	202401033964847060137888494
486	2024010339650040601111722770
487	2024010339651150601111438685
488	2024010339652440601163570286
489	202401033965312060182174700
490	202401033965499060108895332
491	202401033965658060189504238
492	2024010339658110601121043635
493	202401033965922060148579047
494	202401033966494060168883508
495	202401033966518060176297712
496	2024010339668270601111293126
497	2024010339670320601114022282
498	2024010339671850601139150769
499	202401033967229060174237974
500	202401033967443060137023370
501	202401033967583060197793370
502	2024010339675930601112267657
503	202401033967756060132190499
504	202401033967885060179242225
505	202401033967910060175956585
506	2024010339680620601111458841
507	202401033968293060182314057
508	202401033968368060103981105
509	2024010339685730601128337166
510	2024010339686890601111966714
511	202401033968691060182151701
512	202401033968771060173479771
513	202401033968782060182091915
514	202401033968870060199717196
515	202401033968930060172920791
516	202401033968965060185750877
517	202401033969102060183184661
518	202401033969225060182336687
519	202401033969320060187744787
520	202401033969654060195536078
521	202401033969816060163654574
522	202401033970064060189827234
523	202401033970375060167931727
524	202401033970692060126224898
525	202401033971162060198807720
526	202401033971390060126079436
527	202401033971692060192202243
528	202401033972857060184008600
529	2024010339731080601110934941
530	202401033974584060183524051
531	202401033977740060122062319
532	2024010339786110601116826048
533	202401033981238060195091915
534	202401033982196060189638878
535	202401033982525060192381812
536	202401033982530060182464358
537	2024010339913280601123677495
538	2024010339934680601111473942
539	202401033994686060173963695
540	202401033999621060162974165
541	202401034008109060183733211
542	2024010340116280601120685570
543	202401034013509060182454875
544	202401034015418060199574641
545	202401034017159060142958488
546	202401034019964060133854422
547	202401044023047060122790928
548	202401044065309060187604988
549	202401044066077060127803532
550	2024010440674800601111615262
551	202401044068237060187864465
552	2024010440690170601124098390
553	202401044069871060195213010
554	202401044070021060187938352
555	2024010440702640601111520489
556	202401044070343060194806050
557	2024010440703980601126025870
558	202401044070470060183224524
559	202401044070596060148676625
560	202401044070603060183924506
561	202401044070701060182696027
562	202401044070838060163033604
563	202401044070955060127801913
564	2024010440710060601119345494
565	202401044071102060182434586
566	202401044071269060192212505
567	2024010440714160601155223672
568	202401044072937060179645052
569	202401044073497060137096107
570	202401044074081060189169079
571	202401044074171060125696627
572	202401044074301060183989079
573	202401044074483060172025966
574	202401044074646060186617412
575	202401044074732060142654208
576	202401044074772060132427686
577	2024010440748410601111408138
578	202401044074965060193369092
579	202401044075043060196564251
580	202401044075158060195523870
581	202401044075968060176002809
582	202401044076048060184026057
583	202401044076139060182096881
584	202401044076145060183757225
585	202401044077262060162706987
586	202401044077412060103820106
587	202401044077421060169349391
588	202401044077584060126412969
589	202401044077762060135022598
590	202401044077770060127184453
591	202401044079147060199453755
592	2024010440794320601110741454
593	2024010440797050601121185747
594	2024010440799600601111913872
595	202401044079994060182303636
596	202401044080723060184703029
597	202401044080876060166480922
598	202401044081132060176021009
599	202401044081356060189897382
600	2024010440824930601111526684
601	2024010440830200601121552150
602	202401044083205060177312386
603	202401044083360060179247197
604	202401044083698060126321541
605	202401044083707060189022505
606	2024010440838220601129568392
607	202401044083972060194496303
608	202401044084146060183585392
609	202401044084183060166439137
610	202401044084258060183585392
611	202401044084511060143035294
612	202401044084816060183990177
613	202401044084836060182447886
614	202401044085808060177745413
615	202401044086215060127169179
616	202401044086286060186620559
617	2024010440867490601111646594
618	202401044086799060102770880
619	202401044086861060103777488
620	202401044087173060187832668
621	202401044087628060134574053
622	2024010440884630601111402958
623	202401044088587060122683000
624	202401044088631060127175046
625	202401044089042060126592519
626	202401044089202060174554789
627	202401044089270060199435795
628	2024010440893410601111422796
629	202401044089358060147338392
630	202401044089384060182096877
631	202401044089384060183839755
632	202401044089384060183839755
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=SUMPRODUCT(--($A$2:$A$1000=$A2))-1	text	NO

 

[awoohaw]

You're applying the CF rule to the entire column, so this starts you in A1.
And the rule starts by comparing cell A2.

Change your rule to compare A1 to start
apply: =SUMPRODUCT(--($A$1:$A$1000=$A1))-1 to A1:A1000
,
or change your applies to range to start at A2.
apply:=SUMPRODUCT(--($A$2:$A$1000=$A2))-1 to A2:A1000

 

[Alex Blakenburg]

I am login off for the night and Awoohaw has addresses your issue, and is probably closer to your time zone.

Note: I went with the "-1" modification since it works and is what I thought Rory had in mind with his initial formula.
Rory's later formula using ">1" instead of "-1" is a better and more intuitive version, so would suggest swapping to that. You will still need to apply the change that Awoohaw has pointed out.