Combination of data with criteria

  • Hi Guys,



    I've had some Excel courses and programmed some vba stuff before, but I got this new project, and I never dealt with something with such complexity. It's for a farm to select the optimal treatment (combinatioon of molecules) considering input information from user input and the hardcoded data stored on different sheets.

    All of the sheets are organized based on the first column, which is a list of individual molecules in all different sheets (some molecules are present in one sheet but not the other). On some of the sheets the other columns represent data for each individual molecule, and on 2 of them the other columns represent the same molecule (so the sheet would give info regarding interaction of 2 different molecules). One example of each:


    molecules
    < 60 days?
    EFFICIENCY FOR A
    EFFICIENCY FOR Bprice/kg
    molecule 1
    yes0.80.55$12
    molecule 2
    no0.450.9$22


    And:

    molecule adversity
    molecule 1molecule 2molecule 3
    molecule 1
    yesnoyes
    molecule 2noyesno
    molecule 3yesnoyes


    What I have now is my code is creating a collection of different molecules, and excluding from it those that don't fit certain criteria.

    Now that this collection is ok, I need to work on specific combinations of maximum 4 molecules, without repeating none (order doesnt matter).

    So If my collection was (a,b,c,d), now it must include also (ab,ac,ad,bc, bd, cd), (abc, abd, acd,bcd) (abcd). (I have much more than 4 initial elements of course)

    Problem is some molecules can't go together because of criteria on some sheets like the adversity example I have shown.


    I can't wrap my mind around this logical problem: how should I structure this?

    I don't know if collections are ideal.

    Can anyone shed any light on this, please? Any consideration will be appreciated.


    Thanks a lot,

    Pedro

  • Hi GV,


    This was the best permutation without repetition code I could find:

    https://stackoverflow.com/ques…on-without-repetition-vba


    Modified to:


    Solves the first part of your problem.


    I do not fully understand the code yet, so would not currently be able to build in the adversity check... I will have another look later and post if I solve it.


    Justin