Formula to find all possible 4 digit combinations using the numbers0,1,2,3,4,5,6,7,8,9

D

Danelle Sanchez

Hi,
I need to create a formula that will show the results of all possible 4 digit combinations using the numbers 0,1,2,3,4,5,6,7,8,9. I found a similar question on this forum but it was only for the numbers 0,1,3,4.
Here is the formula I found but did not know how to edit it for my required results

0, 1, 2, 3, 4

=MOD(INT((ROW(A2)-1)/125),5)&MOD(INT((ROW(A2)-1)/25),5)&MOD(INT((ROW(A2)-1)/5),5)&MOD(ROW(A2)-1,5)

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorial...b-7374d3da3425/wpf-and-the-model-view-vi.aspx
 
M

Mike H

Danelle,

I've been trying to get my head around how may combinations this is but it
is so many Excel can't calculate it or at least i can't come up with a
formula. having said that here's the formula you posted modified for all
combinations of 0 - 9

=MOD(INT((ROW(A2)-1)/9765625),5)&MOD(INT((ROW(A2)-1)/1953125),5)&MOD(INT((ROW(A2)-1)/390625),5)&MOD(INT((ROW(A2)-1)/78125),5)&MOD(INT((ROW(A2)-1)/15625),5)&MOD(INT((ROW(A2)-1)/3125),5)&MOD(INT((ROW(A2)-1)/625),5)&MOD(INT((ROW(A2)-1)/125),5)&MOD(INT((ROW(A2)-1)/25),5)&MOD(INT((ROW(A2)-1)/5),5)&MOD(ROW(A2)-1,5)

BTW I ran out of rows in Excel 2007 and was nowhere near the end

Mike
 
P

Pete_UK

Put this formula in A1:

=ROW(A1)-1

and use a custom format on the cell of "0000" (without the quotes).

Or alternatively use this formula:

=TEXT(ROW(A1)-1,"0000")

and have the format as General.

Then copy the down to A10000.

Both formulae will give you all the 4-digit numbers from 0000 to 9999.

Hope this helps.

Pete
 
G

Gord Dibben

Mike

Using the PERMUT fuction with 10 objects and 4 items I get 5040 possible..

Using COMBIN function I get 210 possibles.

Or am I missing something?


Gord Dibben MS Excel MVP
 
J

JLatham

Maybe I'm missing something -- given that we can have any 4 digits from the
set of 10 (0-9), aren't there 10,000 numbers that can be created? 0000
through 9999 ?? Assuming each digit can be used more than once?
 
J

JLatham

Or, just to make things more difficult, you can do it with a macro:

Sub Build4DigitNumsFrom10()
Dim ThousandsLoop As Integer
Dim HundredsLoop As Integer
Dim TensLoop As Integer
Dim OnesLoop As Integer
Dim myNumber As Integer

For ThousandsLoop = 0 To 9
For HundredsLoop = 0 To 9
For TensLoop = 0 To 9
For OnesLoop = 0 To 9
myNumber = ThousandsLoop * 1000 + _
HundredsLoop * 100 + _
TensLoop * 10 + OnesLoop
ActiveSheet.Range("A" & myNumber + 1) = _
myNumber
ActiveSheet.Range("A" & myNumber + 1).NumberFormat = _
"0000"
Next ' ones loop
Next ' tens loop
Next ' hundreds loop
Next ' thousands loop
End Sub
 
L

lyndsey wilson

Hi
I have a similar problem. I need to find all 4 digit combinations of numbers 1-5 but not sure which parts of the above suggestions to change. I tried the excel thing but couldnt get it to work as printed...
Can anyone help me please?
Thanks
 
P

Pete_UK

Lyndsey,

put this formula in A1 of a blank sheet:

=MOD(1000*(INT((ROW(A1)-1)/125)),5000)+1000+MOD(100*(INT((ROW(A1)-1)/
25)),500)+100+MOD(10*(INT((ROW(A1)-1)/5)),50)+10+MOD(ROW(A1)-1,5)+1

