Generate random numbers with no repeats

**Paul Sheppard** · 04-03-2007, 06:17 PM

I want to produce a randomly generated competition draw,

eg for 128 teams, numbered 1 to 128, what I need is a formula for cells A1 to B64, that will produce a number between 1 and 128 in each of the cells, with no repeats

So that the team in A1 will play B1 etc

This needs to be dynamic as the number of teams for any given competition may vary

Thanks in advance for any help

I have looked at rand and randbetween without success

**daddylonglegs** · 04-03-2007, 07:05 PM

Here's one possible approach.

In E1 put the formula =RAND() and copy this down as far as you need to cover the maximum number of teams you might have, e.g. to E1000.

In D1 enter the number of teams, e.g. 128

In A1 use this formula

=IF(ROW()-ROW(A$1)+1>$D$1/2,"",RANK(OFFSET($E$1,ROW()-ROW(A$1)+(COLUMN()-COLUMN($A1))*($D$1/2),),$E$1:INDEX($E$1:$E$1000,$D$1)))

copy across to B1 and down as far as you might need.

Assuming you have 128 in D1 then A1:B64 will now include one each of numbers 1 to 128. Subsequent cells will be blank. Change D1 to any even number to regenerate numbers. Note: recalculation of worksheet will re-generate random numbers, hide column E if you wish

**Paul Sheppard** · 04-04-2007, 04:24 AM

Originally Posted by daddylonglegs

Here's one possible approach.

In E1 put the formula =RAND() and copy this down as far as you need to cover the maximum number of teams you might have, e.g. to E1000.

In D1 enter the number of teams, e.g. 128

In A1 use this formula

=IF(ROW()-ROW(A$1)+1>$D$1/2,"",RANK(OFFSET($E$1,ROW()-ROW(A$1)+(COLUMN()-COLUMN($A1))*($D$1/2),),$E$1:INDEX($E$1:$E$1000,$D$1)))

copy across to B1 and down as far as you might need.

Assuming you have 128 in D1 then A1:B64 will now include one each of numbers 1 to 128. Subsequent cells will be blank. Change D1 to any even number to regenerate numbers. Note: recalculation of worksheet will re-generate random numbers, hide column E if you wish

Thanks daddylonglegs, that's just what I needed

**Lista** · 04-04-2007, 04:40 AM

For future reference (and I'm talking about VBA), Rand() function does give random numbers, but starts from the same position. To avoid this, use Randomize before the Rand() function itself.

I wrote this because this is the first thing that occured to me when I saw the title, and I suppose I won't be the only one.

Cheers,
Ivan.