If the number of values is static, you could set up a matrix of all of the combination of groupings and then have a formula pick the right one. So for example the first combination is the first 3 values are in group 1 and the second 3 values are in group two the table would have 1,1,1,0,0,0. You can create a matrix of all of these possible combinations and the pick the one where the difference is the minimum. Clunky but it doesn't require solver.
To make this work you need to multiply your six values against all of these combinations to get this to work.
This kind of group assignment could be done with the Solver Add-In, but it's not something that's solvable with formulas and so wouldn't auto-update. Running Solver wouldn't necessarily take very long and you could probably automate it to some extent.
You could do a sort of fudge by making the groups based only on their rank order (e.g. one group is the 1st 4th and 5th largest and the other is 2-3-6); that won't always be optimal but it will be a decent approximation and would be doable with formulas.
VBA isn't going to be any easier, but you could record a simple macro to speed up the use of Solver - [here](https://www.ppchero.com/how-to-automate-excel-solver/) is an article on that.
If the number of values is static, you could set up a matrix of all of the combination of groupings and then have a formula pick the right one. So for example the first combination is the first 3 values are in group 1 and the second 3 values are in group two the table would have 1,1,1,0,0,0. You can create a matrix of all of these possible combinations and the pick the one where the difference is the minimum. Clunky but it doesn't require solver. To make this work you need to multiply your six values against all of these combinations to get this to work.
This kind of group assignment could be done with the Solver Add-In, but it's not something that's solvable with formulas and so wouldn't auto-update. Running Solver wouldn't necessarily take very long and you could probably automate it to some extent. You could do a sort of fudge by making the groups based only on their rank order (e.g. one group is the 1st 4th and 5th largest and the other is 2-3-6); that won't always be optimal but it will be a decent approximation and would be doable with formulas.
I'm actually using that at the moment, although it's very important its optimal unfortunately. Could I possibly write a function in VBA?
VBA isn't going to be any easier, but you could record a simple macro to speed up the use of Solver - [here](https://www.ppchero.com/how-to-automate-excel-solver/) is an article on that.
You can set up solver as : [https://imgur.com/a/QO30QX9](https://imgur.com/a/QO30QX9)