# Finding duplicate numbers

#### keithbc

##### New Member
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.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.

Last edited:
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:
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.

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).

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.

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>

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.

Glad it worked out. It was one heck of a dilly to mull over, and I'm glad for the practice.

Replies
3
Views
235
Replies
7
Views
407
Replies
7
Views
230
Replies
7
Views
188
Replies
9
Views
344

1,217,679
Messages
6,137,947
Members
450,100
Latest member
mscetr

### 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.

### Which adblocker are you using?

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

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