All Possible Combinations from Single Column

  • Hi everyone,


    I am new to this forum and would greatly appreciate your help.
    My search on the forum was not successful but if a similar post exists, feel free to direct me.


    I need a macro or formula that can produce all possible combinations of 3 from a single column.
    I do not need permutation just combination.
    Please note: my data set below is just for illustration purposes the number of variables my range from 5 up to 15.


    For example:
    DATASET: Results:(All combinations of 3 variables)
    A..............ABC
    B..............ABD
    C..............ACD
    D..............BCD
    E..............BCE (and so on)


    your help is very much appreciated, thank you in advance!

  • Re: All Possible Combinations from Single Column


    Try this for results in "B".

  • Re: All Possible Combinations from Single Column


    Oh my, that is beautiful.
    Thank you so much MickG.


    MickG, i have another question:
    So let us say that instead of all 3 variable combinations; i want two variable or four variable combinations.
    what part of the code do i need to modify to achieve these results.


    Can this be done with minor modifications?


    Thank you so much for your help!

  • Re: All Possible Combinations from Single Column


    Just change the value of "P" at top of code

    Code
    Set rRng = Range("A1", Range("A1").End(xlDown))
        p = 3 'Change value here !!!
        vElements = Application.Index(Application.Transpose(rRng), 1, 0)
  • I hope MickG or someone else can give me a help here: I used the above VBA for one column and it worked Beautifully (I set p=2 as I need the pairs and I set it to show results in 2-columns).


    I have 100s of columns now and for EACH column I need to find all possible combination of pairs. Using MickG's VBA I can do it for 1 column. How can I modify the VBA to generate pairs for e.g. 100 columns?


    Example of a Dataset for 2 columns: The pairs are Generated in Columns C &D


    ColumnA-------- ColumnB -------pairs generated:------Column C --------------ColumnD
    Joe----------------Apple ----------------------------------------Joe------------------- Melissa
    Melissa--------- Orange--------------------------------------- Joe------------------- Jack
    Jack --------------Banana------------------------------------- Joe-------------------- Nancy
    Nancy ------------------------------------------------------------Melissa--------------- Jack
    ---------------------------------------------------------------------Melissa--------------- Nancy
    ---------------------------------------------------------------------Jack------------------- Nancy
    -------------------------------------------------------------------- Apple------------------ Orange
    -------------------------------------------------------------------- Apple------------------ Banana
    ------------------------------------------------------------------- Orange----------------- Banana

    The above VBA works with 1 column (p=2). The example above is for "2" columns (of people & fruits). My data had 100s of columns.


    Any help would be greatly appreciated.

  • Re: All Possible Combinations from Single Column


    Hi Mick,


    I have just used your code for combinations and I love it. Am I right in believing the output should go to col c if it exceeds the capacity of col B. I ask because I get a VBA error 400 when I try to list all combinations of 5 from 45


    Thanks

    Baz1066

  • How can I use these codes to generate combinations of 5 numbers from 45 numbers. I tried it in Excel, but Excel can not handle more than 1,048,576 rows.

  • Hello Kpk64,


    You should start your own thread ...since this one is dated Sept 2020 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!