and copy down to A625 (after which it repeats). This is all one
formula, so be wary of spurious line breaks. I've repeated it below so
that you can see the 4 elements making up each digit:

=MOD(1000*(INT((ROW(A1)-1)/125)),5000)+1000
+MOD(100*(INT((ROW(A1)-1)/25)),500)+100
+MOD(10*(INT((ROW(A1)-1)/5)),50)+10
+MOD(ROW(A1)-1,5)+1

Hope this helps.

Pete
 
J

joeu2004

I have a similar problem. I need to find all 4 digit
combinations of numbers 1-5 but not sure which parts
of the above suggestions to change. I tried the excel
thing but couldnt get it to work as printed...
Can anyone help me please?
Click to expand...

Which "excel thing" did you try? Specifically, what did you write?
It is best to copy-and-paste from the Formula Bar.

And it is unwise to attach a new question as a response to an old
discussion. Many helpful people might not even see it.

-----

There are 625 possible solutions (5^4), so the solution is quite
tractable.

The most straight-forward solution seems to be the first suggestion,
modified for your purposes. I think the easiest approach is....

Set up a helper column (e.g. B1:B625) with the numbers 0 through 624.
For example, put zero into B1, select B1, then click on Edit > Fill >
Series, select Column, and enter 624 into Stop Value.

Then put the following formula into the first cell of a parallel
column (e.g. C1) and copy down (e.g. through C625):

=--( MOD(INT(B1/125),5)+1 & MOD(INT(B1/25),5)+1
& MOD(INT(B1/5),5)+1 & MOD(B1,5)+1 )

The double negation (--) converts the text result into a numeric
result.

If you do not want to use a helper column (but that is oh-so-more
efficient) and your formulas start in row 1, replace B1 with the
expression (ROW()-1) exactly as written with surrounding parentheses.

If you want to start your column in another row (e.g. B15), replace B1
with the expression (ROW()-ROW($B$15)) with surrounding parentheses.
 
M

mehmet

