Random Numbering

[Guest]

I'm really having a tough time figuring this out. I appreciate the help I've got so far, but nothing seems to work. Would it help if I shortened my needs from 0000-0000-0000-0000 to 000-000-000? Now, all I need is a way to generate a each set of zeros into random numbers. I don't know if someone is willing to go as far as to actually create it for me, and e-mail be the file??? I tried everything I got so far, all I get is something in each field such as #NAME? What's that mean? Please help. Thanks!

 

[Frank Kabel]

Hi Bob
you received some formulas to do this
e.g.
=TEXT(RANDBETWEEN(0,9999),"0000") & "-" &
TEXT(RANDBETWEEN(0,9999),"0000") & "-" &
TEXT(RANDBETWEEN(0,9999),"0000") & "-" &
TEXT(RANDBETWEEN(0,9999),"0000")

If you receive the #NAME error you have to activate/install the
Analysis Toolpak Add-in. To do this goto 'Tools - add-in manager' and
check the Analysis toolpak addin. You may have to insert your Excel
installation CD

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag

I'm really having a tough time figuring this out. I appreciate the

help I've got so far, but nothing seems to work. Would it help if I
shortened my needs from 0000-0000-0000-0000 to 000-000-000? Now, all I
need is a way to generate a each set of zeros into random numbers. I
don't know if someone is willing to go as far as to actually create it
for me, and e-mail be the file??? I tried everything I got so far, all
I get is something in each field such as #NAME? What's that mean?
Please help. Thanks!

 

[Norman Harker]

Hi Bob!

Just in case you're having problems copying Frank's suggestion:

Select the entire formula from his post
Right click > Copy
Click on to the Excel icon on your start bar
Select the cell you want to put the formula in
Click on the formula bar
Right click > Paste
Enter

You'll find it works as described, although it won't necessarily
generate a valid credit card number.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
in message

 

[Guest]

Thanks so much guys. I got it

----- Norman Harker wrote: ----

Hi Bob

Just in case you're having problems copying Frank's suggestion

Select the entire formula from his pos
Right click > Cop
Click on to the Excel icon on your start ba
Select the cell you want to put the formula i
Click on the formula ba
Right click > Past
Ente

You'll find it works as described, although it won't necessaril
generate a valid credit card number

