Creating a circular combination pattern from a list of names using Excel formulas

Question

I have tried using various formulas, but I have been unable to achieve the desired output. Which is all possible permutations of given data.

Could someone please provide me with a formula or approach that can help me generate this output pattern in MS Excel?

Input:

Ram
Sham
Raj
Rahul
Ravi

Output:

Ram     Sham    Raj     Rahul   Ravi
Ram     Raj     Rahul   Ravi    Sham
Ram     Rahul   Ravi    Sham    Raj
Ram     Ravi    Sham    Raj     Rahul

Sham    Ram     Raj     Rahul   Ravi
Sham    Raj     Rahul   Ravi    Ram
Sham    Rahul   Ravi    Ram     Raj
Sham    Ravi    Ram     Raj     Rahul

Raj     Ram     Sham    Rahul   Ravi
Raj     Sham    Rahul   Ravi    Ram
Raj     Rahul   Ravi    Ram     Sham
Raj     Ravi    Ram     Sham    Rahul

Rahul   Ram     Sham    Raj     Ravi
Rahul   Sham    Raj     Ravi    Ram
Rahul   Raj     Ravi    Ram     Sham
Rahul   Ravi    Ram     Sham    Raj

Ravi    Ram     Sham    Raj     Rahul
Ravi    Sham    Raj     Rahul   Ram
Ravi    Raj     Rahul   Ram     Sham
Ravi    Rahul   Ram     Sham    Raj

I tried few permutation generator formulas but these not giving expected results:

=INDEX($A$1:$A$5,MOD((ROW()-1)*5+COLUMN()-2,5)+1)

In addition to not giving us the code of your own effort, you do not explain to us the logic that you placed the values. — ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ, Jun 24 '23 at 15:26
Row 4 for Ram seems to have Ravi twice, is that correct or a mistake? — Solar Mike, Jun 24 '23 at 15:55
So why is Raj not used on that row 4, similar issue on row 12. But why does this not happen for rows 8, 16 and 20? — Solar Mike, Jun 24 '23 at 16:35
@SolarMike Thanks! you was right, I have corrected the mistakes — Rakesh Kumar, Jun 24 '23 at 16:48

P.b · Accepted Answer · 2023-06-24T17:24:55.510

2

=LET(z, TOROW(A1:A5),
     REDUCE(TOCOL(z),DROP(z,,1),
     LAMBDA(x,       y,
TOCOL(IFS(ISERR(FIND(z,x)),x&z),3))))

It stacks all values of range z after eachother and returns them if they weren't found yet.

Updated to spill the results to the right as well:

=LET(z,TOROW(A1:A5), 
     DROP(REDUCE(0,REDUCE(
                          TOCOL(z),                       
                          DROP(z,,1),
                       
                   LAMBDA(x,y,
                          TOCOL(IFS(ISERR(FIND(z,x)),x&";"&z),3))),
          LAMBDA(v,w,
                 VSTACK(v,TEXTSPLIT(w,";")))),1))

edited Jun 24 '23 at 17:24

answered Jun 24 '23 at 16:07

P.b

8,293
2
10
25

I tried this formula, but everything is coming in single column and also there is no space in between.(i.e. `RamShamRajRahulRavi`) – Rakesh Kumar Jun 24 '23 at 16:32
My bad, see edit, but no space between the different starting values. – P.b Jun 24 '23 at 17:25
1

Thanks! now it is giving expected result – Rakesh Kumar Jun 24 '23 at 18:18

Tom Sharpe · Answer 2 · 2023-07-03T14:28:08.313

I have started looking at some methods for generating permutations in the literature and investigating if they can be implemented without recourse to VBA.

The Lexicographic Ordering method described here can be implemented moderately easily, but is slow.

However the 'bellringers' method described here performs better. The recursion can be simulated by using alternate hstack and vstack. While the article describes inserting a number one greater than the largest number in the current permutation into each position in the current permutations like this:

Existing permutations

1 2
2 1

New permutations

It's easier if you change the order like this:

So you hstack the first pair of threes onto the existing pair of permutations, then vstack these same two resulting permutations after swapping their first two elements and again after swapping the second and third elements of the previous pair.

The actual formula for a list of strings is:

=LET(sums,{1,3,6,10,15,21,28,36,45},factorials,{1,2,6,24,120,720,5040,40320,362880},n,8,
REDUCE(B1,SEQUENCE(INDEX(sums,n)-1),LAMBDA(a,c,
LET(cStart,XLOOKUP(c,sums,sums,,-1),
group,XMATCH(c,sums,-1),
groupSize,XLOOKUP(c,sums,factorials,,-1),
IF(c=cStart,HSTACK(MAKEARRAY(groupSize,1,LAMBDA(r,c,INDEX(B:B,group+1))),a),
VSTACK(a,hSwap(TAKE(a,-groupSize),c-cStart,c-cStart+1)))))))

hSwap(Array,Pos_1,Pos_2) is just a wrapper for Choosecols:

=LET(
    cols, COLUMNS(Array),
    seq, SEQUENCE(cols),
    CHOOSECOLS(Array, IFS(seq = Pos_1, Pos_2, seq = Pos_2, Pos_1, TRUE, seq))
)

score 1 · Answer 3 · answered Jun 24 '23 at 16:12

1

Well, works but not as pretty as the other answer:

But will work with earlier versions of Excel...

answered Jun 24 '23 at 16:12

Solar Mike

7,156
4
17
32

Creating a circular combination pattern from a list of names using Excel formulas

3 Answers3

Linked