I have the results of
any 4 digits from 10; 1. 0 1 2 3
2. 0 1 2 4
3. 0 1 3 4
4. 0 2 3 4
5. 1 2 3 4
6. 0 1 2 5
7. 0 1 3 5
8. 0 2 3 5
9. 1 2 3 5
10. 0 1 4 5
11. 0 2 4 5
12. 1 2 4 5
13. 0 3 4 5
14. 1 3 4 5
15. 2 3 4 5
16. 0 1 2 6
17. 0 1 3 6
18. 0 2 3 6
19. 1 2 3 6
20. 0 1 4 6
21. 0 2 4 6
22. 1 2 4 6
23. 0 3 4 6
24. 1 3 4 6
25. 2 3 4 6
26. 0 1 5 6
27. 0 2 5 6
28. 1 2 5 6
29. 0 3 5 6
30. 1 3 5 6
31. 2 3 5 6
32. 0 4 5 6
33. 1 4 5 6
34. 2 4 5 6
35. 3 4 5 6
36. 0 1 2 7
37. 0 1 3 7
38. 0 2 3 7
39. 1 2 3 7
40. 0 1 4 7
41. 0 2 4 7
42. 1 2 4 7
43. 0 3 4 7
44. 1 3 4 7
45. 2 3 4 7
46. 0 1 5 7
47. 0 2 5 7
48. 1 2 5 7
49. 0 3 5 7
50. 1 3 5 7
51. 2 3 5 7
52. 0 4 5 7
53. 1 4 5 7
54. 2 4 5 7
55. 3 4 5 7
56. 0 1 6 7
57. 0 2 6 7
58. 1 2 6 7
59. 0 3 6 7
60. 1 3 6 7
61. 2 3 6 7
62. 0 4 6 7
63. 1 4 6 7
64. 2 4 6 7
65. 3 4 6 7
66. 0 5 6 7
67. 1 5 6 7
68. 2 5 6 7
69. 3 5 6 7
70. 4 5 6 7
71. 0 1 2 8
72. 0 1 3 8
73. 0 2 3 8
74. 1 2 3 8
75. 0 1 4 8
76. 0 2 4 8
77. 1 2 4 8
78. 0 3 4 8
79. 1 3 4 8
80. 2 3 4 8
81. 0 1 5 8
82. 0 2 5 8
83. 1 2 5 8
84. 0 3 5 8
85. 1 3 5 8
86. 2 3 5 8
87. 0 4 5 8
88. 1 4 5 8
89. 2 4 5 8
90. 3 4 5 8
91. 0 1 6 8
92. 0 2 6 8
93. 1 2 6 8
94. 0 3 6 8
95. 1 3 6 8
96. 2 3 6 8
97. 0 4 6 8
98. 1 4 6 8
99. 2 4 6 8
100. 3 4 6 8
101. 0 5 6 8
102. 1 5 6 8
103. 2 5 6 8
104. 3 5 6 8
105. 4 5 6 8
106. 0 1 7 8
107. 0 2 7 8
108. 1 2 7 8
109. 0 3 7 8
110. 1 3 7 8
111. 2 3 7 8
112. 0 4 7 8
113. 1 4 7 8
114. 2 4 7 8
115. 3 4 7 8
116. 0 5 7 8
117. 1 5 7 8
118. 2 5 7 8
119. 3 5 7 8
120. 4 5 7 8
121. 0 6 7 8
122. 1 6 7 8
123. 2 6 7 8
124. 3 6 7 8
125. 4 6 7 8
126. 5 6 7 8
127. 0 1 2 9
128. 0 1 3 9
129. 0 2 3 9
130. 1 2 3 9
131. 0 1 4 9
132. 0 2 4 9
133. 1 2 4 9
134. 0 3 4 9
135. 1 3 4 9
136. 2 3 4 9
137. 0 1 5 9
138. 0 2 5 9
139. 1 2 5 9
140. 0 3 5 9
141. 1 3 5 9
142. 2 3 5 9
143. 0 4 5 9
144. 1 4 5 9
145. 2 4 5 9
146. 3 4 5 9
147. 0 1 6 9
148. 0 2 6 9
149. 1 2 6 9
150. 0 3 6 9
151. 1 3 6 9
152. 2 3 6 9
153. 0 4 6 9
154. 1 4 6 9
155. 2 4 6 9
156. 3 4 6 9
157. 0 5 6 9
158. 1 5 6 9
159. 2 5 6 9
160. 3 5 6 9
161. 4 5 6 9
162. 0 1 7 9
163. 0 2 7 9
164. 1 2 7 9
165. 0 3 7 9
166. 1 3 7 9
167. 2 3 7 9
168. 0 4 7 9
169. 1 4 7 9
170. 2 4 7 9
171. 3 4 7 9
172. 0 5 7 9
173. 1 5 7 9
174. 2 5 7 9
175. 3 5 7 9
176. 4 5 7 9
177. 0 6 7 9
178. 1 6 7 9
179. 2 6 7 9
180. 3 6 7 9
181. 4 6 7 9
182. 5 6 7 9
183. 0 1 8 9
184. 0 2 8 9
185. 1 2 8 9
186. 0 3 8 9
187. 1 3 8 9
188. 2 3 8 9
189. 0 4 8 9
190. 1 4 8 9
191. 2 4 8 9
192. 3 4 8 9
193. 0 5 8 9
194. 1 5 8 9
195. 2 5 8 9
196. 3 5 8 9
197. 4 5 8 9
198. 0 6 8 9
199. 1 6 8 9
200. 2 6 8 9
201. 3 6 8 9
202. 4 6 8 9
203. 5 6 8 9
204. 0 7 8 9
205. 1 7 8 9
206. 2 7 8 9
207. 3 7 8 9
208. 4 7 8 9
209. 5 7 8 9
210. 6 7 8 9
 
M

mehmet

