I need an Excel spreadheet template that can randomize in a
non-repeating pattern, a sequence of numbers (example, 1 through 30).

Hello,

There are a couple of solutions available, if you're willing to pay or
roll up your sleeves a little bit.  You don't mention which platform
you're using (Mac or Windows) or which version, but these solutions
should work for most recent versions of Excel, which have had random
number analysis tools for several years.  My understanding from your
question was that you wanted (in your example) 30 numbers in random
order.

For $24.95, you can use Macro Systems' Random Number Generator tool,
(http://www.add-ins.com/random.htm).  There isn't much in the way of
specifications for the macro, but it should work with Windows versions
of Excel.  My research indicates that this is definitely the easiest
option.


If you are unwilling to pay, and have a little bit of numerical
understanding, you can use the built-in Excel ToolPak (under the
Tools) menu.  It's not automatically installed with Excel, so you can
use the instructions (found, for example, at the bottom of this page:
http://www.vuse.vanderbilt.edu:8888/es130/lectures/lecture7/spreadnew.html
)

A good explanation of how to use Excel's Random Number Generator can
be found here:
http://www.vuse.vanderbilt.edu:8888/es130/lectures/lecture7/random.html

Perhaps a caveat is in order: this is not as simple or
straight-forward as it should be.  If you aren't familiar with
statistics, I would avoid this.

If you just need the list of numbers, perhaps a web site can help. 
http://www.pangloss.com/seidel/rnumber.cgi will generate the numbers
for you in any way you like, including tab and comma-delimited format
that you can then import into Excel.  And it's free.


While this may not be precisely what you are looking for, you might
find it useful at some point.  There is a random sequence generator
that will create a string of digits of any length, but they are digits
not decimal numbers.  As a result, they will not be unique.  You can
find this excel macro at
http://members.lycos.nl/excelsoftware/Files/excel_files.htm

Hope this helps you.

Search terms used:

Excel Random Generator
Google: ://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&safe=off&q=Excel+Random+Generator
Teoma: http://www.teoma.com/search.asp?t=Excel+Random+Generator&qcat=1&qsrc=1
Altavista: http://www.altavista.com/sites/search/web?q=Excel+Random+Generator&pg=q&avkw=tgz&kl=XX

Excel Random Number spreadsheet
://www.google.com/search?hl=en&ie=ISO-8859-1&q=Excel+Random+Number+Spreadsheet

Excel Random Number template
://www.google.com/search?hl=en&ie=ISO-8859-1&q=Excel+Random+Number+Template


Ask.com
Create a Random Sequence with Excel (found no results)
http://www.ask.com/main/askjeeves.asp?ask=Create+a+Random+sequence+with+Excel&o=0

Teoma.com
Create a Random Sequence with Excel
http://www.teoma.com/search.asp?t=Create+a+random+sequence+with+Excel&qcat=1&qsrc=0

Regards,
drjmetz-ga

---

Request for Answer Clarification by quadrex-ga on 24 Jun 2002 19:45 PDT

What I needed was for the generation of a sequence of NON-REPEATING
random numbers in Excel...the link you gave to www.pangloss.com was
the closest example of what I was looking for however it was not in an
Excel format...the answers given by excel-wiz and enhanced by dsico
did the trick.  Thanks for your research - this one had me stumped for
hours.

D

---

Clarification of Answer by drjmetz-ga on 25 Jun 2002 05:48 PDT

You're welcome.  Hopefully you managed to find some usefulness there. 
You are correct, those answers provided in the comments are excellent.

In Col A put your number sequence (e.g., 1-30).
In B1, type =rand().
Drag the formula in B1 to the last row of numbers.
Turn calculation to manual (Tools>Options>Calculation).

Now, each time you hit F9, the random numbers will recalc.
After the recalc, simply sort both A and B on Col. B.

Hope this helps.

Without specifics as to where you want these numbers the following
macro puts random numbers down the the first column.

Sub Random()
  
  Randomize (Time)
   Max = 30: Min = 1
   For Count = 1 To 100
   Application.Goto Reference:="R" + Trim(Str(Count)) + "C1"
   ActiveCell.FormulaR1C1 = Trim(Str(Int((Max - Min + 1) * Rnd +
Min)))
   Next Count
   
End Sub

The Max & Min determine the bounds for the random number. In your case
stated as 1-30.
The count is how many random numbers you want down the first column.
This macro just needs to be cut and pasted into your
Tools\Macro\Macros  area. If you want it as I template so that it runs
when you create a new workbook of the required type then let me know &
I'll email it to you.

Regards

excelwiz-ga has the simplest method.
the Rand() function is clearly the easiest. 

However if you want to avoid using Sort, there is a way to do it with formula.

with the setup mentioned simply add in column C the formula 

=SUMIF($B$1:$B$30, SMALL($B$1:$B$30,A1),$A$1:A$30)

this will produce the same sorted results as the method mentioned by excelwiz-ga.

dsico