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.

	Numbers		DuplicateNo.	Frequency
	30		10	2
	30		20	4
	10		30	2
	10		40	3
	40
	40
	40
	20
	20
	20
	20

 

[Drrellik]

I don't think the basic quantitative concept of logarithmic functions or properties is going to help him count how many duplicate phone numbers he has in a single column of number's. I could be wrong, but I am just saying.

 

[MisterMeehan]

are you wanting to remove the duplicates?

No, I don't wish to remove the duplicates, but table them separately and count them.

Thanks

 

[Aladin Akyurek]

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

https://dl.dropboxusercontent.com/u/65698317/MisterMeehanCalculate Duplicate Frequency.xlsx

Here is a tutorial on running a pivot table: http://www.contextures.com/CreatePivotTable.html

 

[MisterMeehan]

Thanks. The video completes the picture. Cool.

Unfortunately, I was not able to get that spreadsheet from dropbox for some reason...not sure why? It just returns a blank URL.

Thanks to everyone who contributed!

Cheers

https://dl.dropboxusercontent.com/u/65698317/MisterMeehanCalculate Duplicate Frequency.xlsx

Here is a tutorial on running a pivot table: http://www.contextures.com/CreatePivotTable.html

 

[Aladin Akyurek]

Thanks. The video completes the picture. Cool.

Unfortunately, I was not able to get that spreadsheet from dropbox for some reason...not sure why? It just returns a blank URL.

Thanks to everyone who contributed!

Cheers

You are welcome. I just checked the link; encountered no problem.

 

[kumar lama]

Are you looking for it? Please press Ctrl+Shift+Enter. It's an Array Formula.

Column B =IFERROR(INDEX($A$2:$A$14,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$14),0)),"")

Column C =COUNTIF($A$1:$A$14,D2)

Let me know if it's work for you,

Else you can email me your workbook, sorry i have no access for dropbox.

 

[MisterMeehan]

Thank you for the offer of assistance. I'll see how it goes with the Pivot Table first.

I do have it working on a dummy set of data (20 records), but I am trying to see if it is behaving nicely on 100,000 records - which is a lot harder to be sure.

Cheers

Are you looking for it? Please press Ctrl+Shift+Enter. It's an Array Formula.

Column B =IFERROR(INDEX($A$2:$A$14,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$14),0)),"")

Column C =COUNTIF($A$1:$A$14,D2)

Let me know if it's work for you,

Else you can email me your workbook, sorry i have no access for dropbox.

 

[Aladin Akyurek]

Thank you for the offer of assistance. I'll see how it goes with the Pivot Table first.

I do have it working on a dummy set of data (20 records), but I am trying to see if it is behaving nicely on 100,000 records - which is a lot harder to be sure.

Cheers

That won't work at all for 100,000 records.

 

[MisterMeehan]

You are right. The pivot table approach fell over when we put that many records in. We are now trying another approach, back to formulae.

Even the processing the spreadsheets on a 4GB memory PC slows down to a crawl, typically taking a few hours per run through. Anyway... battle on.

Thanks for your kind follow up.

That won't work at all for 100,000 records.

 

[DRSteele]

Just a thought: if this isn't a live and active list where you'll always have to do this, then filter it: set up the data properly with column headers and the like, then use Filter, Unique Records Only, Extract to Another sheet. Advanced filter may help too.