Finding duplicate numbers

keithbc

New Member
Joined
Mar 16, 2009
Messages
24
I have a column (I) in which i record 7 digit numbers, sometimes i use more than 1 number in each cell, each number is separated by a space, i am currently using this formula to show me if i am entering a duplicate number,
=IF(COUNTIF($I$2:I2,I2)>1,"Dup","") it is fine when there is only 1 number in a cell, the problem is it does not find duplicates if there is more than one number in the cell. Any help would be appreciated.:confused:
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Gohar Shahab

Active Member
Joined
May 17, 2006
Messages
339
Use formula this way in column B...and drag it down

==IF(COUNTIF(A:A,A1)>1,"duplicate","Not Duplicate")

1 duplicate
1 duplicate
1 duplicate
2 Not Duplicate
3 duplicate
3 duplicate
4 duplicate
4 duplicate
5 duplicate
5 duplicate
6 Not Duplicate
7 Not Duplicate
8 Not Duplicate
9 Not Duplicate
10 Not Duplicate


I have a column (I) in which i record 7 digit numbers, sometimes i use more than 1 number in each cell, each number is separated by a space, i am currently using this formula to show me if i am entering a duplicate number,
=IF(COUNTIF($I$2:I2,I2)>1,"Dup","") it is fine when there is only 1 number in a cell, the problem is it does not find duplicates if there is more than one number in the cell. Any help would be appreciated.:confused:
 
Last edited:

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Ugly solution (but it works):
Excel Workbook
AB
17183053 1043587 4597167 4335521dup
21143840not dup
37183053dup
49662236not dup
59587218 7978343not dup
68188758 7697472not dup
72339207 6259563not dup
88781446 5488206not dup
94597167dup
109186879 9242066not dup
119601945 8610198not dup
126883568not dup
139877802dup
145751849 5816467not dup
157909766not dup
164335521 1054716dup
178155626 6306355dup
189572214not dup
196306355dup
202878995not dup
217759814 7041817 9292587dup
222883090not dup
235681118not dup
249292587dup
259350603 6175267not dup
264626929not dup
276299372 9877802dup
286299372 1043587dup
299313136 7217523not dup
Sheet1
#VALUE!
</td></tr></table></td></tr></table>

This will only work for up to 6 numbers in the same field. If you put 7 in the same cell, it won't match them. You can add more by changing the {1,9,17,26,35,44} to include the next multiple of 9, -1.

So for instance: {1,9,17,26,35,44,53}
 
Last edited:

keithbc

New Member
Joined
Mar 16, 2009
Messages
24
Hi Sal
my data is in column I, i have changed the reference from A to I, but the only answer i get is "not dup". can this formula be dragged down the column, if not please tell me how you specify the "names in formula"
thanks again.
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457

ADVERTISEMENT

Be sure you have a named range called 'Numbers' that has the data in it, and that the formula is confirmed with Control-Shift-Enter (not just enter).
 

keithbc

New Member
Joined
Mar 16, 2009
Messages
24
Sal

I have named column I1 to I250 as numbers, this is the column that has the 7 digit numbers in, i then copied the formula into J1 (without {}and did Cntr+Shift+ Enter, i then dragged this down the J column to 250. i don't know if this is correct, all i get is 'not dup' and it does not find any duplicates.
Sorry for this but a bit more info on how to use the formula would be appreciated.
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457

ADVERTISEMENT

I have no idea why it isn't working for you.

Here is the same with I and J:

Excel Workbook
IJ
17183053 1043587 4597167 4335521dup
21143840not dup
37183053dup
49662236not dup
59587218 7978343not dup
68188758 7697472not dup
72339207 6259563not dup
88781446 5488206not dup
94597167dup
109186879 9242066not dup
119601945 8610198not dup
126883568not dup
139877802dup
145751849 5816467not dup
157909766not dup
164335521 1054716dup
178155626 6306355dup
189572214not dup
196306355dup
202878995not dup
217759814 7041817 9292587dup
222883090not dup
235681118not dup
249292587dup
259350603 6175267not dup
264626929not dup
276299372 9877802dup
286299372 1043587dup
299313136 7217523not dup
Sheet1
#VALUE!
</td></tr></table></td></tr></table>
 

keithbc

New Member
Joined
Mar 16, 2009
Messages
24
Sal
it works perfctly, thankyou very much for your help. Problem was that the cell field was General, as soon as i converted it to Number it worked perfectly.
Thanks again for making my life easier.
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Glad it worked out. It was one heck of a dilly to mull over, and I'm glad for the practice.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,326
Messages
5,595,517
Members
413,995
Latest member
waealu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top