1. 0 1 2 3
2. 0 1 2 4
3. 0 1 3 4
4. 0 2 3 4
5. 1 2 3 4
6. 0 1 2 5
7. 0 1 3 5
8. 0 2 3 5
9. 1 2 3 5
10. 0 1 4 5
11. 0 2 4 5
12. 1 2 4 5
13. 0 3 4 5
14. 1 3 4 5
15. 2 3 4 5
16. 0 1 2 6
17. 0 1 3 6
18. 0 2 3 6
19. 1 2 3 6
20. 0 1 4 6
21. 0 2 4 6
22. 1 2 4 6
23. 0 3 4 6
24. 1 3 4 6
25. 2 3 4 6
26. 0 1 5 6
27. 0 2 5 6
28. 1 2 5 6
29. 0 3 5 6
30. 1 3 5 6
31. 2 3 5 6
32. 0 4 5 6
33. 1 4 5 6
34. 2 4 5 6
35. 3 4 5 6
36. 0 1 2 7
37. 0 1 3 7
38. 0 2 3 7
39. 1 2 3 7
40. 0 1 4 7
41. 0 2 4 7
42. 1 2 4 7
43. 0 3 4 7
44. 1 3 4 7
45. 2 3 4 7
46. 0 1 5 7
47. 0 2 5 7
48. 1 2 5 7
49. 0 3 5 7
50. 1 3 5 7
51. 2 3 5 7
52. 0 4 5 7
53. 1 4 5 7
54. 2 4 5 7
55. 3 4 5 7
56. 0 1 6 7
57. 0 2 6 7
58. 1 2 6 7
59. 0 3 6 7
60. 1 3 6 7
61. 2 3 6 7
62. 0 4 6 7
63. 1 4 6 7
64. 2 4 6 7
65. 3 4 6 7
66. 0 5 6 7
67. 1 5 6 7
68. 2 5 6 7
69. 3 5 6 7
70. 4 5 6 7
71. 0 1 2 8
72. 0 1 3 8
73. 0 2 3 8
74. 1 2 3 8
75. 0 1 4 8
76. 0 2 4 8
77. 1 2 4 8
78. 0 3 4 8
79. 1 3 4 8
80. 2 3 4 8
81. 0 1 5 8
82. 0 2 5 8
83. 1 2 5 8
84. 0 3 5 8
85. 1 3 5 8
86. 2 3 5 8
87. 0 4 5 8
88. 1 4 5 8
89. 2 4 5 8
90. 3 4 5 8
91. 0 1 6 8
92. 0 2 6 8
93. 1 2 6 8
94. 0 3 6 8
95. 1 3 6 8
96. 2 3 6 8
97. 0 4 6 8
98. 1 4 6 8
99. 2 4 6 8
100. 3 4 6 8
101. 0 5 6 8
102. 1 5 6 8
103. 2 5 6 8
104. 3 5 6 8
105. 4 5 6 8
106. 0 1 7 8
107. 0 2 7 8
108. 1 2 7 8
109. 0 3 7 8
110. 1 3 7 8
111. 2 3 7 8
112. 0 4 7 8
113. 1 4 7 8
114. 2 4 7 8
115. 3 4 7 8
116. 0 5 7 8
117. 1 5 7 8
118. 2 5 7 8
119. 3 5 7 8
120. 4 5 7 8
121. 0 6 7 8
122. 1 6 7 8
123. 2 6 7 8
124. 3 6 7 8
125. 4 6 7 8
126. 5 6 7 8
127. 0 1 2 9
128. 0 1 3 9
129. 0 2 3 9
130. 1 2 3 9
131. 0 1 4 9
132. 0 2 4 9
133. 1 2 4 9
134. 0 3 4 9
135. 1 3 4 9
136. 2 3 4 9
137. 0 1 5 9
138. 0 2 5 9
139. 1 2 5 9
140. 0 3 5 9
141. 1 3 5 9
142. 2 3 5 9
143. 0 4 5 9
144. 1 4 5 9
145. 2 4 5 9
146. 3 4 5 9
147. 0 1 6 9
148. 0 2 6 9
149. 1 2 6 9
150. 0 3 6 9
151. 1 3 6 9
152. 2 3 6 9
153. 0 4 6 9
154. 1 4 6 9
155. 2 4 6 9
156. 3 4 6 9
157. 0 5 6 9
158. 1 5 6 9
159. 2 5 6 9
160. 3 5 6 9
161. 4 5 6 9
162. 0 1 7 9
163. 0 2 7 9
164. 1 2 7 9
165. 0 3 7 9
166. 1 3 7 9
167. 2 3 7 9
168. 0 4 7 9
169. 1 4 7 9
170. 2 4 7 9
171. 3 4 7 9
172. 0 5 7 9
173. 1 5 7 9
174. 2 5 7 9
175. 3 5 7 9
176. 4 5 7 9
177. 0 6 7 9
178. 1 6 7 9
179. 2 6 7 9
180. 3 6 7 9
181. 4 6 7 9
182. 5 6 7 9
183. 0 1 8 9
184. 0 2 8 9
185. 1 2 8 9
186. 0 3 8 9
187. 1 3 8 9
188. 2 3 8 9
189. 0 4 8 9
190. 1 4 8 9
191. 2 4 8 9
192. 3 4 8 9
193. 0 5 8 9
194. 1 5 8 9
195. 2 5 8 9
196. 3 5 8 9
197. 4 5 8 9
198. 0 6 8 9
199. 1 6 8 9
200. 2 6 8 9
201. 3 6 8 9
202. 4 6 8 9
203. 5 6 8 9
204. 0 7 8 9
205. 1 7 8 9
206. 2 7 8 9
207. 3 7 8 9
208. 4 7 8 9
209. 5 7 8 9
210. 6 7 8 9
 
