• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create permutations & combinations

Dear All,

In a array of cells in a column there are five names of places and there is a need to create all sorts of permutations that can happen between these places in regard to origin and destination. Each of the places can be a destination and an origin. How to create such a permutation?

The kind of result sought is attached in the file.

Regards,
Santanu
 

Attachments

  • permutations.xlsx
    8.8 KB · Views: 4
If you have 365 or 2021 how about
=LET(rng,A2:A6,r,ROWS(rng),s,SEQUENCE(r*r,,0),CHOOSE({1,2},INDEX(rng,INT(s/r)+1),INDEX(rng,MOD(s,r)+1)))
 
Hi Fluff13,

Thanks for guiding me. However, could you please apply it in the excel sheet provided in my mail so that I can properly understand how to go about it?

Regards,
Santanu
 
Thanks Fluff13. While checking you formula, I found a suffix as "_xlfn." befor each function that you have used in the formula and the formula is also coming as an array formula within {}...is it a formula to be applied with CSE?

Regards,
Santanu
 
Or, try this old school formula for all Excel versions

In C3, formula copied right to D3 and all copied down until blank :

=IF(ROW($A1)<=COUNTA($A$2:$A$6)^2,INDEX($A$2:$A$6,CHOOSE(COLUMN(A$1),INT((ROW($A1)-1)/5),MOD(ROW($A1)-1,5))+1),"")

77555
 
Back
Top