http://www.ozgrid.com/VBA/RandomNumbers.htm
Great example here for a UserDefinedFunction
I've created a range from G2-J5, 16 cells total to generate random numbers between 1 & 54 using "=RANDBETWEEN(1,54)". The problem is I'm getting duplicate numbers within the 16 cells. How can I modify the formula to eliminate duplicate numbers?
Thanks, Tim
http://www.ozgrid.com/VBA/RandomNumbers.htm
Great example here for a UserDefinedFunction
Thanks for the link. The function works but it generates 16 unique numbers in one cell. I need 16 unique numbers, one number in each cell. I've also noticed when using this function, I'm not able to reference the results with VLOOKUP to generate a text output which I could do when using "=RANDBETWEEN(1,54)".
Thanks, Tim
Hi,
Here's a udf that should be pretty good substitute for the RandBetween(). It'll return an error if a random number can't be generated. I'd be wary of trying to generate a really big data set with it.
Example of use:Code:Public Function randBetweenExcludeRange(lngBottom As Long, lngTop As Long, _ rngExcludeValues As Range) As Variant Dim c As Range Dim dict As Object Dim i As Long Dim blNoItemsAvailable As Boolean Dim lngTest As Long 'some notes on code: 'It'd probably be a good idea to check for values that are only integers in the range 'you might be able to sort the already excluded values, choose a number between 1 and 'the number of remaining available values and then generate that from a full list of 'values. (maybe by making the dictionary hold available values only?) 'I'm pretty sure the comment above doesn't make a lot of sense. If it 'did, i'd have tried to implement it. If lngBottom > lngTop Then randBetweenExcludeRange = CVErr(xlErrNA) Exit Function End If 'get a list of all items in range 'i = 0 Set dict = CreateObject("Scripting.dictionary") For Each c In rngExcludeValues 'I should have really only checked for c.values that are longs. If IsNumeric(c.Value) Then If c.Value >= lngBottom And c.Value <= lngTop Then If Not dict.exists(c.Value) Then dict.Add c.Value, "" End If End If End If Next c 'check to make sure that there are values available to use If dict.Count >= lngTop - lngBottom + 1 Then 'initialize error holder to true blNoItemsAvailable = True For i = lngBottom To lngTop If Not dict.exists(i) Then blNoItemsAvailable = False Exit For End If Next i End If If blNoItemsAvailable Then randBetweenExcludeRange = CVErr(xlErrNA) Exit Function End If 'this bit could (probably) be made a lot more efficient. see notes at top 'of code Do lngTest = Int(Rnd() * (lngTop - lngBottom + 1)) + lngBottom If Not dict.exists(lngTest) Then randBetweenExcludeRange = lngTest Exit Function End If Loop End Function
Sheet1Excel 2007
A 1 Test 2 6 3 3 4 4 5 8 6 1 7 9 8 5 9 10 10 7 11 2 12 #N/A 13 #N/A
Worksheet Formulas
Cell Formula A2 =randBetweenExcludeRange(1,10,A$1:A1) A3 =randBetweenExcludeRange(1,10,A$1:A2) A4 =randBetweenExcludeRange(1,10,A$1:A3) A5 =randBetweenExcludeRange(1,10,A$1:A4) A6 =randBetweenExcludeRange(1,10,A$1:A5) A7 =randBetweenExcludeRange(1,10,A$1:A6) A8 =randBetweenExcludeRange(1,10,A$1:A7) A9 =randBetweenExcludeRange(1,10,A$1:A8) A10 =randBetweenExcludeRange(1,10,A$1:A9) A11 =randBetweenExcludeRange(1,10,A$1:A10) A12 =randBetweenExcludeRange(1,10,A$1:A11) A13 =randBetweenExcludeRange(1,10,A$1:A12)
EDIT: I just saw that you're putting these items into a square array instead of a single column or row. You'll need to mess around a little bit with index() or indirect() functions to get it to work right. Sorry, I'm heading out for the weekend now.
Last edited by ChrisOswald; Aug 5th, 2011 at 07:52 PM. Reason: Read the whole of the original post:
Using Office 2007
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Signature Shamelessly Swiped From Sticky
Hi,
Maybe this ...Can do it as a UDF if you like.Code:Sub generateuniquerandom() Dim b() As Boolean, e As Range, k&, x& ReDim b(1 To 54) For Each e In Range("G2:J5") Do x = Int(Rnd() * 54) + 1 If b(x) = False Then e.Value = x b(x) = True Exit Do End If k = k + 1: If k > 100 Then Exit Sub Loop Next End Sub
Chris...
Thanks so much. I was able to tweak it a little bit & it works great!
Thanks, Tim
Col A insert numbers 1 to 54
Col B cell 1 insert =RAND() copy down to B54
Col C cell 1 insert =INDEX(A:A,MATCH(SMALL(B:B,ROWS($1:1)),B:B,0))
Copy down to cell C16
As a check for duplication in col D cell 1 =IF(COUNTIF(C:C,C1)>1,"Duplicate","") copy down to D16
There is a large pool of numbers between 0 and 1 to practically ensure the absence of duplicate random numbers in such a small sample.
Echo the results in C1 to C16 into your selected range G2 to J5
Might want to turn off Autocalculate as well to stop the numbers from changing all the time.
Last edited by Just Jan; Aug 7th, 2011 at 05:37 AM.
Hi friend
You can download "morefunc" and use "mrand" function.
Select G2:J5
Active cell in G2
=mrand(16,1,54)
Ctrl Shift Enter
Hope your feedback
Sheet23Excel 2010
G H I J 2 16 3 39 8 3 35 38 12 19 4 51 30 31 11 5 7 48 40 50
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
Cell Formula G2:J5 =MRAND(16,1,54,TRUE)
Here is the link:
http://www.youtube.com/user/ExcelIsF.../0/TAofPm4LCRc
Try putting numbers 1 thru 54 in Column A, then enter =RAND() in B1, and drag down to B54. Then sort columns A and B by Column B, and take the first 16 numbers in Column A. They will now be 16 randomly chosen numbers between 1 and 54.
Like this thread? Share it with others