Permute values in one column

[thundaraga]

Hello,

I have a list of values in column A such as

A
B
C
D
E

I want to output the list of permutations in column B for 2, 3 and 4 ways.:

A. B
B. A
A, B, C
A, C, B
B, A, C
A, B, C, D
A, C, B, D
B, A, C, D
E, A, C, D

Does anyone have any idea how it could be done in VBA?

Thank you!

 

[JamesCanale]

Here is a non-VBA solution. It might have been welded into one long nasty formula, but it gets too complex. LET might make quick work of it, but I don't have LET.

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1															RESULT
2		0	0000	0	0	0	0					A	0	A	AA
3	A	1	0001	0	0	0	1				A	B	0	B	AC
4	B	2	0002	0	0	0	2				B	C	0	C	AD
5	C	3	0003	0	0	0	3				C	D	0	D	AE
6	D	4	0004	0	0	0	4				D	E	0	E	BA
7	E	5	0005	0	0	0	5				E	A	0	A	BB
8		6	0010	0	0	1	0			A		AA	0	AA	BD
9		7	0011	0	0	1	1			A	A	AB	1	AC	BE
10		8	0012	0	0	1	2			A	B	AC	0	AD	CA
11		9	0013	0	0	1	3			A	C	AD	0	AE	CB
12		10	0014	0	0	1	4			A	D	AE	0	B	CC
13		11	0015	0	0	1	5			A	E	B	0	BA	CE
14		12	0020	0	0	2	0			B		BA	0	BB	DA
15		13	0021	0	0	2	1			B	A	BB	0	BD	DB
16		14	0022	0	0	2	2			B	B	BC	1	BE	DC
17		15	0023	0	0	2	3			B	C	BD	0	C	DD
18		16	0024	0	0	2	4			B	D	BE	0	CA	EA
19		17	0025	0	0	2	5			B	E	C	0	CB	EB
20		18	0030	0	0	3	0			C		CA	0	CC	EC
21		19	0031	0	0	3	1			C	A	CB	0	CE	ED
22		20	0032	0	0	3	2			C	B	CC	0	D	EE
23		21	0033	0	0	3	3			C	C	CD	1	DA	ABA
24		22	0034	0	0	3	4			C	D	CE	0	DB	ABD
25		23	0035	0	0	3	5			C	E	D	0	DC	ABE
26		24	0040	0	0	4	0			D		DA	0	DD	ACA
27		25	0041	0	0	4	1			D	A	DB	0	E	ACC
28		26	0042	0	0	4	2			D	B	DC	0	EA	ACE
29		27	0043	0	0	4	3			D	C	DD	0	EB	ADA
30		28	0044	0	0	4	4			D	D	DE	1	EC	ADC
31		29	0045	0	0	4	5			D	E	E	0	ED	ADD
32		30	0050	0	0	5	0			E		EA	0	EE	AEA
33		31	0051	0	0	5	1			E	A	EB	0	AA	AEC
34		32	0052	0	0	5	2			E	B	EC	0	AC	AED
Sheet28

