How To Create Permutations And Combinations In Excel

Inactive profile

H i Sébastien ,

Thanks for using Microsoft products and posting in the community, I'm glad to offer help.

According to your description, if you want to combine 4 values from 4 different cells into one cell, you can refer to the following article: Combine text from two or more cells into one cell

If I have any misunderstanding of your question, could you please provide more details? It would be helpful if you can share some screenshots of the sample data and the expected result, your understanding and cooperation will be highly appreciated.

Looking forward to your feedback.

Best Regards,

Arck

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Rohn007

Actually the correct term for what you want to do is generate "permutations" of your list.

There is a web page that will do it for you ...

Thanks to the MathiIsFun website, we can get a list of all 1140 combinations.
https://www.mathsisfun.com/combinatorics/combinations-permutations-calculator.html
Then we need Excel wildcards and COUNTIFS to tally how many times each of the 1140 trios was ordered.
.

Give the macro on this page a try;

All Possible Combinations from Single Column (VBA) 2017 01 30
https://www.ozgrid.com/forum/index.php?thread/146983-all-possible-combinations-from-single-column/
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)

This page has a couple of formulas

Excel Permutation Table without VBA 2017 09 00
https://stackoverflow.com/questions/46101847/excel-permutation-table-without-vba/46101848
Is it possible to generate a table of permutations in Excel without using VBA and without using any "helper" sheets or rows/columns?
For N columns, there would be N! rows.
For example, the table for N=3 would look like this:
1 2 3
1 3 2
2 1 3
2 3 1
3 1 2
3 2 1

Open a blank worksheet and paste the following formula into cell A1. (I included line breaks for readability.)

= IF(ROW()<=FACT(COLUMN()-1),COLUMN(),
INDIRECT(ADDRESS(ROW()-FACT(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+1),
IF(COLUMN()=(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+2),1,COLUMN()+1))))

Simply drag this formula over N columns and then down N! rows in order to generate a full permutation table of order N.
.

generate a list of all possible 4 digits combinations in Excel?
https://www.extendoffice.com/documents/excel/3101-list-of-all-possible-4-digits-combinations.html
In some cases, we may need to generate a list of all possible 4 digits combinations of number 0 to 9, which means to generate a list of 0000, 0001, 0002…9999. To quickly solve the list task in Excel, I introduce some tricks for you.
. * A list of all possible 4 digits combinations with formula
. * A list of all possible 4 digits combinations with List All Combinations good idea3
. * List all possible 4 digits combinations with Insert Sequence Number good idea3
.

Generate Combinations from a single list
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_online/i-need-to-index-too-many-words-without-repetition/9f60be58-e2e4-44c4-b13e-16b2a25241d0?messageId=10482caf-5733-491a-88c4-7e825ce79011
e- generate Combinations4.xlsm (downloaded)
I have some words and I need to make sentences whether meaningful or meaningless. Suppose you have 10 words: No, Hi, Please, Do, Not, There, You, And, Well and Why. How can you make the following results in other cells without repetition?
.

List / generate all possible combinations in Excel? (formula)
https://www.extendoffice.com/documents/excel/3097-excel-list-all-possible-combinations.html
Let's say, I have the following two columns of data, and now, I want to generate a list of all possible combinations based on the two lists of values as following screenshot shown. Maybe, you can list all the combinations one by one if there are few values, but, if there are several columns with multiple values needed to be listed the possible combinations, here are some quick tricks may help you to deal with this problem in Excel.
. * List or generate all possible combinations from two lists with formula
. * List or generate all possible combinations from three or more lists with VBA code
.

.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you'd think we'd be able to communicate quite well with people.
Prof. Doug Fisher

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Inactive profile

Hi Sébastien ,

I'm writing this reply to follow up this case, may I know if you have checked my last reply? When you have time, you are welcome to come back to let us know if you still need help here.

Hope you are all well during this period, stay safe and merry Christmas 😊

Best Regards,

Arck

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hello Arck,

Merry Christmas to you too. I have not been able to work on it as much as I would have liked to in the past week.

Rohn007's suggestion using VBA (https://www.ozgrid.com/forum/index.php?thread/146983-all-possible-combinations-from-single-column/) has been really useful. Big thanks to you Rohn.

As I said, I have 5 values (A, B, C, D and E). From this I have to generate 2 sets. The first set will contain 4 values. The second set will contain the 5th value.

Rohn's VBA suggestion works fine for the first set, see below

So if we consider the first line, the macro used gives me the first set : A, B, C, D. So logically I should have E in the second set as it is the remaining value. It would be nice to have a macro for this, while being able to switch to 2 values for the second set if necessary.

Thank you for your help.

Regards,

Sebastien.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.