I have a dictionary where I am creating keys that represent multiple keys. I create them with a hashtag. They keys are time based data. Here is an example with 2 years of data over 2 months and three days.
Is it possible to do sumif’s and averageifs on the dictionary for the years or months? I assume I would have to parsing the exciting key/hashtag while looping? But I also assume I would have replace the existing keys with a temporary key (i.e. replace “2020|January|01” with “2000“ or “January”) and then perform the sumif/averageif. But it is not clear to me how you would do this as parsing the key for sumifs and averageifs would create duplicate keys (i.e. I would have multiple keys for January in a given year).
Or should I be simply using a Collection?
Any idea on how to do this?
Sub Test()
Dim NewDict as New Scripting Dictionary
Set NewDict to New Scripting Dictionary
NewDict.Add “2020|January|01”, 350
NewDict.Add “2020|January|02”, 165
NewDict.Add “2020|January|03”, 134
NewDict.Add “2020|February|01”, 350
NewDict.Add “2020|February|02”, 165
NewDict.Add “2020|February|03”, 134
End Sub
Thanks
Is it possible to do sumif’s and averageifs on the dictionary for the years or months? I assume I would have to parsing the exciting key/hashtag while looping? But I also assume I would have replace the existing keys with a temporary key (i.e. replace “2020|January|01” with “2000“ or “January”) and then perform the sumif/averageif. But it is not clear to me how you would do this as parsing the key for sumifs and averageifs would create duplicate keys (i.e. I would have multiple keys for January in a given year).
Or should I be simply using a Collection?
Any idea on how to do this?
Sub Test()
Dim NewDict as New Scripting Dictionary
Set NewDict to New Scripting Dictionary
NewDict.Add “2020|January|01”, 350
NewDict.Add “2020|January|02”, 165
NewDict.Add “2020|January|03”, 134
NewDict.Add “2020|February|01”, 350
NewDict.Add “2020|February|02”, 165
NewDict.Add “2020|February|03”, 134
End Sub
Thanks