mercredi 28 mars 2018

Excel VBA : Finding random combinations across 3 columns that match preset sums

I need to randomly pick individual and repeated entries from a list that has 3 separate integers, whose sum must equal separate particular values.

I've worked a bit with VBA 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 get it to process stage by stage.

DATA: (stored on an invisible tab)

    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

I'm trying to find Random combination of items that whose sums equal the respective target result values for each column. (selected by preset dropdowns)

Say for example, I'd want Column A to equal 10, Column B to equal 12, and Column C to equal 8.

The output might look like this.

    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
    -------------------------
    SUM          10   12    8

I'd guess that the results should be random each time, if it's a random search for the answer. The solver would likely work for this, but it'd have to be a nest of conditionals.

Something like:

    :START
    Calc Column A. If random sampled solution across Column A = desiredA, 
    then Record names of A, then Calculate Col B. 
    If Col B = desiredB, then Record names of B, then Calculate Column C. 
    If Col C = desired C, then Record names of C, Print 
    Value1:Value2:Value3. 
    If no solution found, goto START.

Any tips would be appreciated, thank you. Apologies for the code block abuse. It makes the formatting more consistent.




Aucun commentaire:

Enregistrer un commentaire