Results 1 to 9 of 9

Thread: Unique Random Number In Ascending Order

  1. #1
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13

    [SOLVED]Unique Random Number In Ascending Order

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

    Code:
    Sub RndNumb()
    With Range("A2:A9")
        .Formula = "=INT(99*RAND()+1)"
        .Value = .Value
    End With
    End Sub
    Last edited by marreco; 11-01-2012 at 07:28 PM. Reason: Solved

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Here's one way to do it....

    Code:
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13
    Hi.

    Great solution!!!

    Thank you very mutch!!!

  4. #4
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13
    Hi.
    how do I use this code in the range "A2: I2"?
    As I mark this post as solved?

  5. #5
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    you should like to see the discussion here on this topic

    Get Random List : « excelpoweruser
    Get Random List : | LinkedIn

    Rajan

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try this

    Code:
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13
    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

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    you can try this UDF.

    Code:
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13
    Hi.

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

Similar Threads

  1. Replies: 6
    Last Post: 05-10-2013, 01:13 AM
  2. List of files in chronological order
    By Rasm in forum Excel Help
    Replies: 2
    Last Post: 11-12-2012, 10:16 PM
  3. Random Unique Number Generator Excel VBA
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 10-18-2012, 01:00 PM
  4. Replies: 2
    Last Post: 01-07-2012, 12:11 AM
  5. Create Random Number Generator VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 12-01-2011, 10:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •