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

mclau152

New Member
Joined
Jul 7, 2014
Messages
28
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
How about just putting the numbers in ascending, random numbers in the next column and sort on this.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
=rand() in A1
=RANK(A1,$A$1:$A$217)+1 in B1

drag them all the way down to Row 217.
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

<b>Unknown</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">6</td><td style="text-align: right;;">0.94254</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.357699</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0.201815</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0.21588</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0.730505</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0.44915</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0.842566</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.065477</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.290075</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0.389441</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B7</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=RAND(<font color="#0000FF"></font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

mclau152

New Member
Joined
Jul 7, 2014
Messages
28
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

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top