Count unique rows based on data in a column

P

Penni

New member
Joined
Mar 13, 2020
Messages
5
Reaction score
0
Points
0
Excel Version(s)
Office 365
Not quite sure how to word this question. I have a column that has numbers that repeat (Site_ID). Each number starts a row that has different data (Species) in another column. What I want to do is create a data set that returns one line for each species by the count of how many unique Site_IDs it matches up with.

Ok. So that's probably not very clear. Please look at what I've loaded and how I would like the new data set to look. It's easy enough to "remove duplicates with a small data set but I have over 14,000 rows of Site IDs couple with species.
 

Attachments

  • Sample data for Xguru Forum.xlsx
    24.7 KB · Views: 16
Why not just use formulae
In G4
=UNIQUE(FILTER(B2:B1000,B2:B1000<>""))
in H4 copied down
=IF(G4="","",COUNT(UNIQUE(FILTER($A$2:$A$1000,$B$2:$B$1000=G4))))
 

Attachments

  • Penni.xlsx
    32.3 KB · Views: 11
And if you do need a macro, how about
Code: 
Sub penni()
   Dim Cl As Range
   Dim Dic As Object
   Dim i As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("sheet1")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Not Dic.exists(Cl.Value) Then Dic.Add Cl.Value, CreateObject("scripting.dictionary")
         Dic(Cl.Value)(Cl.Offset(, -1).Value) = Empty
      Next Cl
      .Range("D4").Resize(Dic.Count).Value = Application.Transpose(Dic.Keys)
      For i = 0 To Dic.Count - 1
         .Range("E" & i + 4).Value = Dic(Dic.Keys()(i)).Count
      Next i
   End With
End Sub
 
You can do this with a pivot table, using species as the row and distinct count of site ID as values, as you'll see in the attached file. It appears, however, that distinct count is only available if you are building a pivot table from the data model. The insert pivot table dialog in my Office 365 Proplus version of Excel has a checkbox to add the data to the data model, not sure if that's the case with other versions.

Norm
 

Attachments

  • Sample data for Xguru Forum.xlsx
    97.9 KB · Views: 4
Awesome. This works great!
 
Not sure which solution you are referring to, but glad we could help & thanks for the feedback
 
You must log in or register to reply here.
Back
Top