Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Uniques in Column G Until Change in Column C, then Restart C
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Uniques in Column G Until Change in Column C, then RestartC
On Nov 6, 11:09 am, ryguy7272
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Uniques in Column G Until Change in Column C, then Resta
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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Uniques in Column G Until Change in Column C, then Resta
On Nov 6, 1:16 pm, ryguy7272
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Uniques in Column G Until Change in Column C, then Resta
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 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Uniques in Column G Until Change in Column C, then Resta
On Nov 6, 6:54 pm, ryguy7272
wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restart row count each time the value in a specific column changes | Excel Worksheet Functions | |||
count uniques in same column, post in blank cell, repeat until end ofspreadsheet | Excel Programming | |||
Count Uniques in Column, put result in next blank cell andcontinue until last row | Excel Programming | |||
Count Uniques in Column, put result in next blank cell and continu | Excel Programming | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions |