Generate Random Number Wihout Repeat In One Cell

[kumara_faith]

Hi,

I am creating a sheet in excel for my company lucky draw. I have 500 participants and I need to create a formula in a single cell that would randomly generate numbers between 001 to 500 without repetitions in one single cell when the guest of honor hits F9. I tried RAND and then RANDBETWEEN but it does generate duplicates.

I have looked through the other post but most are VBA and does not generate in one single cell.

Appreciate all the help I could get.

 

[skorpionkz]

how many numbers you need to generate?

 

[kumara_faith]

Hi,

I need to generate 50 numbers.

 

[skorpionkz]

try this code.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit

Sub RNGen()

Application.ScreenUpdating = False

Dim strArray As String, myArray() As String, FindMe As String
Dim LRandomNumber As Integer, iNum As Integer, lFilter As Integer
Dim lUpp As Long, lLow As Long, lCheck As Long

strArray = Empty
For iNum = 1 To 50
    If iNum > 50 Then Exit For
    LRandomNumber = Int((500 * Rnd()) + 1)
    
    If Not strArray = Empty Then
        FindMe = LRandomNumber
        
        If Not UBound(Filter(myArray, FindMe)) > -1 Then
        strArray = strArray & "," & LRandomNumber
        myArray = Split(strArray, ",")
        Else
        iNum = iNum - 1
        End If
    Else
        strArray = LRandomNumber
        myArray = Split(strArray, ",")
    End If

Next iNum

MsgBox (strArray)

Application.ScreenUpdating = True

End Sub

 

[kumara_faith]

Hi,

I received an error as below:

Compile error:
Expected end sub

 

[kumara_faith]

Hi ,

I have a cross post here:

Generate Random Number Wihout Repeat In One Cell

 

[skorpionkz]

It seems like you didn't copy whole code. please copy whole code and try again

 

[kumara_faith]

Hi,

Thank you and I have pasted the entire code.

The code returns a box which has numbers in it. I am assuming this is the random 50 numbers. But how would this help to create a spreadsheet where the guest would come and just hit F9 and the screen populates in a single cell 50 random numbers without duplicates? The numbers needs to be generated during the event and not preset just like a lucky draw.

Is there a way to achieve this? My apologies for taking your time. Appreciate your patience.

 

[skorpionkz]

you will need to have 2 macros. Result will show up in active cell

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Select "ThisWorkbook" module
5. Where the cursor is flashing, paste the code

Private Sub Workbook_Open()
Application.OnKey "{F9}", "RNGen"
End Sub

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Press the keys ALT + I to activate the Insert menu
3. Press M to insert a Standard Module
4. Where the cursor is flashing, paste the code

Option Explicit

Sub RNGen()

Application.ScreenUpdating = False

Dim strArray As String, myArray() As String, FindMe As String
Dim LRandomNumber As Integer, iNum As Integer, lFilter As Integer
Dim lUpp As Long, lLow As Long, lCheck As Long

strArray = Empty
For iNum = 1 To 50
    If iNum > 50 Then Exit For
    LRandomNumber = Int((500 * Rnd()) + 1)
    
    If Not strArray = Empty Then
        FindMe = LRandomNumber
        
        If Not UBound(Filter(myArray, FindMe)) > -1 Then
        strArray = strArray & "," & LRandomNumber
        myArray = Split(strArray, ",")
        Else
        iNum = iNum - 1
        End If
    Else
        strArray = LRandomNumber
        myArray = Split(strArray, ",")
    End If

Next iNum

ActiveCell = strArray

Application.ScreenUpdating = True

End Sub

 

[kumara_faith]

Hi,

Thank you for your response.

My apologies. I think I might not have been clear in my request. The numbers needs to be populated one by one in a single cell as the user hits F9. There is a total of 50 lucky draw prizes to be given. So as each guest comes forward, they will press F9 and one by one the numbers will populated. The numbers will populate in one cell.

My apologies for any inconvenience and appreciate your time and patience.