# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Randbetween - not to repeat or duplicate

## itselflearn

a1=RANDBETWEEN(1,3)
a2=IF(A1=1,RANDBETWEEN(2,3),IF(A1=3,RANDBETWEEN(1,2),CHOOSE(RANDBETWEEN(1,2),1,3)))
a3=6-SUM(A1:A2)

the formula above will create random numbers between 1 and 3 but the numbers will not repeat. I would like to increase the numbers into 7 random numbers (from 1 to 7) but no repeating. Please help. Thank you.

example:
a1 = 3
a2 = 1
a3 = 5
a4 = 2
a5 = 4
a6 = 7
a7 = 6

if I click on refresh, numbers will change randomly but not repeating.

----------


## Jonmo1

Try like this

Fill A1:A7 with =RAND()
Then in B1 and filled down to B7 put
=RANK(A1,A$1:A$7)

----------


## Melvinrobb

In cells A1 insert this formula, and drag down: 

```
Please Login or Register  to view this content.
```


In cells B1 insert this formula, and drag down: 

```
Please Login or Register  to view this content.
```



You will have a unique list of 7 numbers in column B.

----------


## itselflearn

Jonmo1 - IT WORKS PERFECTLY!!!!! i understand the RAND part but can you please explained the RANK and why it is not repeating (if possible in plain english). Thank you  :Smilie:

----------


## itselflearn

does RANK only work in ROW because I tried to do it in COLUMN and it does not work?

----------


## Jonmo1

Rank simply ranking the 7 results of each Rand.
and Rand is EXTREMELY unlikely to produce a duplicate...and I mean EXTREMELY.
So if all 7 rand's are different numbers, then they have a rank, one is the largest #, another is the 2nd largest..etc..

----------


## Jonmo1

The example posted IS working on a Column...
A1:A7 = column
A1:G1 = row..


Now, if you wanted it to work accross a row, just adjust the refereces, and put th $ next to column letters instead of Row#s.

----------


## Jasonjroberts

I have 126 individuals that I must randomly rank.
I out them into a spread sheet and tried everything to rank them randomly without duplicates from 1-126.
Nothing is working. Help: :Confused:

----------


## arlu1201

Jasonjroberts,

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.

----------


## Rambo4711

> I have 126 individuals that I must randomly rank.
> I out them into a spread sheet and tried everything to rank them randomly without duplicates from 1-126.
> Nothing is working. Help



Hi, Jasonjroberts
Here is a formula I found in the book "Excel Gurus Gone Wild" and was originally created by the alias PGC01.

----------


## Edwin1971

To all who provided an answer.... GREAT, you solved my problems too
Edwin

----------


## Bullethead

One thing,  your list does random order, however, the last value on the list is always the largest number.  If the highest number is 7 then 7 is last on the list.  If 125 is the highest number then 125 is the last on the list.  I have tried this across multiple columns and get this result every time.

Anyway to change that?

----------

