Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Highest total function - a work in progress

  1. #1
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0

    Highest total function - a work in progress



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    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 by kitsu_ne; 2014-04-10 at 10:05 AM. Reason: formatting issues

  2. #2
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    Location
    Italy
    Posts
    59
    Articles
    0
    let save time, attach please a sample file

  3. #3
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0

    File Attached

    Alright, I've attached the file, and I added another example just for the heck of it. I haven't made any changes to the function yet.

    Thanks to anyone who takes a look.
    Attached Files Attached Files

  4. #4
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0
    It won't let me edit my previous message, but I wanted to add that I noticed that I accidentally created two separate veriables to track the summation totals. This doesn't have anything to do with why the function is not working properly but I wanted to mention it.

    Ignore this line entirely:
    Code:
        Dim drvSum As Double                ' This will allow us to keep track of the summation for each item in drvList.

  5. #5
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0
    I actually figured out what was causing the numbers to not match up = since I was setting i to 0, thinking that the collections started at 0, it was not adding the propper numbers together. When I set i to 1 all the numbers matched up perfectly.

    But now the problem that I have now is, how can I sort both ttlList and drvList so that the values match up? I need to be able to sort these values by highest to lowest total.

    I've attached the updated file.
    Attached Files Attached Files

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    You can get the same results you're getting very quickly indeed without a udf by using a pivot table. See attached which gives you summarised and sorted data. You can then get the top value, either by looking at the topmost value or you can dispense with sorting and only display the highest value(s) (I did this in the 2 rightmost pivots in the attached).
    If for some reason this isn't going to be good enough, I'll add a sorting routine to your function.

    ps.
    You're adding ranges to the drvList collection; it might be less resource hungry just to add values.

    If you have the vb.Net framework files installed you may be able to use either a Systems.Collection.ArrayList which has built-in sorting or a Systems.Collection.SortedList.

    Consider also converting the result of your function to a complete 2D array whci you could then array-enter into a range on your sheet. This would also be less resource hungry as the function would only be executed once to get all the results in the cells, instead of once per cell that the function is in.

    I'll wait for you to come back before I do any coding/tweaking.
    Attached Files Attached Files

  7. #7
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0

    Updated

    The function that I am trying to build is a little more complicated that I had previously mentioned. I updated my spreadsheet to include more of what I'm trying to include, as well as a modified snippet of the data that it would be iterating through. I updated the function as well, and I hope it's easy to understand.

    I had been using pivot tables in the past, but as you can see in the attachement, I need to pull data by a date range. In my main spreadsheet I have visual basic modify the contents of the 10 pivot tables by going through every instance of the date and making hidden the dates that do not fit the range, or making visible the ones that do. Because there can be a lot of records by the end of the month, half way through the month (I need a monthly range and a weekly range, which is why there are 10 tables) it takes the file quite some time to go through each table and update them. Because of this, and because the function would be used throughout the file, I'm trying to get away from using pivot tables.

    What you describe sounds promising, but I've never looked into that before. I only just found out about creating collections while I was trying to figure this out.

    Thanks for your time,
    Kitsu
    Attached Files Attached Files

  8. #8
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0
    I am all for speeding the function up and using less resources! That's why I'm trying to do this in the first place! I looked to see if I have the vb.Net framework using the steps described here: http://msdn.microsoft.com/en-us/libr...v=vs.110).aspx
    The "FULL" folder exists but there is no Release value, so I guess that means I do not have 4.5 or newer? My folder looks exactly like the snapshot there, just without the Release value/file.

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    OK, I'm aware of time passing, so here is a possible solution that might be better than what you have but it is very much WORK IN PROGRESS.
    It uses a dictionary object to get unique drivers and sum their hours at the same time. This is then converted into a 2-D array and sorted.
    Dictionaries are not the fastest tool in the box, and the bubble sort I used is one of the slower sorting algorithms.

    I'll continue to explore:
    built-in collections (probems with outputting the keys)
    Systems.Collection.ArrayList
    Systems.Collection.SortedList
    Attached Files Attached Files

  10. #10
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0
    Very cool, I like how you can do that! I don't entirely understand what's going on though.

    I tried to recreate your while loop. I had written a similar "for" loop, but this is my first attempt at using a while loop. It looks like it works, but for some reason it seems to skip "0" values for some reason... Did I do something wrong?

    Also, one thing that I do want, however, is to be able to specify the number of the driver that I want because sometimes I'll only want one of them, but it won't necesarrily be the top most value.

    I'm still interested in what you are/were working on. I've read a little about dictionaries a year ago but I didn't undertand as much as I do now, so it will be helpful to see what you came up with.


    I really apreciate you taking a look, p45cal, thank you!
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •