Solved

# Excel Randbetween function without repeat

Posted on 2014-12-03
2,217 Views
is there any excel function that i can use, where in a range of cells it does not give me a repeated number.

for example in a range of 200 cells, i want to put a random number from 100 to 1000000 and i do not want repeated.

is this possible?
0
Question by:Flora
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40479056
Well, you can do this with a couple of functions, but given the range of numbers you want to generate, I don't recommend this as it involves both the RANDBETWEEN function (real-time recalculation with any worksheet change) and an array function.

But for the sake of discussion:
1) in cell A1: =RANDBETWEEN(100,1000000)
2) IN CELL A2:  =LARGE(ROW(\$100:\$1000000)*NOT(COUNTIF(\$A\$1:A1,ROW(\$100:\$1000000))),RANDBETWEEN(1,(1000000+2-101)-ROW(A1))) (entered as array function with [Ctrl]+[Shift]+[Enter]).
3) Copy cell A2 and paste into A3:A200.

WARNING! This will likely take several minutes to process!  I recommend trying with on just ten-twenty rows so you can see why...it recalculates all values each iteration.

-Glenn
0

LVL 27

Expert Comment

ID: 40479124
What you really want is a VBA method to do this:
``````Option Explicit
Sub Unique_Random()
Dim r As Integer
Dim lngLow, lngHigh, lngRand As Long
Dim rng As Range
Dim cl As Object

lngLow = 100
lngHigh = 1000000
Range("A1").Select
For r = 1 To 200
Do
lngRand = Application.WorksheetFunction.RandBetween(lngLow, lngHigh)
Set rng = Range("A1:A" & r)
Set cl = rng.Find(lngRand)
Loop While Not cl Is Nothing
Cells(r, 1).Value = lngRand
Next r
End Sub
``````

Runs incredibly fast and inserts values instead of functions.

Regards,
-Glenn
0

LVL 6

Author Closing Comment

ID: 40479699
thank you Glenn
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month8 days, 8 hours left to enroll