Calculate Duplicate Frequency

[MisterMeehan]

Hi,


New poster here :wink:


I have a large dataset (~100,000 rows) where I am trying to count the number of duplicate values in a given column. (There are a lot!)


I would like to generate two columns showing which numbers are duplicates (column 1) and how many times (frequency) that each number is repeated (column 2). Please see example below.

How do I do that?


Sorry if this is an obvious thing to work out, but I have no experience with anything other than rudimentary tasks in Excel. Furthermore, I cannot see an example of how to do this in the archives.


I trust this can be calculated using a formula because I wouldn’t know how to apply any Visual Basic. Please advise how to work this out in easy steps.


Thanks for any help given.



[TABLE="class: cms_table"]
[TR]
[TD][/TD]
[TD]Numbers

[/TD]
[TD][/TD]
[TD]DuplicateNo.

[/TD]
[TD]Frequency

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30

[/TD]
[TD][/TD]
[TD]10

[/TD]
[TD]2

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30

[/TD]
[TD][/TD]
[TD]20

[/TD]
[TD]4

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10

[/TD]
[TD][/TD]
[TD]30

[/TD]
[TD]2

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10

[/TD]
[TD][/TD]
[TD]40

[/TD]
[TD]3

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]40

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]40

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]40

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

 

[DRSteele]

I would suggest you try the COUNTIF or COUNTIFS functions, like this. Note that I've selected the entire column A; you can select the range A2 to A100000 or whatever if you like. Also, watch this and the other videos to learn everything you ever wanted to know about Excel's counting with criteria. https://www.youtube.com/watch?v=ohGgZ5ik4Xo

Excel 2012
	A	B	C
1	Numbers	DuplicateNo.	Frequency
2	30	10	2
3	30	20	4
4	10	30	2
5	10	40	3
6	40
7	40
8	40
9	20
10	20
11	20
12	20
Sheet48

Cell Formulas
Range	Formula
C2	=COUNTIFS(A:A,B2)
C3	=COUNTIFS(A:A,B3)
C4	=COUNTIFS(A:A,B4)
C5	=COUNTIFS(A:A,B5)

 

[Aladin Akyurek]

Given that you have a huge set, try to run a pivot table...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]Numbers[/td][td][/td][td][/td][td]Row Labels[/td][td]Count of Numbers[/td][/tr]

[tr][td]

2​

[/td][td]

30​

[/td][td][/td][td][/td][td]10[/td][td]

2​

[/td][/tr]

[tr][td]

3​

[/td][td]

30​

[/td][td][/td][td][/td][td]20[/td][td]

4​

[/td][/tr]

[tr][td]

4​

[/td][td]

10​

[/td][td][/td][td][/td][td]30[/td][td]

2​

[/td][/tr]

[tr][td]

5​

[/td][td]

10​

[/td][td][/td][td][/td][td]40[/td][td]

3​

[/td][/tr]

[tr][td]

6​

[/td][td]

40​

[/td][td][/td][td][/td][td]Grand Total[/td][td]

11​

[/td][/tr]

[tr][td]

7​

[/td][td]

40​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

40​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[MisterMeehan]

Hi and thank you for that explanation and I looked at that video. But this is not giving me a solution. Perhaps I didn't explain it clearly.

I only have column A (telephone numbers). I need to generate column B (duplicate telephone numbers) and column C (the number of duplicates).

How would I go about that?

Thanks again.

PS. Another person suggested a pivot table. Would that help?

 

[MisterMeehan]

Thanks for the suggestion of the pivot table. I am not sure how it helps, but I will try it.

How did you generate columns E and F? Because that is what I am trying to do. Did you use COUNTIFS to get those results?

Thanks

 

[DRSteele]

Aladin's suggestion of pivot table (which is what he did for columns E and F) is the best option here because pivot tables automatically find all the duplicates and count them (and can do tons of other calculations). There is a way to use other complex formulas to create the list of duplcates and then Countifs to count them.

 

[Drrellik]

100,000 phone numbers if you are state side the way numbers are formatted with area code and the exchange would only allow for a block of 10,000 number for one exchange (area code) xxx-0000, xxx-9999 and 100,000 would make it a large pivot table if you actually have 90,000 unique numbers with 10,000 duplicates. have you tried sorting small to large and looking at conditional formatting to get an idea of how many duplicates really have first. then perhaps rework the problem

 

[MisterMeehan]

Okay thanks for the guidance on this. I will wait until I hear from Aladin on how he got that pivot table to catch all the duplicates. Hoping this task is not as difficult as it looks.

Cheers

 

[MisterMeehan]

Yes, there are 100,000 phone numbers, and each row is meant to have a unique customer-telephone relationship, but as it turns out there are some 30,000 duplicates which is a huge number to deal with (Somebody tampered with the data... but that is another story).

As you can gather, I am not very pro with this, but have got the telephone column sorted so that there is a logical sequence whereby all duplicates are bunched together (but still interspersed amongst non-duplicates), so far so good, but now the task is to calculate and table which numbers are duplicated and how many times the duplication occurs for those numbers.

Seems simple... but am cracking me head on this

Thanks!

100,000 phone numbers if you are state side the way numbers are formatted with area code and the exchange would only allow for a block of 10,000 number for one exchange (area code) xxx-0000, xxx-9999 and 100,000 would make it a large pivot table if you actually have 90,000 unique numbers with 10,000 duplicates. have you tried sorting small to large and looking at conditional formatting to get an idea of how many duplicates really have first. then perhaps rework the problem

 

[Drrellik]

are you wanting to remove the duplicates?