# Combinations

#### goggem

##### New Member
I want a formula to work out every 9-digit number, that uses only the values 1,2,3 and 4.

(I don't mind having the numbers in separate cells)

Like this:

1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 2
1 1 1 1 1 1 1 1 3
1 1 1 1 1 1 1 1 4
1 1 1 1 1 1 1 2 1
1 1 1 1 1 1 1 2 2
1 1 1 1 1 1 1 2 3
1 1 1 1 1 1 1 2 4
1 1 1 1 1 1 1 3 1
1 1 1 1 1 1 1 3 2
1 1 1 1 1 1 1 3 3
1 1 1 1 1 1 1 3 4
1 1 1 1 1 1 1 4 1
1 1 1 1 1 1 1 4 2
1 1 1 1 1 1 1 4 3
1 1 1 1 1 1 1 4 4
1 1 1 1 1 1 2 1 1
1 1 1 1 1 1 2 1 2
1 1 1 1 1 1 2 1 3
1 1 1 1 1 1 2 1 4
1 1 1 1 1 1 2 2 1
1 1 1 1 1 1 2 2 2
1 1 1 1 1 1 2 2 3
1 1 1 1 1 1 2 2 4
1 1 1 1 1 1 2 3 1
1 1 1 1 1 1 2 3 2
1 1 1 1 1 1 2 3 3
1 1 1 1 1 1 2 3 4
1 1 1 1 1 1 2 4 1
1 1 1 1 1 1 2 4 2
1 1 1 1 1 1 2 4 3
1 1 1 1 1 1 2 4 4
....
....

I can do the first column with a formula - see here: http://www.editgrid.com/user/adamhman/Combinations

I would like to be able to do the other columns in a similar way.

Any help would be appreciated!

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Andrew Fergus

##### MrExcel MVP
Hi

Use a separate column for each digit. In your first row enter the value 1 into each column. In cell I2 (the 9th column) use the formula:
=MOD(I1,4)+1
and copy down,

In cell H2 (the 8th column) use the formula:
=IF(AND(I2=1,I1=4),MOD(H1,4)+1,H1)
and copy down 255 rows.

Copy the formula from cell H2 into cells A2:G2 and copy down 255 rows.

HTH, Andrew

#### jbeaucaire

##### Well-known Member
There are 94 million + answers, according to the macro I tried to use....if that's true you're not going to get that on Excel 2003.

There's a macro on this website that will give you what you want, perhaps Excel 2007 can run it with your desired parameters:

Put a C in A1, 9 in A2, the number 1 in the next 9 cells, the number 2 in the next 9, 3 in the next 9 and 4 in the final 9. Then run the macro.

The answers will appear in a comma delimited list. You can parse those out to separate cells if you wish, or run another macro to remove the commas.

#### Andrew Fergus

##### MrExcel MVP
My bad with the 255 rows - I only tested it for the first 4 digits!

The first 65536 rows gets me to the number:
144444444
which is exactly a quarter of the numbers needed so I suspect the total number will be a lot less than 94 million!

Andrew

Last edited:

#### mikerickson

##### MrExcel MVP
Put this CSE formula in a cell and drag down

=SUM((10^(COLUMN(\$A\$1:\$I\$1)-1))*(1+MOD(INT((ROW(A1)-1)/(4^(COLUMN(\$A\$1:\$I\$1)-1))),4)))

As has been noted, thats a lot of values.

What you are doing is counting from 0 to 4^10 (and then substituting 1 for 0, 2 for 1, 3 for 2, 4 for 3)

What do you need all these numbers for?
Analysis may get you your result without listing a bunch of numbers.

#### goggem

##### New Member
I came up with an easier way to do it that is not recursive based on the above values.
its as lease recursive as possible. all values are based off the number, from 0-262,143
(since there are 262,144 combinations)

Just thought I'd share.

#### Andrew Fergus

##### MrExcel MVP
That seems an awfully complicated way of doing it! But hey if it works then so be it.....

Cheers
Andrew

Replies
4
Views
129
Replies
6
Views
144
Replies
7
Views
117
Replies
1
Views
96
Replies
41
Views
471

1,191,076
Messages
5,984,497
Members
439,893
Latest member
johnsboxftm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back