PDA

View Full Version : organize data in groups with near equal Totals



pounraj
11-23-2017, 06:19 AM
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
11-24-2017, 07:40 AM
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
11-24-2017, 08:49 AM
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
11-24-2017, 11:17 AM
Ah. A Sudoku puzzle.

pounraj
11-24-2017, 10:54 PM
Hello Sam,
Any excel macro code available to resolve this problem???
Thanks a lot

SamT
11-25-2017, 07:03 AM
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
11-25-2017, 09:44 AM
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
11-25-2017, 10:06 AM
Target Group total(s) is Series Total / Group Count

Your Example Group total target is 16.938.

:dunno" I think you may need to use Solver.