Rrandomly selecting a sum of three separate columns in VBA for a set of particular totals

[elsrik]

Hello,

I need to randomly pick individual and repeated entries from an array that has 3 separate integers via VB script, whose sum must equal separate particular values.




I've worked a bit with VBA in Excel before, but this is stumping me.

I have seen examples with how to use Solver for a single row, but have not figured out how to achieve this.


Example:


SOURCE DATA:


name Col A Col B Col C
-----------------------------------
Red 2 3 4
Yellow 2 4 5
Blue 3 1 2
Green 1 1 1
Purple 1 4 1
Orange 0 2 5
Black 4 4 4
White 1 1 1
Brown 3 3 3
Pink 0 1 0
Blaze Orange 0 0 0
Ultraviolet 1 0 0
Crystal 5 1 0




OUTPUT:


Col A Col B Col C
Target Value 10 12 8
-----------------------------------
Green 1 1 1
Purple 1 4 1
Purple 1 4 1
Crystal 5 1 0
Orange 0 2 5
Ultraviolet 1 0 0
Ultraviolet 1 0 0


I'd guess that the results should be random each time, if it's a random search for the answer.

Any tips or assistance would be appreciated.


Thanks,

elsrik

 

[sheetspread]

If repetitions are ok you can just use the randbetween function.