kitsu_ne
New member
- Joined
- Jan 15, 2014
- Messages
- 16
- Reaction score
- 0
- Points
- 0
I'm trying to create a function that I can use to return a top total in a range (that is specified). There are more (somewhat complicated) steps that I will have to work through after this, but for now, if I can get passed this part I think I should be good to go.
Here's what I'm imagining:
I have a set of data like the following:
So the idea is to create an array of unique values in the months column, as well as a summation of the corresponding hours.
Function Driver(sum As Range, driverRng As Range, val As Integer, Optional title As Boolean)
so, "sum" is the column that I want to total, "driverRng" is the months column that I want condensed,
"val" is which value I want to return (1 being the higest value, 10 being the tenth's hightest and so on),
and lastly "title" if specified as True would return the actual driver, and not the summation. So for instance True in the example above would return "Mar" while False would return "18"
Here's what I've put together.
Sadly, the numbers are not even totalled correctlt.
Also, I still need to figure out how to sort the values so that it returns the top number, which I have no idea how to do at the moment.
Currently, this returns the following:
If anyone has any suggestions please let me know,
Thanks.
~Kitsu
Here's what I'm imagining:
I have a set of data like the following:
Month | Hours |
Jan | 1 |
Feb | 2 |
Mar | 3 |
Jan | 4 |
Feb | 5 |
Mar | 6 |
Jan | 7 |
Feb | 8 |
Mar | 9 |
So the idea is to create an array of unique values in the months column, as well as a summation of the corresponding hours.
Function Driver(sum As Range, driverRng As Range, val As Integer, Optional title As Boolean)
so, "sum" is the column that I want to total, "driverRng" is the months column that I want condensed,
"val" is which value I want to return (1 being the higest value, 10 being the tenth's hightest and so on),
and lastly "title" if specified as True would return the actual driver, and not the summation. So for instance True in the example above would return "Mar" while False would return "18"
Here's what I've put together.
Sadly, the numbers are not even totalled correctlt.
Also, I still need to figure out how to sort the values so that it returns the top number, which I have no idea how to do at the moment.
Code:
Function Driver(sum As Range, driverRng As Range, val As Integer, Optional title As Boolean)
Dim drvList As New Collection ' This will contain a list of all the values in driverRange, but will not duplicate.
Dim ttlList As New Collection ' This will hold the final list of data.
Dim drvSum As Double ' This will allow us to keep track of the summation for each item in drvList.
Dim ret As Variant ' This will be the end value to return.
Dim i As Long ' i will keep track of the instance we are working with in loops.
Dim d As Double ' d will hold a temp total of each driver in a loop.
On Error Resume Next
For Each a In driverRng
drvList.Add a, a ' arr.Add "value", "key" - Since we can only have 1 key of each item, duplicates are not added to the table.
Next
For Each itm In drvList ' For each unique driver that was found...
i = 0 ' Set the count, and the total to 0.
d = 0
For Each cell In driverRng ' Then loop through all the cells in the driver's range...
If itm = cell Then ' If the cell is the same as the item we're working on...
d = d + sum(i) ' grab the corresponding number value and add it to the total for the item.
End If
i = i + 1 ' Increase the instance count as we cycle through the cells so we know what number to add.
Next cell
ttlList.Add d, itm ' Once all cells have been cycled add the total to the total's collection, and then move on to the next item.
Next itm
If title = True Then Driver = drvList(val) ' Once all drivers have been cycled through we check what value is wanted and return the driver title if True...
If title = False Then Driver = ttlList(val) ' Or the total if False, or not specified.
End Function
Currently, this returns the following:
number | FALSE | TRUE | Should be: |
1 | 7 | Feb | 15 |
2 | 13 | Jan | 12 |
3 | 16 | Mar | 18 |
If anyone has any suggestions please let me know,
Thanks.
~Kitsu
Last edited: