Generating combinations.

[serge]

Hi everyone,
I have 3 digits 1,2,3 and 7 columns.
I need to create all possible combinations, starting in B3:H3 which formula could do the job ? Thank you.
See example below.

 

[Eric W]

Try:

Book3
	A	B	C	D	E	F	G	H
1
2
3		1	1	1	1	1	1	1
4		1	1	1	1	1	1	2
5		1	1	1	1	1	1	3
6		1	1	1	1	1	2	1
7		1	1	1	1	1	2	2
8		1	1	1	1	1	2	3
9		1	1	1	1	1	3	1
10		1	1	1	1	1	3	2
11		1	1	1	1	1	3	3
12		1	1	1	1	2	1	1
13		1	1	1	1	2	1	2
14		1	1	1	1	2	1	3
15		1	1	1	1	2	2	1
16		1	1	1	1	2	2	2
17		1	1	1	1	2	2	3
18		1	1	1	1	2	3	1
19		1	1	1	1	2	3	2
20		1	1	1	1	2	3	3
21		1	1	1	1	3	1	1
22		1	1	1	1	3	1	2
23		1	1	1	1	3	1	3
24		1	1	1	1	3	2	1
25		1	1	1	1	3	2	2
26		1	1	1	1	3	2	3
27		1	1	1	1	3	3	1
28		1	1	1	1	3	3	2
29		1	1	1	1	3	3	3
30		1	1	1	2	1	1	1
31		1	1	1	2	1	1	2
32		1	1	1	2	1	1	3
33
34
Sheet7

Cell Formulas
Range	Formula
B3:H32	B3	=MOD(INT((ROW()-3)/(3^(COLUMNS(B:$H)-1))),3)+1

 

[serge]

THANK YOU SO MUCH Eric, that's exactly what I needed, it work perfect, you save me so much time.

 

[serge]

I may need exactly the same thing but in 8 columns, what need to be change in the formula, please ?
Thank you.

 

[Eric W]

No change to the formula, just add the 8th column on the left.

Just to explain a bit:

=MOD(INT((ROW()-3)/(3^(COLUMNS(B:$H)-1))),3)+1

The red 3 is the starting row of your range. The green 3s are how high you go before repeating (1,2,3,1,2,3, etc.). The purple part is a way to get a counter starting at 1 on the right, going up as you drag the formula to the left. So the right most column should match the letter.

 

[serge]

Thank you Eric, now I understand better with your explanation, I really appreciate your help.