Number Generation Question

December 12, 2008 10:06 AM

I need to generate the complete series of 5 digit numbers. The catch? Can only use even digits (0,2,4,6,8). I'd like to accomplish this in Excel, if possible.
posted by Rock Steady to Computers & Internet (28 answers total)

Trying to make sure I understand. So you want:
00000
00002
00004
.
.
.
20000
20002
20004
.
.
.
88888

Right?
posted by inigo2 at 10:13 AM on December 12, 2008

Correct me if I'm wrong, but you want the even numbers from 20000 to 88888? Or from 00000 to 88888?
posted by jedicus at 10:13 AM on December 12, 2008

Response by poster: inigo2 is right. 46802 is a good result, 46812 is not.
posted by Rock Steady at 10:16 AM on December 12, 2008

That doesn't sound right, because that includes numbers such as 30000 which use an odd digit. It sounds like the OP wants
00000
00002
00004
00006
00008
00020
00022
...
00028
00040
...

skipping any numbers that have any odd digits at all.
posted by likedoomsday at 10:17 AM on December 12, 2008

Response by poster: To clarify the leading zero issue, 00802 is good, 00812 bad.
posted by Rock Steady at 10:18 AM on December 12, 2008

You could generate the series of even numbers between 0 and 88888, then in another column use text functions to determine if any of the digits is odd. Filter on that second column, and you've got your series. You can copy and paste the filtered list into a new sheet if you want the list clean like that.
posted by Dec One at 10:22 AM on December 12, 2008

Well, the quick and easy way is a loop that adds 2 to the iterator, checks for the presence of any odd digits, and then either enters the number (padded with leading zeroes as necessary) or continues the loop.

A lot of improper numbers will be generated and rejected, but it's simpler than constructing a series of nested loops that generates only the correct numbers.
posted by jedicus at 10:24 AM on December 12, 2008

So:

set y to 1
repeat 88888 times
set y to y+1
if y contains any of "13579"
then repeat else
print y
print return
repeat

applescript n00b
posted by Aquaman at 10:24 AM on December 12, 2008

It's not the easiest way, but this will work.

1. Enter the numbers 0,2,4,6,8,10 into a column and then select all five numbers.

2. Click the square at the lower-right hand corner of the selection and drag it down. Excel will follow the pattern and fill in all the even numbers. Hold down the mouse button for a long time (took me about a minute) until you get to six digit numbers.

3. You now have a list of all the even numbers between 0 and 99,998. To get rid of the numbers that contain 1,3,5,7 or 9, do the following for each number:
a) Go to Find and Replace and go to the Replace tab.
b) Enter *1* into the "Find what" textbox and leave the "Replace with" textbox blank.
c) Click on the Options button and check the "Match entire cell contents" checkbox.
d) Click Replace All.
e) Repeat steps a through d for 3,5,7 and 9.

4. Select the column the numbers are in and go to Data > Sort. Click OK. That will get rid of the empty spaces in the list.

5. Select the column again and go to Format > Cells.

6. Go to the Number tab and select Custom for the Category. Type in 00000 into the Type field. Click OK. (this will add the leading zeroes).

7. Profit.
posted by hootch at 10:29 AM on December 12, 2008

take all the numbers from 00000 to 44444, base 5. Transliterate those (make text, then make numbers from the text) to base ten. Then double every number.
posted by notsnot at 10:30 AM on December 12, 2008

or a series of nested repeats-
n = 0
repeat 5{
repeat 5 {
repeat 5 {
repeat 5 {
repeat 5 { print n; n+2 }
n+20 }
n+200 }
n+2000 }
n+20000 }
posted by noloveforned at 10:35 AM on December 12, 2008

shoot, those should actually be +2, +10, +100, +1000 and +10000... stupid math!
posted by noloveforned at 10:36 AM on December 12, 2008

echo {0,2,4,6,8}{0,2,4,6,8}{0,2,4,6,8}{0,2,4,6,8}{0,2,4,6,8}
posted by nicwolff at 10:38 AM on December 12, 2008

Best answer: What's the problem? Do you want a xls (or csv) that contains them, or do you want the function to generate them?

To generate, you can use seq and grep : seq -w 00000 99999 | grep '[02468][02468][02468][02468][02468]'

http://www.sharefile.org/showfile-549/5digits.txt has a copy of the output that you can import into excel if you wish.
posted by devbrain at 10:39 AM on December 12, 2008 [1 favorite]

Or, if you want a comma-delimited list for easy import into Excel:

echo {0,2,4,6,8}{0,2,4,6,8}{0,2,4,6,8}{0,2,4,6,8}{0,2,4,6,8},
posted by nicwolff at 10:40 AM on December 12, 2008 [2 favorites]

Response by poster: Thanks everyone! Your file did the trick, devbrain. I dig the procedural efforts, but in this case, "do it for me" is just as good as "teach me".
posted by Rock Steady at 10:47 AM on December 12, 2008

Got it for Excel:

1. Select the whole first column and change formatting to text.
2. In a new sheet, put the following in the first few rows of the first column:

00000
00002
00004

