organize data in groups with near equal Totals

[pounraj]

Hello friends,

I need a excel macro to perform the following task..Please advise how this can be done..
User will provide 5 to 50 data values which is a variable ( like 4.30, 5.55, 2.75,, 8.35, 9.27,3.45,10.25,7.27,1.23,1.36,2.31,1.83,3.71,1.12,1.45,2.27,2.36,3.24,0.84 ,1.54,2.23,2.43,3.19,0.77, 1.62 etc ) and user will also give number of groups to be sorted and is also variable ( for instance say 5 )..
Objective is get the number of group of datas ( 5 in this example ) whose sum is as close as possible and all the given data values to be used.. Group may contain any number of data values ( for instance, 3 or 4 or 6 or 1 etc)
Thanks in advance..

[SamT]

Not enough information.

What pattern or Criteria would you apply to group these values from your example?

Values	Differences
0.77	0.07
0.84	0.28
1.12	0.11
1.23	0.13
1.36	0.09
1.45	0.09
1.54	0.08
1.62	0.21
1.83	0.40
2.23	0.04
2.27	0.04
2.31	0.05
2.36	0.07
2.43	0.32
2.75	0.44
3.19	0.05
3.24	0.21
3.45	0.26
3.71	0.59
4.3	1.25
5.55	1.72
7.27	1.08
8.35	0.92
9.27	0.98
10.25

[pounraj]

Hello Sam,

Thank you for the reply..

For example, I want to group them into 5 groups ( it is not a constant, user may specify any number of groups ) and the sum of the 5 groups shall be as close as possible..manually first I will sort data values...then put numbers 1 t0 5 from the bottom ( since 5 groups need to be made)... then sum values with the same number... But as you can see the sum of values are not close

After this, I used to swap the values to bring the sums as close as possible...

please refer below the manual approach

value Groups Sum
1.36 1
1.45 1
2.75 1
3.19 1
10.25 1 19
1.23 2
1.54 2
2.43 2
3.24 2
9.27 2 17.71
1.12 3
1.62 3
2.36 3
3.45 3
8.35 3 16.9
0.84 4
1.83 4
2.31 4
3.71 4
7.27 4 15.96
0.77 5
2.23 5
2.27 5
4.3 5
5.55 5 14.35

[SamT]

Ah. A Sudoku puzzle.

[pounraj]

Hello Sam,
Any excel macro code available to resolve this problem???
Thanks a lot

[SamT]

You have grouped them into groups of the same size, But if you took a number from the group with the largest Total and put in the group with the smallest total the Totals would be closer, but your groups would not be the same size.

Which has the higher importance? Group Size or closer Totals?

[pounraj]

You have grouped them into groups of the same size, But if you took a number from the group with the largest Total and put in the group with the smallest total the Totals would be closer, but your groups would not be the same size.

Which has the higher importance? Group Size or closer Totals?

Hello Sam,

Close Total is important... Group size can be different..

[SamT]

Target Group total(s) is Series Total / Group Count

Your Example Group total target is 16.938.

" I think you may need to use Solver.