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---
--
RyGuy
"dbKemp" wrote:
> On Nov 6, 1:16 pm, ryguy7272 <ryguy7...@discussions.microsoft.com>
> wrote:
> > 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---
> >
> > --
> > RyGuy
> >
> > "dbKemp" wrote:
> > > On Nov 6, 11:09 am, ryguy7272 <ryguy7...@discussions.microsoft.com>
> > > wrote:
> > > > 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---
> >
> > > > --
> > > > RyGuy
> >
> > > 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.
>
> 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
>
|
Similar Threads
