Unique Random Number In Ascending Order

[marreco]

Hi.
I need to generate repeat NOT REPEATED numbers and arrange the numbers in ascending order. If anyone can fix I would appreciate it.


Sub RndNumb()
With Range("A2:A9")
.Formula = "=INT(99*RAND()+1)"
.Value = .Value
End With
End Sub

[Excel Fox]

Here's one way to do it....



Sub GenerateRandomUnique()

Dim lng As Long
Dim var As Variant
With CreateObject("Scripting.Dictionary")
Do While .Count <= Range("A2:A9").Cells.Count
lng = Rnd * 99 + 1
.Item(lng) = Empty
Loop
var = Application.Transpose(.Keys)
SortIntegerArray var
Range("A2:A9").Value = var
End With

End Sub
Sub SortIntegerArray(ByRef paintArray As Variant)
Dim lngX As Long
Dim lngY As Long
Dim intTemp

For lngX = LBound(paintArray) To (UBound(paintArray) - 1)
For lngY = LBound(paintArray) To (UBound(paintArray) - 1)
If Val(paintArray(lngY, 1)) > Val(paintArray(lngY + 1, 1)) Then
'exchange the items
intTemp = paintArray(lngY, 1)
paintArray(lngY, 1) = paintArray(lngY + 1, 1)
paintArray(lngY + 1, 1) = intTemp
End If
Next
Next
End Sub

[marreco]

Hi.

Great solution!!!

Thank you very mutch!!!

[marreco]

Hi.
how do I use this code in the range "A2: I2"?
As I mark this post as solved?

[Rajan_Verma]

you should like to see the discussion here on this topic

Get Random List : « excelpoweruser (http://excelpoweruser.wordpress.com/2012/10/15/get-random-list/)
Get Random List : | LinkedIn (http://www.linkedin.com/groups/Get-Random-List-3843467.S.175282658?qid=5b136646-46ff-4cdc-9f83-d3f0c9eef66b&trk=group_items_see_more-0-b-cmr)

Rajan

[Excel Fox]

Try this


Sub GenerateRandomUnique()

Dim lng As Long
Dim var As Variant
Dim rng As Range
Set rng = Range("A2:A9")
With CreateObject("Scripting.Dictionary")
Do While .Count <= rng.Cells.Count
lng = Rnd * 99 + 1
.Item(lng) = Empty
Loop
var = Application.Transpose(.Keys)
SortIntegerArray var
If rng.Columns.Count = 1 Then
rng.Value = var
ElseIf rng.Rows.Count = 1 Then
rng.Value = Application.Transpose(var)
End If
End With

End Sub

[marreco]

Hi.
In this case it would be the range "A2: I12" where numbers can not be repeated.

65 58 28 6 39 49 89 26 31
80 24 7 75 37 66 42 69 3
48 9 8 32 51 63 67 11 45
10 0 18 85 4 50 64 88 44
38 81 93 1 27 41 5 16 73
76 82 21 92 35 23 61 13 46
86 74 56 96 71 29 47 33 43
15 36 97 91 59 14 77 72 20
68 87 79 62 83 17 19 22 84
53 34 95 57 12 25 55 40 54
70 98 30 90 60 52 2 94 78

[Admin]

Hi

you can try this UDF.


Option Explicit
Function RANDOMNUMGENERATOR(ByVal MinVal As Long, ByVal MaxVal As Long, _
Optional HowMany As Long, Optional ByVal UNIQUE As Boolean = True)

'// Created by : Krishnakumar @ ExcelFox.com

Dim Diff As Long, Tot As Long
Dim RNG() As Long, n As Long
Dim AC, RowsCount As Long
Dim tmp, ColCount As Long
Dim r As Long, c As Long


On Error Resume Next
Set AC = Application.Caller
If Err.Number <> 0 Then
Err.Clear: On Error GoTo 0: GoTo 2:
End If
On Error GoTo 0

If TypeName(AC) <> "Range" Then
RANDOMNUMGENERATOR = CVErr(xlErrRef)
Exit Function
End If

Application.Volatile

RowsCount = AC.Rows.Count
ColCount = AC.Columns.Count
Tot = RowsCount * ColCount
If HowMany <> Tot Then HowMany = Tot

ReDim RNG(1 To RowsCount, 1 To ColCount)

2:
Diff = MaxVal - MinVal
RANDOMNUMGENERATOR = Empty
If UNIQUE Then
With CreateObject("scripting.dictionary")
Do While .Count <= HowMany - 1
Randomize
.Item(MinVal + Int(Rnd * Diff)) = Empty
Loop
tmp = .keys
For r = 1 To RowsCount
For c = 1 To ColCount
RNG(r, c) = tmp(n)
n = n + 1
Next
Next
RANDOMNUMGENERATOR = RNG 'Application.Transpose(.keys)
End With
Else
For r = 1 To RowsCount
For c = 1 To ColCount
RNG(r, c) = MinVal + Int(Rnd * Diff)
Next
Next
RANDOMNUMGENERATOR = RNG
End If

End Function

select A2:I12, type =RANDOMNUMGENERATOR(1,100)

It's an array formula. Confirmed with CTRL + SHIFT + ENTER

[marreco]

Hi.

Thank you all for helping me, it was great!!