M

mehmet

1. 0 1 2 3
2. 0 1 2 4
3. 0 1 3 4
4. 0 2 3 4
5. 1 2 3 4
6. 0 1 2 5
7. 0 1 3 5
8. 0 2 3 5
9. 1 2 3 5
10. 0 1 4 5
11. 0 2 4 5
12. 1 2 4 5
13. 0 3 4 5
14. 1 3 4 5
15. 2 3 4 5
16. 0 1 2 6
17. 0 1 3 6
18. 0 2 3 6
19. 1 2 3 6
20. 0 1 4 6
21. 0 2 4 6
22. 1 2 4 6
23. 0 3 4 6
24. 1 3 4 6
25. 2 3 4 6
26. 0 1 5 6
27. 0 2 5 6
28. 1 2 5 6
29. 0 3 5 6
30. 1 3 5 6
31. 2 3 5 6
32. 0 4 5 6
33. 1 4 5 6
34. 2 4 5 6
35. 3 4 5 6
36. 0 1 2 7
37. 0 1 3 7
38. 0 2 3 7
39. 1 2 3 7
40. 0 1 4 7
41. 0 2 4 7
42. 1 2 4 7
43. 0 3 4 7
44. 1 3 4 7
45. 2 3 4 7
46. 0 1 5 7
47. 0 2 5 7
48. 1 2 5 7
49. 0 3 5 7
50. 1 3 5 7
51. 2 3 5 7
52. 0 4 5 7
53. 1 4 5 7
54. 2 4 5 7
55. 3 4 5 7
56. 0 1 6 7
57. 0 2 6 7
58. 1 2 6 7
59. 0 3 6 7
60. 1 3 6 7
61. 2 3 6 7
62. 0 4 6 7
63. 1 4 6 7
64. 2 4 6 7
65. 3 4 6 7
66. 0 5 6 7
67. 1 5 6 7
68. 2 5 6 7
69. 3 5 6 7
70. 4 5 6 7
71. 0 1 2 8
72. 0 1 3 8
73. 0 2 3 8
74. 1 2 3 8
75. 0 1 4 8
76. 0 2 4 8
77. 1 2 4 8
78. 0 3 4 8
79. 1 3 4 8
80. 2 3 4 8
81. 0 1 5 8
82. 0 2 5 8
83. 1 2 5 8
84. 0 3 5 8
85. 1 3 5 8
86. 2 3 5 8
87. 0 4 5 8
88. 1 4 5 8
89. 2 4 5 8
90. 3 4 5 8
91. 0 1 6 8
92. 0 2 6 8
93. 1 2 6 8
94. 0 3 6 8
95. 1 3 6 8
96. 2 3 6 8
97. 0 4 6 8
98. 1 4 6 8
99. 2 4 6 8
100. 3 4 6 8
101. 0 5 6 8
102. 1 5 6 8
103. 2 5 6 8
104. 3 5 6 8
105. 4 5 6 8
106. 0 1 7 8
107. 0 2 7 8
108. 1 2 7 8
109. 0 3 7 8
110. 1 3 7 8
111. 2 3 7 8
112. 0 4 7 8
113. 1 4 7 8
114. 2 4 7 8
115. 3 4 7 8
116. 0 5 7 8
117. 1 5 7 8
118. 2 5 7 8
119. 3 5 7 8
120. 4 5 7 8
121. 0 6 7 8
122. 1 6 7 8
123. 2 6 7 8
124. 3 6 7 8
125. 4 6 7 8
126. 5 6 7 8
127. 0 1 2 9
128. 0 1 3 9
129. 0 2 3 9
130. 1 2 3 9
131. 0 1 4 9
132. 0 2 4 9
133. 1 2 4 9
134. 0 3 4 9
135. 1 3 4 9
136. 2 3 4 9
137. 0 1 5 9
138. 0 2 5 9
139. 1 2 5 9
140. 0 3 5 9
141. 1 3 5 9
142. 2 3 5 9
143. 0 4 5 9
144. 1 4 5 9
145. 2 4 5 9
146. 3 4 5 9
147. 0 1 6 9
148. 0 2 6 9
149. 1 2 6 9
150. 0 3 6 9
151. 1 3 6 9
152. 2 3 6 9
153. 0 4 6 9
154. 1 4 6 9
155. 2 4 6 9
156. 3 4 6 9
157. 0 5 6 9
158. 1 5 6 9
159. 2 5 6 9
160. 3 5 6 9
161. 4 5 6 9
162. 0 1 7 9
163. 0 2 7 9
164. 1 2 7 9
165. 0 3 7 9
166. 1 3 7 9
167. 2 3 7 9
168. 0 4 7 9
169. 1 4 7 9
170. 2 4 7 9
171. 3 4 7 9
172. 0 5 7 9
173. 1 5 7 9
174. 2 5 7 9
175. 3 5 7 9
176. 4 5 7 9
177. 0 6 7 9
178. 1 6 7 9
179. 2 6 7 9
180. 3 6 7 9
181. 4 6 7 9
182. 5 6 7 9
183. 0 1 8 9
184. 0 2 8 9
185. 1 2 8 9
186. 0 3 8 9
187. 1 3 8 9
188. 2 3 8 9
189. 0 4 8 9
190. 1 4 8 9
191. 2 4 8 9
192. 3 4 8 9
193. 0 5 8 9
194. 1 5 8 9
195. 2 5 8 9
196. 3 5 8 9
197. 4 5 8 9
198. 0 6 8 9
199. 1 6 8 9
200. 2 6 8 9
201. 3 6 8 9
202. 4 6 8 9
203. 5 6 8 9
204. 0 7 8 9
205. 1 7 8 9
206. 2 7 8 9
207. 3 7 8 9
208. 4 7 8 9
209. 5 7 8 9
210. 6 7 8 9
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Avoiding the worksheet specific name in a macro 2
about comma style 2
Excel 2007 Nested formulas 2
Outlook message protection 3
xp embedded 2
Publishing calendar loses recurrence and reminders 3
How to sort data in Xl by adding a row at top 1
Old Help View 3

Top