Count Uniques in Column G Until Change in Column C, then Restart C

[ryguy7272]

I have a list of names in Column C and a list of duplicate and/or unique IDs
in Column G. I'm trying to find a way to count all unique numbers in Column
G, for each person listed in Column C (data is sorter by Column C). Any
ideas?

I posted in the Excel - Functions area earlier, and TM provided this solution.
=SUMPRODUCT((C1:C100<>"")/COUNTIF(G1:G100,G1:G100&""))

I think this will work, but my data set is quite large and Excel runs very
slow when I implement this solution (and I'm only using the function on 1/5
of the data). There must be a better way of doing this, right.

Thanks,
Ryan---

 

[dbKemp]

I have a list of names in Column C and a list of duplicate and/or unique IDs
in Column G. I'm trying to find a way to count all unique numbers in Column
G, for each person listed in Column C (data is sorter by Column C). Any
ideas?

I posted in the Excel - Functions area earlier, and TM provided this solution.
=SUMPRODUCT((C1:C100<>"")/COUNTIF(G1:G100,G1:G100&""))

I think this will work, but my data set is quite large and Excel runs very
slow when I implement this solution (and I'm only using the function on 1/5
of the data). There must be a better way of doing this, right.

Thanks,
Ryan---

I don't have a function.
I use Scripting.Dictionaries in VBA to do things like this. Using a
dictionary is faster that using a collection. I would use one
dictionary to manage the unique items in column C, where the key would
be the name in column C and the values would be another dictionary to
hold all of the ID's in column G (key and value = ID).

If this might be an acceptable approach, and you are not familiar with
dictionaries, post back & I will spend more time to rough something
out.

 

[ryguy7272]

Thanks for the info. dbKemp! I am not very familiar with this type of
solution. I've done VBA work for a few years now. I don't know about this
particular issue though. I guess I am just stumped, so any help would be
great.

Thanks,
Ryan---

 

[dbKemp]

Thanks for the info. dbKemp! I am not very familiar with this type of
solution. I've done VBA work for a few years now. I don't know about this
particular issue though. I guess I am just stumped, so any help would be
great.

Thanks,
Ryan---

Try this:

Private Sub Test()
'Scripting.Dictionaries require reference to MS Scripting Runtime
Dim dicNames As Scripting.Dictionary
Dim dicIDs As Scripting.Dictionary
'Input ranges
Dim rNames As Excel.Range
Dim rIDs As Excel.Range
'Counter
Dim lCtr As Long
'Value in Name column
Dim sName As String
'Value in ID Column
Dim vID As Variant

'These will be different for you
Set rNames = Sheets(1).Range("A1:A8")
Set rIDs = Sheets(1).Range("B1:B8")

'Initialize Name dictionary
Set dicNames = New Scripting.Dictionary
'Loop through cells in ranges (This can be done quicker if
necessary)
For lCtr = 1 To rNames.Rows.Count
'Get name and ID
sName = rNames(lCtr, 1).Value
vID = rIDs(lCtr, 1).Value

'See if name exists in Name dictionary
If dicNames.Exists(sName) Then
'If yes set IDs dictionary = to it's value
Set dicIDs = dicNames(sName)
Else
'If not, create a new dictionary
Set dicIDs = New Scripting.Dictionary
End If
'Add ID to IDs dictionary
'Doing it like this instead of using .Add will eliminate Dupe
IDs for same name
dicIDs(vID) = vID
'Store dicIDs in dicNames
Set dicNames(sName) = dicIDs
Next

'Get count of ID's for each Name
For lCtr = 0 To dicNames.Count - 1
Set dicIDs = dicNames.Items(lCtr)
'This will be different for you
MsgBox "Name: " & dicNames.Keys(lCtr) & " , Count: " &
dicIDs.Count
Next
End Sub

 

[ryguy7272]

Thanks for the effort dbKemp!! Unfortunately, the code didn't do anything.
All I did was add a reference to MS Scripting Runtime, and I modified these
lines of code:
'These will be different for you
Set rNames = Sheets(1).Range("C2:C4500")
Set rIDs = Sheets(1).Range("G2:G4500")

What was supposed to happen?

Thanks,
Ryan---

 

[dbKemp]

Thanks for the effort dbKemp!! Unfortunately, the code didn't do anything.
All I did was add a reference to MS Scripting Runtime, and I modified these
lines of code:
'These will be different for you
Set rNames = Sheets(1).Range("C2:C4500")
Set rIDs = Sheets(1).Range("G2:G4500")

What was supposed to happen?

Thanks,
Ryan---

If your data is on 'Sheets(1)'....it will put up a message box for
each unique name with the count of ID's. If you data is not on
'Sheets(1)' then it will do nothing. Make sure the sheet reference is
correct and then step through the code line by line checking the
values of the variables to see that it is reading the correct values
from the worksheet.