+ Reply to Thread
Results 1 to 14 of 14

How to divide a set of numbers into 4 equal sum groups

  1. #1
    Registered User
    Join Date
    04-17-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    6

    How to divide a set of numbers into 4 equal sum groups

    Hi, I would like to develope a VBA macro code to split pool of numbers into 4 groups of equal / minimum different of sum.
    We could exclude or left out one or two number that cannot fit.

    Example of my attachment: I have 25 numbers need to be splitted into 4 groups of equal sum.

    Can anyone please help to provide me the VBA code?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,503

    Re: How to divide a set of numbers into 4 equal sum groups

    I would venture to suggest that much of this question is more about the algorithm than about the code. Do you have a preferred algorithm for this problem?

    From a quick look around the internet, I see a few different algorithms proposed.

    1) A Monte Carlo type algorithm. Put together random groupings, check the sum, then randomly swap values from one group to another until you find a suitable solution. With the right thinking, you can probably make this more of an evolutionary algorithm so that it tends towards a better solution with each iteration rather than being truly random.

    2) Something like this one looks promising: http://answers.microsoft.com/en-us/o...6-b1b529cc6bcc I tried it using Excel functions and it seems fairly straightforward. Sort the data from high to low or low to high. In an adjacent column, put 1,2,3,4,1,2,3,4,..... Then, the formula for each stack becomes a simple =sumif() function. Adjust the criteria for the sumif() until the sums are suitably close together.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: How to divide a set of numbers into 4 equal sum groups

    hi ahchin5 and welcome to the forum.

    as you've no doubt discovered this is a rather complicated request. i looked around for a while but didn't find anything i considered perfect so i wrote some code which is also not perfect, but it's almost the weekend and it doesn't look like you're getting a better answer for now:

    Please Login or Register  to view this content.
    basically this just sorts the values largest to smallest and then drops them one at a time into each of your four buckets (excluding the smallest value at the end...) - the end result is not bad. i had some thoughts around identifying how far from the average each of the buckets is and trying to trade some values around to optimize them and / or looping through each individual value to see if moving it to another bucket would reduce the overall variance, but i didn't actually make any headway in the vba. Even with the additional effort to tweak the thing i don't think i can guarantee an optimal distribution though...

    anyway, after all that, i thought perhaps this code would get you close enough to provide some value - from here you can fairly easily do a spot check and identify additional optimization opportunities so at least you're not just starting from scratch. Hope it helps.
    Hope I could help - if your post has been solved don't forget to mark it as such.

    Clicking the * below is a great way to say thanks!

    "Drowning in 10 feet of water isn’t any different than drowning in a million feet. And if you can swim, it doesn’t matter how deep the ocean is. At some level, once you realize you’re in water that’s too deep to stand, you have to have a very different approach," - Joi Ito

  4. #4
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,132

    Re: How to divide a set of numbers into 4 equal sum groups

    It is also posible to use solver and I've setup a model for this. As you have 25 items that you wish to split i four groups I've specified one group with 7 items and the other 3 with 6 items each.

    To test clear range D2:G26 and run solver.

    For setting up solver and how it works see link to "Chandoo.org"

    http://chandoo.org/wp/2011/05/11/usi...o-assign-item/

    Alf
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-17-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    6

    Re: How to divide a set of numbers into 4 equal sum groups

    Hi Alf,
    Thank you very much. In fact I tried solver but the solution seems not suit to my need. It do not match and group into 4 with minimal difference in sum (which is causing waste of my material in production). That is why I need to split our "reserved wafer" for future use if any of it cause big difference in the sum of 4 groups.

  6. #6
    Registered User
    Join Date
    04-17-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    6

    Re: How to divide a set of numbers into 4 equal sum groups

    Hi simarui,

    Thank you very much for your great help! This seems close to my need but as you mentioned it is still not optimize to split with smallest variance.
    In fact I need this for my production to split my product builds correctly to eliminate waste.
    If you have any better idea on how to optimize it later on, please kindly help me.. thanks a lot

  7. #7
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,132

    Re: How to divide a set of numbers into 4 equal sum groups

    Just curious but was this you wanted?
    Attached Images Attached Images

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,889

    Re: How to divide a set of numbers into 4 equal sum groups

    I got this close using Evolutionary Solver, changing col C to minimize max - min:

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Wafer
    Qty
    Stack
    1
    2
    3
    4
    2
    0
    0
    0
    0
    3
    wf01
    522
    2
    0
    522
    0
    0
    D3: =D2 + (ROUND($C3, 0)=D$1)*$B3
    4
    wf02
    526
    4
    0
    522
    0
    526
    5
    wf03
    542
    2
    0
    1064
    0
    526
    6
    wf04
    530
    3
    0
    1064
    530
    526
    7
    wf05
    579
    1
    579
    1064
    530
    526
    8
    wf06
    529
    4
    579
    1064
    530
    1055
    9
    wf07
    483
    3
    579
    1064
    1013
    1055
    10
    wf08
    477
    3
    579
    1064
    1490
    1055
    11
    wf09
    578
    2
    579
    1642
    1490
    1055
    12
    wf10
    558
    1
    1137
    1642
    1490
    1055
    13
    wf11
    522
    3
    1137
    1642
    2012
    1055
    14
    wf12
    430
    3
    1137
    1642
    2442
    1055
    15
    wf13
    571
    4
    1137
    1642
    2442
    1626
    16
    wf14
    554
    2
    1137
    2196
    2442
    1626
    17
    wf15
    504
    2
    1137
    2700
    2442
    1626
    18
    wf16
    528
    4
    1137
    2700
    2442
    2154
    19
    wf17
    579
    2
    1137
    3279
    2442
    2154
    20
    wf18
    560
    1
    1697
    3279
    2442
    2154
    21
    wf19
    478
    3
    1697
    3279
    2920
    2154
    22
    wf20
    572
    4
    1697
    3279
    2920
    2726
    23
    wf21
    522
    4
    1697
    3279
    2920
    3248
    24
    wf22
    499
    3
    1697
    3279
    3419
    3248
    25
    wf23
    565
    1
    2262
    3279
    3419
    3248
    26
    wf24
    531
    1
    2793
    3279
    3419
    3248
    max-min
    27
    wf25
    557
    1
    3350
    3279
    3419
    3248
    171
    Entia non sunt multiplicanda sine necessitate

  9. #9
    Registered User
    Join Date
    04-17-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    6

    Re: How to divide a set of numbers into 4 equal sum groups

    Hi Alf, yes this is exactly what i wanted.

  10. #10
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,132

    Re: How to divide a set of numbers into 4 equal sum groups

    Your problem in Solver terms is a “Simplex LP” problem but the solver model that comes with Excel is not powerful enough to solve this.

    To do so you need to download the “OpenSolver” for Excel. You can either build your model in “OpenSolver” or using the Excel solver (I prefer the latter as I’m more used to this).

    The setup is quite simple but depends a bit on your Excel version. Forum profile says Excel 2007 but your uploaded file is xls i.e. Excel 2003 or earlier.

    The image shows the solver model for Excel 2007. In the upper right corner the icons for “OpenSolver” can be seen. The solver settings under “Options” are “Tolerance: 1%”, “Assume Linear Model” and “Assume Non-Negative” boxes ticked.

    Alf
    Attached Images Attached Images

  11. #11
    Registered User
    Join Date
    04-17-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    6

    Re: How to divide a set of numbers into 4 equal sum groups

    hi Alf, could you please send the Excel solver that you did as screenshot (with the Reserved) to me?
    I tried to put in the criteria as the picture but came out error

  12. #12
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,132

    Re: How to divide a set of numbers into 4 equal sum groups

    could you please send the Excel solver that you did as screenshot
    Sure no problem but rememder you need to download the "OpenSolver" and extract the files. Then open this file and find the folder where the "OpenSolver" is stored normally "C:\Opensolver". In this folder you will find the file "OpenSolver.xlam". Doubble click on this file and then you will have "Opensolver" on the ribbon of the opened Excel file. You have acccess to "OpenSolver" as long as you have an active Excel file. So the next time you starts Excel you will have to activate "OpenSolver" as well if you wish to use it.

    Assuming you are in the "Data" tab you can now clear range "D2:H26" and click on icon marked "solve" this will start the run of the "OpenSolver"

    You download the "OpenSolver" at this link.
    http://opensolver.org/installing-opensolver/

    Alf
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    04-17-2015
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    6

    Re: How to divide a set of numbers into 4 equal sum groups

    thank you very much!

  14. #14
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,132

    Re: How to divide a set of numbers into 4 equal sum groups

    You are welcome and I hope this works for you.

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1