--
Regard
Norman Harker MVP (Excel
Sydney, Australi
(e-mail address removed).a
Excel and Word Function Lists (Classifications, Syntax and Arguments
available free to good homes
in messag

 

[Max]

Frank and Norman - thanks, guys!

Don't think it was a problem with the pasting of formulas
from the post as Bob got the first set of formulas given
working the first time round.

Below's the further suggestion given to Bob a couple of
days ago.

--------begin post---
From: Max ([email protected])
Subject: Re: Sequential Numbering
Newsgroups: microsoft.public.excel.misc
Date: 2004-03-26 17:08:31 PST

Try playing around with this set-up

Put in B1: =randbetween(0,999)
copy across to E1

Put in A1:

=TEXT($B$1+ROW(),"0000")&"-"&TEXT($C$1+ROW(),"0000")&"-
"&TEXT($D$1+ROW(),"00
00")&"-"&TEXT($E$1+ROW(),"0000")

Copy A1 down

Press F9 to re-generate a fresh set of random numbers in
B1:E1
The sequential numbering in col A will change accordingly
--
Replace what's in B1:E1 with your own set of random numbers

Adjust this part of the formula:

$B$1+ROW(), $C$1+ROW(), etc

depending on where your start row is,
and what is the number you want to begin the numbering with
--
Note: RANDBETWEEN requires the Analysis Toolpak be
installed and activated
-----

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik yahoocom
----
in message

Below is the formula you suggested Max. It worked.

However, do you know
how I can start the count with my own random numbers
rather then having 4
sets of the same numbers? You're assistance it great!

Try this in any cell in row1:

=TEXT(ROW(),"0000")&"-"&TEXT(ROW(),"0000")&"-"&TEXT(ROW
(),"0000")&"-"&TEXT(ROW(),"0000")

copy down

--------end of post--------

Bob:- I did suggest to you to:

Replace what's in B1:E1 with your own set of random

numbers

since you mentioned in your 2nd post ..

.. how I can start the count with my own random numbers

rather then having 4 sets of the same numbers?

Did you do this ?

 

[Guest]

Max,

Thanks for your help too. Initially, I thought I needed sequential numbering (that's what you gave me the formula to), then I realized I needed random numbering. The Add-In took care of all of my problems. I was under the impression it was installed already, it wasn't.

Thanks again
I really appreciate everybody's help

----- Max wrote: ----

Frank and Norman - thanks, guys

Don't think it was a problem with the pasting of formulas
from the post as Bob got the first set of formulas given
working the first time round.

Below's the further suggestion given to Bob a couple of
days ago.

--------begin post--
From: Max ([email protected]
Subject: Re: Sequential Numbering
Newsgroups: microsoft.public.excel.mis
Date: 2004-03-26 17:08:31 PST

Try playing around with this set-u

Put in B1: =randbetween(0,999
copy across to E

Put in A1

=TEXT($B$1+ROW(),"0000")&"-"&TEXT($C$1+ROW(),"0000")&"
"&TEXT($D$1+ROW(),"0
00")&"-"&TEXT($E$1+ROW(),"0000"

Copy A1 dow

Press F9 to re-generate a fresh set of random numbers in
B1:E
The sequential numbering in col A will change accordingl
-
Replace what's in B1:E1 with your own set of random number

Adjust this part of the formula

$B$1+ROW(), $C$1+ROW(), et

depending on where your start row is
and what is the number you want to begin the numbering wit
-
Note: RANDBETWEEN requires the Analysis Toolpak be
installed and activate
----

-
Rgd
Ma
xl 9
--
Please respond, in newsgrou
xdemechanik yahooco
---
in messag

Below is the formula you suggested Max. It worked.

However, do you kno
how I can start the count with my own random numbers
rather then having
sets of the same numbers? You're assistance it great--------end of post-------

Bob:- I did suggest to you to

Replace what's in B1:E1 with your own set of random

number

since you mentioned in your 2nd post ..

.. how I can start the count with my own random numbers

rather then having 4 sets of the same numbers?

Did you do this

-
Rgd
Ma
xl 9

 

[Max]

Glad to hear that, Bob !

Apologies for presuming that you knew what to do about the
caveat below which was a detail given in my 2nd reply to
your original post, viz.:

 

[Max]

Ahh, that's the beauty of dynamic specifications <bg>

You're welcome, Bob !

Great to know that you've got everything
you wanted .. and more

Thanks for the feedback

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

Max,

Thanks for your help too. Initially, I thought I needed

sequential numbering (that's what you gave me the formula
to), then I realized I needed random numbering. The Add-In
took care of all of my problems. I was under the
impression it was installed already, it wasn't.

 

[Max]

That's the nature of RANDBETWEEN, a volatile function.

You usually have to "freeze" the values in the formula cells via copying
either the entire sheet or a specific range thereof, and then pasting
special as "Values" either in-place or elsewhere.

For example, let's say you want to freeze Sheet1 (entire sheet)

Select Sheet1
Press Ctrl + A (to select the entire sheet)
Click Copy

Right-click on A1 in an empty sheet, say Sheet2
Select Paste special > check Values > OK
[Right-click again to paste special > check Formats > OK]

If you need to do the above often,
maybe try the sub MakeStaticCopy() in:

http://tinyurl.com/286kt

--

.. However, every time I close and then reopen Excel,
numbers I've created change ..

Try changing the calculation mode to "Manual"
(default mode is Automatic)

Click Tools > Options > Calculation tab

Check "Manual" > OK

Save the book with this mode

But note that the calculation mode of each Excel session is dictated by the
mode of the first book opened within the session

If the first book's calc mode is Automatic, then the mode for all subsequent
books opened in the same Excel session (i.e. until / unless Excel is exited)
will be Automatic, irregardless of their earlier saved modes.

--
Rgds
Max
xl 97
---
Please respond, in newsgroupb
xdemechanik yahoocom
----
in message

Max,

Here's another one for ya!

I got everything created as I needed. Great! However, every time I close

and then reopen Excel, the numbers I've created change. New numbers are
generated every time, even without intentionally making changes. Do you know
how to stop that from happening?