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)

• 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)``````
• Re: All Possible Combinations from Single Column

Thank a bunch, thank you so much for your expertise!

• Re: All Possible Combinations from Single Column

You're welcome

• 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!