3-pair Permutation / Combinations from a single list of seven (7) inputs

[fastwriter]

Hey gang!

Hope you are all well!

So I've been scouring the 'net for this answer and can't quite find the *exact* formula for this. Hoping I can solve this in Excel w/out VBA. The simpler, the better.

I am trying to generate a permutation list of 2-char pairs and generate a non-repeating list of three (3) combinations from the following:

- Sa
- Ur
- No
- Ma
- Ha
- Ju
- Mo

From the above list, I'd like to get a combination with only 3 of the above at time, with no repeats.

Example:
- Sa Ur No
- Sa Ur Ma
- Sa Ur Ha
- Sa Ur Ju
- Sa Ur Mo


-- Is there a simple way to do this in Excel?

Looking forward to your responses!

 

[fastwriter]

I found what I'm looking for, but the video doesn't explain HOW
URL:

 

[Fluff]

Hi & welcome to MrExcel.

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[lrobbo314]

A way using VBA.

BookII.xlsm
	A	B	C	D	E
1	Sa		Mo	Sa	Ur
2	Ur		Mo	Sa	No
3	No		Mo	Sa	Ha
4	Ma		Mo	Sa	Ju
5	Ha		Mo	Sa	Ma
6	Ju
7	Mo
Sheet13

Sub Main()
Dim r As Range:         Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = PX(r)

Set r = Range("C1")
r.Resize(UBound(AR), UBound(AR, 2)).Value2 = AR
End Sub

Function PX(r As Range)
Dim AR() As Variant:        AR = r.Value2
Dim RES() As Variant:       ReDim RES(1 To UBound(AR) - 2, 1 To 3)

MIX AR

For i = 3 To UBound(AR)
    RES(i - 2, 1) = AR(1, 1)
    RES(i - 2, 2) = AR(2, 1)
    RES(i - 2, 3) = AR(i, 1)
Next i

PX = RES
End Function

Function MIX(AR As Variant)
Dim UB As Integer:  UB = UBound(AR)
Dim RI As Integer

For i = 1 To UB
    RI = Int((UB) * Rnd() + 1)
    tmp = AR(RI, 1)
    AR(RI, 1) = AR(i, 1)
    AR(i, 1) = tmp
Next i
End Function

 

[lrobbo314]

Or, if you have O365...

BookII.xlsm
	A	B	C	D	E
1	Sa		Ju	Mo	Sa
2	Ur		Ju	Mo	No
3	No		Ju	Mo	Ur
4	Ma		Ju	Mo	Ma
5	Ha		Ju	Mo	Ha
6	Ju
7	Mo
Sheet13

Cell Formulas
Range	Formula
C1:E5	C1	=LET(r,A1:A7,c,COUNTA(r),sb,SORTBY(SEQUENCE(c),RANDARRAY(c)),fi,INDEX(sb,1),se,INDEX(sb,2),INDEX(r,MAKEARRAY(c-2,3,LAMBDA(r,c,CHOOSE(c,fi,se,INDEX(FILTER(sb,(sb<>fi)*(sb<>se)),r))))))
Dynamic array formulas.

 

[Fluff]

Another option for 365

Fluff.xlsm
	A	B	C	D	E
1	Sa
2	Ur		Ur	Ju	Mo
3	No		Ur	Ju	Ha
4	Ma		Ur	Ju	No
5	Ha		Ur	Ju	Ma
6	Ju		Ur	Ju	Sa
7	Mo
Data

Cell Formulas
Range	Formula
C2:E6	C2	=LET(a,SORTBY(A1:A7,RANDARRAY(7)),b,MAKEARRAY(5,3,LAMBDA(r,c,IF(c=3,r+c-1,c))),INDEX(a,b))
Dynamic array formulas.

 

[Eric W]

I might be looking at this a completely different way, but here's a way to generate all 3 item combinations from a list of 7 with no repeats. This doesn't require Excel 365 or VBA.

Book1
	A	B	C	D	E
1	Sa		1	2	3
2	Ur		Sa	Ur	No
3	No		Sa	Ur	Ma
4	Ma		Sa	Ur	Ha
5	Ha		Sa	Ur	Ju
6	Ju		Sa	Ur	Mo
7	Mo		Sa	No	Ma
8			Sa	No	Ha
9			Sa	No	Ju
10			Sa	No	Mo
11			Sa	Ma	Ha
12			Sa	Ma	Ju
13			Sa	Ma	Mo
14			Sa	Ha	Ju
15			Sa	Ha	Mo
16			Sa	Ju	Mo
17			Ur	No	Ma
18			Ur	No	Ha
19			Ur	No	Ju
20			Ur	No	Mo
21			Ur	Ma	Ha
22			Ur	Ma	Ju
23			Ur	Ma	Mo
24			Ur	Ha	Ju
25			Ur	Ha	Mo
26			Ur	Ju	Mo
27			No	Ma	Ha
28			No	Ma	Ju
29			No	Ma	Mo
30			No	Ha	Ju
31			No	Ha	Mo
32			No	Ju	Mo
33			Ma	Ha	Ju
34			Ma	Ha	Mo
35			Ma	Ju	Mo
36			Ha	Ju	Mo
37
38
Sheet16

Cell Formulas
Range	Formula
C2	C2	=A1
D2	D2	=A2
E2	E2	=A3
C3:C38	C3	=IFERROR(IF(E2<>$A$7,C2,IF(D2<>$A$6,C2,IF(C2<>$A$5,INDEX($A$1:$A$7,MATCH(C2,$A$1:$A$7,0)+1),""))),"")
D3:D38	D3	=IFERROR(IF(E2<>$A$7,D2,IF(D2<>A$6,INDEX($A$1:$A$7,MATCH(D2,$A$1:$A$7,0)+1),IF(C2<>$A$5,INDEX($A$1:$A$7,MATCH(C2,$A$1:$A$7,0)+2),""))),"")
E3:E38	E3	=IFERROR(IF(E2<>$A$7,INDEX($A$1:$A$7,MATCH(E2,$A$1:$A$7,0)+1),IF(D2<>$A$6,INDEX($A$1:$A$7,MATCH(D2,$A$1:$A$7,0)+2),IF(C2<>$A$5,INDEX($A$1:$A$7,MATCH(C2,$A$1:$A$7,0)+3),""))),"")

 

[fastwriter]

Thank you all for your responses.
Again, I was looking for a solution without relying upon VBA.

@Eric W - Yes, this seems to work. Thank you!
@Fluff - Thx - I'll be sure to update my details.

 

[Fluff]

@Fluff - Thx - I'll be sure to update my details.

Except you don't seem to have done that.

 

[fastwriter]

@Fluff - No, need to be pushy. When I have another question, I'll be sure to update it.