3. Select the whole first column again, and do AutoFill Series. (In 2007, it's on the Home Tab under Editing.) This will fill the whole column with even numbers, some higher than 88888, but no worries. Excel overflows before 20,000 comes up.

4. Select the whole first column, and do Conditional Formatting. Tell it to highlight cells containing the text 1, 3, 5, 7, or 9 and color them red. I had to apply each rule separately for each number.

5. With the first column selected, filter out all of the red text cells. In 2007, I had to click Filter in the Data tab. This brought up the Filter button on the first cell "00000", which you can click and say "Filter By Color" and select "Automatic" (Black).

All the numbers you want will be in the first column, and you can copy them to a new sheet if needed. Let me know if any of these steps don't make sense.
posted by ALongDecember at 10:49 AM on December 12, 2008

I emailed you my efforts, Rock Steady, although you have an answer already :).
posted by MadamM at 10:52 AM on December 12, 2008 [1 favorite]

I did it with formulas, which might be what you want. It was a fun little challenge.

=MOD(ROW()-1,5)*2+ROUNDDOWN((ROW()-1)/5,0)*20+ROUNDDOWN((ROW()-1)/10,0)*200+ROUNDDOWN((ROW()-1)/15,0)*2000+ROUNDDOWN((ROW()-1)/20,0)*20000

You can use excel formatting to get the leading zeroes back. You can also decompose the function to get each digit if that's what you want (it's how I built the formula). Just break it up at the '+'s

You can also replace ROW() with whatever index you want, as long as it's a n=n+1 kinda thing.
posted by teabag at 10:54 AM on December 12, 2008

Ack, noticed a bug in my formula, lemme see if I can fix it...
posted by teabag at 10:57 AM on December 12, 2008

Since Excel doesn't ddo funny bases (at least my vanilla version doesn't) I've made a spreadsheet that figures out the digits using modulo arithmetic. Mefi mail me and I'll send you the spreadsheet.
posted by notsnot at 11:01 AM on December 12, 2008

Fixed, forgot the moduli and my intervals were wrong...

=MOD(ROW()-1,5)*2+MOD(ROUNDDOWN((ROW()-1)/5,0)*2,10)*10+MOD(ROUNDDOWN((ROW()-1)/25,0)*2,10)*100+MOD(ROUNDDOWN((ROW()-1)/125,0)*2,10)*1000+MOD(ROUNDDOWN((ROW()-1)/625,0)*2,10)*10000

The divisor in the ROUNDDOWN section is 5* the previous divisor, if you're wanting more digits.
posted by teabag at 11:04 AM on December 12, 2008 [1 favorite]

..and since the answer has already been sent to the asker, I'll put in my formulae:

A1=1, A2=A1+1, on down to 3124.
B1=MOD(A2-125*C2-25*D2-5*E2-F2,3125)/625
C1=MOD(A2-25*D2-5*E2-F2,625)/125
D1=MOD(A2-5*E2-F2,125)/25
E1=MOD($A2-F2,25)/5
F1=MOD($A2,5)
G1=2*(VALUE(CONCATENATE(B2,C2,D2,E2,F2)))

Change the formatting on g1 to "zip code" to display all five numbers.

...and that, folks, is how you crunch out weird bases manually.
posted by notsnot at 11:07 AM on December 12, 2008

Windows cmd shell abuse:

for %a in (0 2 4 6 8) do @for %b in (0 2 4 6 8) do @for %c in (0 2 4 6 8) do @for %d in (0 2 4 6 8) do @for %e in (0 2 4 6 8) do @echo %a%b%c%d%e
posted by milnak at 2:22 PM on December 12, 2008

In Excel, with semi-recursion (start in A2, then C&P to row 3,125):

=IF(MOD(FLOOR((A1+2)/10,1),2)=0,A1+2,IF(MOD(FLOOR((A1+12)/100,1),2)=0,A1+12,IF(MOD(FLOOR((A1+112)/1000,1),2)=0,A1+112,IF(MOD(FLOOR((A1+1112)/10000,1),2)=0,A1+1112,IF(MOD(FLOOR((A1+11112)/100000,1),2)=0,A1+11112,"Number too high")))))

In JavaScript:

<script>
for (i=0; i<9>    if (i.toString().search(/.*[13579]/) != 0)
      document.write(i+'<br/>');
</script>

Since you got the answer (the method and the series), can we ask why you are doing this?
posted by ostranenie at 2:30 PM on December 12, 2008

That should actually be:

for (i=0; i<99999; i++)
    if (i.toString().search(/.*[13579]/) != 0)
        document.write(i+'<br/>');
posted by ostranenie at 2:32 PM on December 12, 2008

Response by poster: can we ask why you are doing this?

I need to generate passcodes for an electronic door lock. The keypad only has 5 buttons (1|2, 3|4, etc) so I need to stick to even numbers so I don't accidentally give people identical codes (24680 and 24580 are essentially the same number in this system). I wanted the whole series so I can cross out numbers that are used and never re-use them. Thanks again everyone!
posted by Rock Steady at 6:54 AM on December 13, 2008

In theory that's good, but are people really going to be trying others' codes on doors that aren't theirs? Is there a lockout time if the wrong code is input 'n' times in a row? Or an alert system that tells you when the wrong code is entered? Can users change their own codes or do you have to do it? Is the code database centrally or locally (at the keypad) managed? If it's centrally managed can you change the code remotely in the case of an emergency? If not, is there a master security code only you know? And so on and so on and so on.

Sorry...I just worry.
posted by ostranenie at 9:54 AM on December 31, 2008