Finding duplicate numbers

[keithbc]

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.

 

[Gohar Shahab]

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.

 

[Sal Paradise]

Ugly solution (but it works):

Excel Workbook
	A	B
1	7183053 1043587 4597167 4335521	dup
2	1143840	not dup
3	7183053	dup
4	9662236	not dup
5	9587218 7978343	not dup
6	8188758 7697472	not dup
7	2339207 6259563	not dup
8	8781446 5488206	not dup
9	4597167	dup
10	9186879 9242066	not dup
11	9601945 8610198	not dup
12	6883568	not dup
13	9877802	dup
14	5751849 5816467	not dup
15	7909766	not dup
16	4335521 1054716	dup
17	8155626 6306355	dup
18	9572214	not dup
19	6306355	dup
20	2878995	not dup
21	7759814 7041817 9292587	dup
22	2883090	not dup
23	5681118	not dup
24	9292587	dup
25	9350603 6175267	not dup
26	4626929	not dup
27	6299372 9877802	dup
28	6299372 1043587	dup
29	9313136 7217523	not 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}

 

[keithbc]

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]

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]

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]

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

Here is the same with I and J:

Excel Workbook
	I	J
1	7183053 1043587 4597167 4335521	dup
2	1143840	not dup
3	7183053	dup
4	9662236	not dup
5	9587218 7978343	not dup
6	8188758 7697472	not dup
7	2339207 6259563	not dup
8	8781446 5488206	not dup
9	4597167	dup
10	9186879 9242066	not dup
11	9601945 8610198	not dup
12	6883568	not dup
13	9877802	dup
14	5751849 5816467	not dup
15	7909766	not dup
16	4335521 1054716	dup
17	8155626 6306355	dup
18	9572214	not dup
19	6306355	dup
20	2878995	not dup
21	7759814 7041817 9292587	dup
22	2883090	not dup
23	5681118	not dup
24	9292587	dup
25	9350603 6175267	not dup
26	4626929	not dup
27	6299372 9877802	dup
28	6299372 1043587	dup
29	9313136 7217523	not dup
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[keithbc]

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]

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