Combinations Question

[chuckjitsu]

Good morning (or afternoon or evening, depending on where you are!). I searched here, but couldn't seem to find a specific answer to what I'm looking for, so if someone knows of a link and can provide it, that would be great. I'm also good with either a VBA solution or worksheet based solution if somebody can do that relatively quickly/easily.

I have four columns, A-D, and each column can contain a value from 1 to 8, meaning the total number of combinations would be 8^4, or 4096. The combination output would look like the below:

1111
1112
1113
etc., with the last value presumably being
8888

Thanks in advance for any assistance.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	1	1	1	1		1	1	1	1
2	1	1	1	2		1	1	1	2
3	1	1	1	3		1	1	1	3
4	1	1	1	4		1	1	1	4
5	1	1	1	5		1	1	1	5
6	1	1	1	6		1	1	1	6
7	1	1	1	7		1	1	1	7
8	1	1	1	8		1	1	1	8
9	1	1	2	1		1	1	2	1
10	1	1	2	2		1	1	2	2
11	1	1	2	3		1	1	2	3
12	1	1	2	4		1	1	2	4
13	1	1	2	5		1	1	2	5
14	1	1	2	6		1	1	2	6
15	1	1	2	7		1	1	2	7
16	1	1	2	8		1	1	2	8
17	1	1	3	1		1	1	3	1
18	1	1	3	2		1	1	3	2
Sheet32

Cell Formulas
Range	Formula
A1:D4096	A1	=MID(BASE(SEQUENCE(4096,,0),8,4),{1,2,3,4},1)+1
F1:I18	F1	=MID(BASE(ROW()-1,8,4),COLUMNS($F:F),1)+1
Dynamic array formulas.

The A1 formula requires Excel 365, the F1 formula should work in Excel 2013 and newer.

 

[chuckjitsu]

That was simpler than I thought it would be. The code below worked fine.

Sub combos()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim counter As Integer
Application.ScreenUpdating = False

counter = 0

For A = 1 To 8
For B = 1 To 8
For C = 1 To 8
For D = 1 To 8
counter = counter + 1
Cells(counter, 1) = A
Cells(counter, 2) = B
Cells(counter, 3) = C
Cells(counter, 4) = D
Next D
Next C
Next B
Next A

Application.ScreenUpdating = True


End Sub

 

[chuckjitsu]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	1	1	1	1		1	1	1	1
2	1	1	1	2		1	1	1	2
3	1	1	1	3		1	1	1	3
4	1	1	1	4		1	1	1	4
5	1	1	1	5		1	1	1	5
6	1	1	1	6		1	1	1	6
7	1	1	1	7		1	1	1	7
8	1	1	1	8		1	1	1	8
9	1	1	2	1		1	1	2	1
10	1	1	2	2		1	1	2	2
11	1	1	2	3		1	1	2	3
12	1	1	2	4		1	1	2	4
13	1	1	2	5		1	1	2	5
14	1	1	2	6		1	1	2	6
15	1	1	2	7		1	1	2	7
16	1	1	2	8		1	1	2	8
17	1	1	3	1		1	1	3	1
18	1	1	3	2		1	1	3	2
Sheet32

Cell Formulas
Range	Formula
A1:D4096	A1	=MID(BASE(SEQUENCE(4096,,0),8,4),{1,2,3,4},1)+1
F1:I18	F1	=MID(BASE(ROW()-1,8,4),COLUMNS($F:F),1)+1
Dynamic array formulas.

The A1 formula requires Excel 365, the F1 formula should work in Excel 2013 and newer.

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	1	1	1	1		1	1	1	1
2	1	1	1	2		1	1	1	2
3	1	1	1	3		1	1	1	3
4	1	1	1	4		1	1	1	4
5	1	1	1	5		1	1	1	5
6	1	1	1	6		1	1	1	6
7	1	1	1	7		1	1	1	7
8	1	1	1	8		1	1	1	8
9	1	1	2	1		1	1	2	1
10	1	1	2	2		1	1	2	2
11	1	1	2	3		1	1	2	3
12	1	1	2	4		1	1	2	4
13	1	1	2	5		1	1	2	5
14	1	1	2	6		1	1	2	6
15	1	1	2	7		1	1	2	7
16	1	1	2	8		1	1	2	8
17	1	1	3	1		1	1	3	1
18	1	1	3	2		1	1	3	2
Sheet32

Cell Formulas
Range	Formula
A1:D4096	A1	=MID(BASE(SEQUENCE(4096,,0),8,4),{1,2,3,4},1)+1
F1:I18	F1	=MID(BASE(ROW()-1,8,4),COLUMNS($F:F),1)+1
Dynamic array formulas.

The A1 formula requires Excel 365, the F1 formula should work in Excel 2013 and newer.

Hi Eric. I tried your solution but I must be doing something wrong as I got different results. Here's what I did.

Selected A1:D4096
Inserted your formula in the formula bar
selected Ctrl+Shift+Enter

this gave me
1111 for 512 rows
2222 for 512 rows
3333 for 512 rows
etc to
8888 for 512 rows

 

[Fluff]

Which of the two formulae are you talking about?

 

[chuckjitsu]

Which of the two formulae are you talking about?

Hi Fluff. The first one in Eric's Cell Formulas table, where Range = A1:D4096. That's the one I used and got the results I described.

 

[Fluff]

In that case delete all those cells & then just put the formula into A1 & drag across to D1. Do not fill down & do not use Ctrl Shift Enter.

 

[Fluff]

Apologies, hadn't noticed that Eric had change the formula in A1, do not drag it across, just put it in A1 only as a normal formula

 

[chuckjitsu]

Apologies, hadn't noticed that Eric had change the formula in A1, do not drag it across, just put it in A1 only as a normal formula

That did the trick. Thanks for the assist!

 

[chuckjitsu]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	1	1	1	1		1	1	1	1
2	1	1	1	2		1	1	1	2
3	1	1	1	3		1	1	1	3
4	1	1	1	4		1	1	1	4
5	1	1	1	5		1	1	1	5
6	1	1	1	6		1	1	1	6
7	1	1	1	7		1	1	1	7
8	1	1	1	8		1	1	1	8
9	1	1	2	1		1	1	2	1
10	1	1	2	2		1	1	2	2
11	1	1	2	3		1	1	2	3
12	1	1	2	4		1	1	2	4
13	1	1	2	5		1	1	2	5
14	1	1	2	6		1	1	2	6
15	1	1	2	7		1	1	2	7
16	1	1	2	8		1	1	2	8
17	1	1	3	1		1	1	3	1
18	1	1	3	2		1	1	3	2
Sheet32

Cell Formulas
Range	Formula
A1:D4096	A1	=MID(BASE(SEQUENCE(4096,,0),8,4),{1,2,3,4},1)+1
F1:I18	F1	=MID(BASE(ROW()-1,8,4),COLUMNS($F:F),1)+1
Dynamic array formulas.

The A1 formula requires Excel 365, the F1 formula should work in Excel 2013 and newer.

Thanks Eric! I went with your first formula and got it working with an assist from Fluff. Works great!