Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dictionary Trim to N smallest items
I have a dictionary and I am trying to trim it down to the N smallest
items. For example, myDict usually contains around 3500 to 5500 items, and I only want the smallest 10 (usually). The code I'm using now is : '// loop, removing largest distance until we're down to the number of points we want Do Until myDict.Count = N maxDist = -1 For Each i In myDict.Keys If myDict.Item(i) maxDist Then maxDist = myDict.Item(i) mx = i End If Next i myDict.Remove mx Loop The problem is that this is super slow; it loops through thousands of times, and this snippet of code is used thousands of times too. Is there a faster way to get the smallest N items? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dictionary Trim to N smallest items
'This loads the 5 smallest numbers from a dictionary object
'back into the dictionary. '-- Sub MakeSmaller() 'Jim Cone - Portland, Oregon USA - Oct 2008 Dim dic As Scripting.Dictionary Dim N As Long Dim M As Long Dim howMany As Long Dim arr As Variant Set dic = New Scripting.Dictionary 'Desired size of dictionary howMany = 5 'Load dictionary with 500 random numbers Randomize For N = 1 To 500 M = Int(555555 * Rnd + 5000) dic.Add N, M Next 'Store all random numbers from dictionary in variant array. arr = dic.Items dic.RemoveAll 'Reload the dictionary with the "howmany" smallest numbers. For N = 0 To howMany - 1 M = Application.Small(arr, N + 1) dic.Add N, M 'Used for testing MsgBox dic.Items(dic.Count - 1) Next Set dic = Nothing End Sub -- Jim Cone Portland, Oregon USA "Nick" wrote in message I have a dictionary and I am trying to trim it down to the N smallest items. For example, myDict usually contains around 3500 to 5500 items, and I only want the smallest 10 (usually). The code I'm using now is : '// loop, removing largest distance until we're down to the number of points we want Do Until myDict.Count = N maxDist = -1 For Each i In myDict.Keys If myDict.Item(i) maxDist Then maxDist = myDict.Item(i) mx = i End If Next i myDict.Remove mx Loop The problem is that this is super slow; it loops through thousands of times, and this snippet of code is used thousands of times too. Is there a faster way to get the smallest N items? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dictionary Trim to N smallest items
On Oct 21, 6:26*pm, "Jim Cone" wrote:
'This loads the 5 smallest numbers from a dictionary object 'back into the dictionary. '-- Sub MakeSmaller() 'Jim Cone - Portland, Oregon USA - Oct 2008 Dim dic As Scripting.Dictionary Dim N As Long Dim M As Long Dim howMany As Long Dim arr As Variant *Set dic = New Scripting.Dictionary 'Desired size of dictionary *howMany = 5 'Load dictionary with 500 random numbers *Randomize *For N = 1 To 500 * * *M = Int(555555 * Rnd + 5000) * * *dic.Add N, M *Next 'Store all random numbers from dictionary in variant array. *arr = dic.Items *dic.RemoveAll 'Reload the dictionary with the "howmany" smallest numbers. *For N = 0 To howMany - 1 * * *M = Application.Small(arr, N + 1) * * *dic.Add N, M * * 'Used for testing * * *MsgBox dic.Items(dic.Count - 1) *Next *Set dic = Nothing End Sub -- Jim Cone Portland, Oregon *USA Thanks Jim, but that doesn't totally solve my problem. For exmaple, say I have a dictionary and I want only the 3 smallest Key Item 1 9 2 8 3 4 4 2 5 12 6 1 7 7 8 7 Your code would result in: Key Item 0 1 1 2 2 4 When what I want is Key Item 6 1 4 2 3 4 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dictionary Trim to N smallest items
Your question was: "Is there a faster way to get the smallest N items?" Since you want both items and keys an approach you could try is... Lay both the items and keys arrays into adjoining blank columns on a worksheet. Sort both columns by the items column. Extract the items and keys from the first N rows. -- Jim Cone Portland, Oregon USA "Nick" wrote in message Thanks Jim, but that doesn't totally solve my problem. For exmaple, say I have a dictionary and I want only the 3 smallest Key Item 1 9 2 8 3 4 4 2 5 12 6 1 7 7 8 7 Your code would result in: Key Item 0 1 1 2 2 4 When what I want is Key Item 6 1 4 2 3 4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Trim vs. No Trim | Excel Programming | |||
Defining Variable Type for Items and Keys in Dictionary | Excel Programming | |||
looping through items in a dictionary | Excel Programming | |||
VBA Trim and Application.worksheetfunction.Trim | Excel Programming | |||
Trim like worksheet Trim | Excel Programming |