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.

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:

Sub create_stacks()
Dim rng As Range
Dim ws As Worksheet
Dim lr As Long
Dim stackrange As Variant


Set ws = Sheets("Sheet1")
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
stackrange = Array(5, 7, 9, 11, 11, 9, 7, 5)
    
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("B2:B" & lr) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("A1:B26")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

For Each rng In ws.Range("B2:B" & lr - 1)
    If i = 8 Then i = 0

    nextcol = stackrange(i)
    nextrow = Cells(Rows.Count, nextcol).End(xlUp).Row + 1
    Cells(nextrow, nextcol).Value = rng.Value
    Cells(nextrow, nextcol).Offset(0, -1).Value = rng.Offset(0, -1).Value
    i = i + 1
Next rng

End Sub

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.

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