Generate Random Numbers Between 0-9

**dreicer_Jarr** · 02-01-2009, 03:05 PM

I am trying to generate random numbers between 0 - 9 in ten cells that do not repeat an always include every value 0 - 9 (0, 1, 2, 3, 4,...9). The object is to have the numbers randomize each time. So cells A1:A10 could look like this:

3
9
4
5
1
0
6
8
7
2

Or, they could look like this:

9
7
5
2
8
4
1
3
6
0

I understand the rand and randbetween formulas, but am having trouble finding the logic to make them non-repeating, all inclusive and still random.

Any help? A bonus point for anyone who can figure out what this is for (hint: there is a kick-off in about four hours).

**Richard Buttrey** · 02-01-2009, 03:42 PM

Hi,

Probably the easiest way is to list your numbers 0-9 in one column and alongside each of them in another column put the formula:

Please Login or Register  to view this content.

Now just sort the two columns using the random number as the key.

You could achieve the same sort of thing with a macro if you didn't want to see the 'workings'

HTH

**shg** · 02-01-2009, 03:46 PM

I would use RAND rather than RANDBETWEEN in the sort key column.

**VBA Noob** · 02-01-2009, 03:49 PM

dreicer_Jarr,

FYI

Richard forget to mention you need to install the Analysis ToolPak add-in before you can use Randbewteen.

Edit

Unless you have xl07

VBA Noob

**daddylonglegs** · 02-01-2009, 03:58 PM

I presume this is related to something called "The Superbowl", whatever that is.......

This is one way to achieve what you want without helper cells.....

In A1

=INT(RAND()*10)

in A2

=SMALL(IF(ISNA(MATCH({0,1,2,3,4,5,6,7,8,9},A$1:A1,0)),{0,1,2,3,4,5,6,7,8,9}),INT(RAND()*(10-ROWS(A$2:A2))+1))

confirmed with CTRL+SHIFT+ENTER and copied down to A10

**splatz** · 11-29-2012, 06:02 PM

Originally Posted by daddylonglegs

I presume this is related to something called "The Superbowl", whatever that is.......

This is one way to achieve what you want without helper cells.....

In A1

=INT(RAND()*10)

in A2

=SMALL(IF(ISNA(MATCH({0,1,2,3,4,5,6,7,8,9},A$1:A1,0)),{0,1,2,3,4,5,6,7,8,9}),INT(RAND()*(10-ROWS(A$2:A2))+1))

confirmed with CTRL+SHIFT+ENTER and copied down to A10

I was able to use this formula for the rows going down the side, but when I tried to change it for the columns going across the top, numbers kept repeating. Any suggestions?

**daddylonglegs** · 11-29-2012, 07:33 PM

The ROWS part increments when you copy down, if you want to copy across you need COLUMNS and the $ signs get adjusted, so this version in B1 copied across

=SMALL(IF(ISNA(MATCH({0,1,2,3,4,5,6,7,8,9},$A1:A1,0)),{0,1,2,3,4,5,6,7,8,9}),INT(RAND()*(10-COLUMNS($B1:B1))+1))

**splatz** · 11-29-2012, 08:16 PM

I used =SMALL(IF(ISNA(MATCH({0,1,2,3,4,5,6,7,8,9},A$2:A2,0)),{0,1,2,3,4,5,6,7,8,9}),INT(RAND()*(10-ROWS(A$3:A3))+1)) because A1 isn't assigned for either the ROWS or COLUMNS.

I used =SMALL(IF(ISNA(MATCH({0,1,2,3,4,5,6,7,8,9},B$1:B1,0)),{0,1,2,3,4,5,6,7,8,9}),INT(RAND()*(10-COLUMNS(C$1:C1))+1)), but it keeps giving me repeated numbers. Also, I used =INT(RAND()*10) for both A2 and B1. Did I enter something wrong?

**daddylonglegs** · 11-30-2012, 06:08 AM

You didn't switch the $ symbols - when copying down you have B$1:B1 but for copying across it's $B1:B1 so in C1 you need this formula

=SMALL(IF(ISNA(MATCH({0,1,2,3,4,5,6,7,8,9},$B1:B1,0)),{0,1,2,3,4,5,6,7,8,9}),INT(RAND()*(10-COLUMNS($C1:C1))+1))

confirm with CTRL+SHIFT+ENTER and copy across

**splatz** · 11-30-2012, 11:54 AM

Thank you! I would have never caught that.

**Dan Small** · 01-16-2014, 05:56 PM

This thread has been extremely helpful. I just have one question about generating random numbers going down the rows and not across the columns. Specifically I need to generate a random non-repeating number from 1-10 that will be paired with numbers 1-10.

For example:
A B
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10

Each number in Column A is a fixed location (in my case nest a bird fledged from), column B is the location of it's first adult nest one year later. What I would like to do is repeat the numbers is column A 1-10, 1-10 (no problem) and so on, but each time generate a random non-repeating number in column B for each set of ten numbers in column A.

In the example above, I could do this by doing a lot of copying and pasting so I'm wondering if anyone knows a way to do it like I need.

Thanks, Dan

**arlu1201** · 01-17-2014, 01:00 AM

Dan Small,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Generate Random Numbers Between 0-9

LinkBack

Thread Tools

Rate This Thread

Display

Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Re: Generate Random Numbers Between 0-9

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions