RandBetween(2,218) to generate 217 UNIQUE integers in 217 cells

[mclau152]

HELLO FORUMS,

I need to make 217 RANDOM integers in 217 CELLS that are UNIQUE (no duplicates)

NOW when I use this formula (=randbetween(2,218)) in my first cell and drag down I get many duplicates (found using conditional formatting)

HOW do I make these random integers WITHOUT getting duplicates

THANK YOU FORUMS

 

[Kyle123]

How about just putting the numbers in ascending, random numbers in the next column and sort on this.

 

[AlanY]

=rand() in A1
=RANK(A1,$A$1:$A$217)+1 in B1

drag them all the way down to Row 217.

 

[Jonmo1]

Getting unique random numbers from Randbetween is very difficult to say the least.

Here's an alternate method using RAND(), this will be unique.

Manually enter your numbers from 2 to 218 in your range of cells (you can just type 2 in the first cell, drag down and use Fill Series)
Then in the adjescent column put
=RAND()

Sort by the column with RAND() in it

Here's an example with just 10 numbers

Unknown
	A	B
1	6	0.94254
2	3	0.357699
3	10	0.201815
4	9	0.21588
5	8	0.730505
6	5	0.44915
7	7	0.842566
8	2	0.065477
9	1	0.290075
10	4	0.389441
Sheet1

Cell Formulas
Range	Formula
B1	=RAND()
B2	=RAND()
B3	=RAND()
B4	=RAND()
B5	=RAND()
B6	=RAND()
B7	=RAND()
B8	=RAND()
B9	=RAND()
B10	=RAND()

 

[mclau152]

HELLO AlanY,

this has worked so far, I have a list of random numbers now and conditional formatting isnt showing any duplicates

I will update if issues arise

THANK YOU

 

[AlanY]

you're welcome