Count duplicates and triplicates etc

bobkap

bobkap

Active Member
Joined
Nov 22, 2009
Messages
323
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I hope someone can help me with this one. I am stuck and cannot even think of how to solve this. Macro? Pivot table? I just don't know. I have a database with approximately 165,000 records. One field of these records "Student ID" appears more than once in many situations. I would like to have a count of all records where one specific ID only appears just once, a count of all times an ID appears twice, three times, etc. Here's sample of the data. ID #51 appears 5 times so I would want to know how many other IDs appear 5 times, etc.[TABLE="width: 281"]
<tbody>[TR]
[TD="class: xl66, width: 68, bgcolor: transparent"]Student ID
[/TD]
[TD="class: xl67, width: 224, bgcolor: transparent"]CLASS
[/TD]
[TD="class: xl67, width: 83, bgcolor: transparent"]YEAR
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]51
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2006
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]51
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2008
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]51
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2010
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]51
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]51
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]53
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2004
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]53
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2006
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]53
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2008
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]53
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2010
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]53
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2010
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]53
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]54
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]55
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2004
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]55
[/TD]
[TD="class: xl67, bgcolor: transparent"]ABC
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2006
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]56
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]60
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]60
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]64
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]65
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]66
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]68
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2005
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]68
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2008
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]68
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2010
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]69
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2009
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]69
[/TD]
[TD="class: xl67, bgcolor: transparent"]LCZ
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2009
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]71
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]72
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]73
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]77
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2004
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]78
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]80
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2004
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]80
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2006
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]80
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2008
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]80
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2010
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]80
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2010
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]80
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]82
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2005
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]82
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2007
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]82
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2009
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]82
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]83
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]85
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]86
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]87
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]90
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]91
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2003
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]93
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2003
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]93
[/TD]
[TD="class: xl67, bgcolor: transparent"]Course 3
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2005
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
A pivot table will work. insert a pivot table where ever you would like. Set the data source to your range. select Student ID as the Row Label. then also set Student ID as the Value Change the calculation to count. You should get a count for every Student ID that exists. Then you can sort or filter how you would like.
 
Upvote 0
bobkap said:
I hope someone can help me with this one. I am stuck and cannot even think of how to solve this. Macro? Pivot table? I just don't know. I have a database with approximately 165,000 records. One field of these records "Student ID" appears more than once in many situations. I would like to have a count of all records where one specific ID only appears just once, a count of all times an ID appears twice, three times, etc. Here's sample of the data. ID #51 appears 5 times so I would want to know how many other IDs appear 5 times, etc.
Click to expand...
something like this, perhaps
Code: 
Sub howmany()

Dim d As Object, a, c()
Dim i&, lr&, e
Set d = CreateObject("scripting.dictionary")
lr = Cells(Rows.Count, 1).End(3).Row
a = Cells(1).Resize(lr)
For i = 1 To lr
    d(a(i, 1)) = d(a(i, 1)) + 1
Next i
ReDim c(1 To d.Count, 1 To 2)
For Each e In d.keys
    k = k + 1
    c(k, 1) = e
    c(k, 2) = d(e)
Next e
Cells(1, "e").Resize(d.Count, 2) = c

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Calculate annual sales per country
Replies
5
Views
125
AhoyNC
A
H
  • Solved
Grouping rows with the same values into columns "B" and "C"
Replies
5
Views
581
harzer
H
H
  • Question Question
Copy rows from one sheet to another sheet whose name is given by the user.
Replies
4
Views
485
harzer
H
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
368
harzer
H
H
  • Solved
Find a subject that is in column "P" in columns "G" and "L" and export.
Replies
2
Views
252
harzer
H

Forum statistics

Threads
1,223,941
Messages
6,175,537
Members
452,652
Latest member
eduedu

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top