Cell Formulas
Range	Formula
A2	A2	=""
B2:B7777	B2	=SEQUENCE(6^5,1,0)
C2:C7777	C2	=BASE(B2#,6,4)
D2:G7777	D2	=MID(C2#,{1,2,3,4},1)
H2:K7777	H2	=INDEX(A2:A7,D2#+1)
L2:L7777	L2	=INDEX(H2#,ROW(B2#),1)&INDEX(H2#,ROW(B2#),2)&INDEX(H2#,ROW(B2#),3)&INDEX(H2#,ROW(B2#),4)
M2:M7777	M2	=(LEN(C2#)-LEN(SUBSTITUTE(C2#,1,""))>1)+(LEN(C2#)-LEN(SUBSTITUTE(C2#,2,""))>1)+(LEN(C2#)-LEN(SUBSTITUTE(C2#,3,""))>1)+(LEN(C2#)-LEN(SUBSTITUTE(C2#,4,""))>1)++(LEN(C2#)-LEN(SUBSTITUTE(C2#,5,""))>1)
N2:N1547	N2	=FILTER(L2#,M2#=0,"")
O2:O346	O2	=UNIQUE(FILTER(N2#,(LEN(N2#)>1),""))
Dynamic array formulas.

 

[thundaraga]

Here is a non-VBA solution. It might have been welded into one long nasty formula, but it gets too complex. LET might make quick work of it, but I don't have LET.

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1															RESULT
2		0	0000	0	0	0	0					A	0	A	AA
3	A	1	0001	0	0	0	1				A	B	0	B	AC
4	B	2	0002	0	0	0	2				B	C	0	C	AD
5	C	3	0003	0	0	0	3				C	D	0	D	AE
6	D	4	0004	0	0	0	4				D	E	0	E	BA
7	E	5	0005	0	0	0	5				E	A	0	A	BB
8		6	0010	0	0	1	0			A		AA	0	AA	BD
9		7	0011	0	0	1	1			A	A	AB	1	AC	BE
10		8	0012	0	0	1	2			A	B	AC	0	AD	CA
11		9	0013	0	0	1	3			A	C	AD	0	AE	CB
12		10	0014	0	0	1	4			A	D	AE	0	B	CC
13		11	0015	0	0	1	5			A	E	B	0	BA	CE
14		12	0020	0	0	2	0			B		BA	0	BB	DA
15		13	0021	0	0	2	1			B	A	BB	0	BD	DB
16		14	0022	0	0	2	2			B	B	BC	1	BE	DC
17		15	0023	0	0	2	3			B	C	BD	0	C	DD
18		16	0024	0	0	2	4			B	D	BE	0	CA	EA
19		17	0025	0	0	2	5			B	E	C	0	CB	EB
20		18	0030	0	0	3	0			C		CA	0	CC	EC
21		19	0031	0	0	3	1			C	A	CB	0	CE	ED
22		20	0032	0	0	3	2			C	B	CC	0	D	EE
23		21	0033	0	0	3	3			C	C	CD	1	DA	ABA
24		22	0034	0	0	3	4			C	D	CE	0	DB	ABD
25		23	0035	0	0	3	5			C	E	D	0	DC	ABE
26		24	0040	0	0	4	0			D		DA	0	DD	ACA
27		25	0041	0	0	4	1			D	A	DB	0	E	ACC
28		26	0042	0	0	4	2			D	B	DC	0	EA	ACE
29		27	0043	0	0	4	3			D	C	DD	0	EB	ADA
30		28	0044	0	0	4	4			D	D	DE	1	EC	ADC
31		29	0045	0	0	4	5			D	E	E	0	ED	ADD
32		30	0050	0	0	5	0			E		EA	0	EE	AEA
33		31	0051	0	0	5	1			E	A	EB	0	AA	AEC
34		32	0052	0	0	5	2			E	B	EC	0	AC	AED
Sheet28

Cell Formulas
Range	Formula
A2	A2	=""
B2:B7777	B2	=SEQUENCE(6^5,1,0)
C2:C7777	C2	=BASE(B2#,6,4)
D2:G7777	D2	=MID(C2#,{1,2,3,4},1)
H2:K7777	H2	=INDEX(A2:A7,D2#+1)
L2:L7777	L2	=INDEX(H2#,ROW(B2#),1)&INDEX(H2#,ROW(B2#),2)&INDEX(H2#,ROW(B2#),3)&INDEX(H2#,ROW(B2#),4)
M2:M7777	M2	=(LEN(C2#)-LEN(SUBSTITUTE(C2#,1,""))>1)+(LEN(C2#)-LEN(SUBSTITUTE(C2#,2,""))>1)+(LEN(C2#)-LEN(SUBSTITUTE(C2#,3,""))>1)+(LEN(C2#)-LEN(SUBSTITUTE(C2#,4,""))>1)++(LEN(C2#)-LEN(SUBSTITUTE(C2#,5,""))>1)
N2:N1547	N2	=FILTER(L2#,M2#=0,"")
O2:O346	O2	=UNIQUE(FILTER(N2#,(LEN(N2#)>1),""))
Dynamic array formulas.

Wow fantastic thanks! Just wondering if I want to have a comma added between each value i.e. (A,B,C,D) instead of (ABCD), how could i go about changing it?

 

[thundaraga]

hmm i realised it starts repeating the permutations after a certain number of rows and it is limited to 5 values in Column A. If there are more than 5 values in column A, then the permutation result would not show them.

 

[JamesCanale]

To use more than 5, change the formula in B2 to =sequence((number of different letters+1)^(max number of characters in the output+1). In H2, change the index range from A2:A7 to A2:A??.

I don't think it repeats as much as it isn't sorted in the way you would expect. Nothing is there twice in Column O.

Commas are tricky while trying to maintain the live arrays... Put this in L2:

=SUBSTITUTE(TRIM(INDEX(H2#,ROW(B2#),1)&" "&INDEX(H2#,ROW(B2#),2)&" "&INDEX(H2#,ROW(B2#),3)&" "&INDEX(H2#,ROW(B2#),4))," ",", ")

 

[thundaraga]

To use more than 5, change the formula in B2 to =sequence((number of different letters+1)^(max number of characters in the output+1). In H2, change the index range from A2:A7 to A2:A??.

I don't think it repeats as much as it isn't sorted in the way you would expect. Nothing is there twice in Column O.

Commas are tricky while trying to maintain the live arrays... Put this in L2:

=SUBSTITUTE(TRIM(INDEX(H2#,ROW(B2#),1)&" "&INDEX(H2#,ROW(B2#),2)&" "&INDEX(H2#,ROW(B2#),3)&" "&INDEX(H2#,ROW(B2#),4))," ",", ")

Hmm the result still shows the permutation of 5 letters. Do i also need to change the formula in cell C2?

 

[JamesCanale]

You are right. Change the BASE formula:

=BASE(B2#,{number of different items +1},{max number in a set})