How to create permutations & combinations

[Santanu Chatterjee]

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

 

[Fluff13]

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)))

 

[Santanu Chatterjee]

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

 

[Fluff13]

What version of Excel are you using?

 

[Santanu Chatterjee]

I use office 365

 

[Fluff13]

Ok, here you go

 

[Santanu Chatterjee]

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

 

[Fluff13]

If you are getting the _xlfn prefix, then you are not using xl365. What version are you using?

 

[Santanu Chatterjee]

 

[bosco_yip]

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),"")

 

[Santanu Chatterjee]

OK...let me check.

Thanks again.

 

[Fluff13]

That shows you are using 2019.
Try bosco_yip's formula

 

[Santanu Chatterjee]

Thanks, bosco_yip's formula is working great.

Thank you guys for helping me out.

Regards,
Santanu