Duplicate count

[dj mondo]

I would like to take a column, that has a list of phone number and i would like to display as a number how many duplicate are there?? how do i go about it, and there about 2000 phone numbers??


Please any help would be appreciated

 

[dj mondo]

please i need to figure this out, i just need a number of duplicates, they don't care what the phone numbers are just how many duplicates for the column

 

[Gary McMaster]

Quick & Dirty...

Sort the phone number column

(assuming phone numbers are in column A & Col A has header)

In B2 enter: =EXACT(A1,A2)

Fill B2 down to end of phone numbers

Copy column B to clipboard

"Paste Special, Values" back to column B (over top of self)

Sort by Column B

True values are duplicates

False values are unique

Gary

 

[dj mondo]

That really doesn't help cause i need to be able to tell the main office how many are duplicates, i need a number count, for each column. I am looking for duplicate emails, and address's. and be able to print the results out and email the results to the main office.

 

[scottylad2]

this depends on how many numbers are duplicated more than one, in the sample 125 and 124 are duplicated. Would you be counting this as 2 numbers duplicated or would you also be wanting to count the duplicates? if it's the latter, 2 countifs will work like the table below

Excel Workbook
	E	F	G
18	124	3	5
19	123	1
20	125	2
21	2525	1
22	2524	1
23	2535	1
24	124	3
25	124	3
26	125	2
Sheet1

Excel Workbook
	G
18	5
Sheet1

 

[Haseeb Avarakkan]

Try,

=COUNTA(A2:A2000)-SUM(IF(FREQUENCY(IF(A2:A2000<>"",MATCH("~"&A2:A2000,A2:A2000&"",0)),ROW(A2:A2000)-ROW(A2)+1),1))

Confirmed with CTRL+SHIFT+ENTER, not just ENTER.

 

[dj mondo]

k i tried that formula, it took a min cause the new file that i used the formula on had 40000 numbers. Now i have a new question, is there a formula, that would allow me to find the duplicate (i.e. phone numbers) and move them in to a new file, so that old file has no duplicates in them. so i end with two files one that has no dupes and one that the dupes were moved to. i hope i explained that well enough??

thank you
MoNdO

 

[scottylad2]

the quickest and safest way is probably to use advanced filter.
Give the column a header, highlight the whole column of numbers including the header, click on Advanced Filter which is on the data tab, click on copy to a new location and click into a cell where you want to copy to, make sure you also tick Unique items only and then click OK. Should be a unique list of your numbers now

 

[dj mondo]

k i guess i didn't explain well. I have multiple columns (i.e. one for first name, one for last name, one for address, and one for phone numbers.) Now if i run a formula or a filter. If it finds a duplicate number, i would like it to remove the number and everything in that row for that number.

the long formula posted here:
=COUNTA(A2:A2000)-SUM(IF(FREQUENCY(IF(A2:A2000<>"",MATCH("~"&A2:A2000,A2:A2000&"",0)),ROW(A2:A2000)-ROW(A2)+1),1))

gives me a count of how many duplicates there is. that is great that is what i need. now to remove those duplicates and put them in a new excel file. is that possible?? or once they have been removed they are gone, i just need to save under a different file name so i have files with no duplicates.

 

[dj mondo]

sorry if i don't make sense, or bothering anyone. Just never had to use excel in this manner, so i am new to this aspect of excel. And i would also like to say thank you for everyone's help with this. Thank you very very much

Thank you
MoNdO