Generate A Permutation List no Macros! if possible- Excel

[faditaouk]

I need a set of formulas to list all 720 permutations of the numbers 1,2,3,4,5,6, if it can't be done by a formula then it has to be in macro. May anyone help!

 

[faditaouk]

hi shado,

Thank you so much for replying. I must admit im inexperience with macro. Is it possible in just a few sentences or point form in the steps to create a macro or VBA, It will be mostly appropriated,

Regards

faditaouk

 

[S.H.A.D.O.]

Hi faditaouk,

Just follow these steps...

(1) Open the workbook.
(2) Press Alt-F11 to open the VBA editor.
(3) From the menu, select Insert > Module.
(4) On the page that opens, copy and paste the code.
(5) Press Alt-Q to close the VBA editor.
(6) Press Alt-F8 to open the macro selector.
(7) Select Permutations_6_From_6 and click Run.

I hope this helps!

 

[faditaouk]

Hi S.H.A.D.O,

Thank you so much you are an absolute legend, The steps and the formula worked out perfectly.
Hopefully, if I need anymore of your expertise you will be willing.

Regards
Faditaouk

 

[S.H.A.D.O.]

You're more than welcome faditaouk, thanks for the feedback.
There will always be somebody here willing to help.
Have a great weekend.

 

[cadafi]

If you want a formula-based solution, then enter this formula into a cell of your choice:

=MODE.MULT(IF(MMULT(0+ISNUMBER(FIND({1,2,3,4,5,6},ROW(INDEX($A:$A,123456):INDEX($A:$A,654321)))),{1;1;1;1;1;1})={6,6},ROW(INDEX($A:$A,123456):INDEX($A:$A,654321))))

Follow the concept of XOR LX, nowaday if you are using Excel 365, you can also use Let() formula

A1 you can type any value from 1 to 9
in B1, you can type:
=LET(myResult,MODE.MULT(IF(MMULT(0+ISNUMBER(FIND(SEQUENCE(1,$A$1),ROW(INDEX($A:$A,--CONCAT(SEQUENCE(,$A$1))):INDEX($A:$A,--CONCAT(SEQUENCE(,$A$1,$A$1,-1)))))),RANDARRAY($A$1,1,1,1))=RANDARRAY(1,2,$A$1,$A$1),ROW(INDEX($A:$A,--CONCAT(SEQUENCE(,$A$1))):INDEX($A:$A,--CONCAT(SEQUENCE(,$A$1,$A$1,-1)))))),MID(myResult,SEQUENCE(1,$A$1),1))