Rand() function in Excel generates a value >= 0 but < 1 (Greater or equal to zero but less than 1) e.g. 0.22489385
The simplest technique is to generate numbers using rand() function and Rank them.
CONS: There is a small possibility of a duplicate may appear. Some people just ignore this and treat the duplicate values as 1st, 2nd and so on.
My Logic (Correct me if I'm wrong) :)
1. Generate the first Number
2. Save the number (cells, list box, combo box, or array)
3. Generate a NEW Number and CHECK if already been generated.
4. If there's a duplicate, Generate a New Number again, until there's a unique value, save it and proceed to the next until it is finished.
Simple Right? :)
Generating a Value Between two Numbers
Int((UpperNumber - LowerNumber + 1) * Rnd + LowerNumber)
e.g. for 1-10, Integer of ((10-1+1) * Rnd + 1)
That's why most programmers only use Int(10 * Rnd + 1)
Where Rnd is almost the same as Rand() is Excel
Using VBA:
Dim RndNumber as Integer
RndNumber = Int(10 * Rnd + 1)
Steps
1. If you can't see the Ribbon GUI Developers Tab, here
2. Insert a List box: Click the Developers Tab > Insert Button > ActiveX Controls Group > Choose the 4th ActiveX Control (List Box Control)
Click and Hold while moving the cursor until Column F.
Once the List box ActiveX Control is loaded, Right-Click > Properties
change the (Name) to LstNumbers
3. Insert a Command Button: Click the Developers Tab > Insert > ActiveX Controls Group > Choose the 1st ActiveX Control (Command Button Control)
Click and Hold while moving the cursor located below the list box as shown in the picture below:
In the Properties Window: Change the Name of the Button e.g. CmdRandomize
Also, change the Name of the Command Button by modifying the Caption Property e.g. Randomize
4. At Column H, enter any number that will become the Range of Numbers as shown in the picture below:
5. To understand, I wrote a simple program that uses List Box as the Memory and that's the reason why we need to change the Name Property and input the Range in Column H
Double-Click the Command Button and Copy-Paste the Code Below:
------------------------------------------------------------------
Dim RndNumber As Integer 'declare the variable as integer
Dim ii As Integer 'declare the variable as integer
Dim FndNumber As Boolean 'Number was Found, YES or NO?
LstNumbers.Clear
Do
RndNumber = Int((Sheet1.Cells(2, 8) - Sheet1.Cells(1, 8) + 1) * Rnd + Sheet1.Cells(1, 8))
' :) Generate the first Number
If LstNumbers.ListCount <> 0 Then 'If First Number, No need to check
For ii = 1 To LstNumbers.ListCount
'check to listbox if the number is existing
If RndNumber = LstNumbers.List(ii - 1) Then
FndNumber = True
Exit For 'Gotcha, Duplicate, EXIT now
Else
FndNumber = False 'NOT FOUND
End If
Next ii
End If
If FndNumber = False Then LstNumbers.AddItem RndNumber
'save the number to listbox if no duplicate found
Loop Until LstNumbers.ListCount = Sheet1.Cells(2, 8) - Sheet1.Cells(1, 8) + 1
'generate until the last number.
'Do loop means read until the TOTAL no. of data to be randomize
------------------------------------------------------------------
DISCLAIMER NOTICE: The code above and the downloadable code can be modified and re-used but I will not be responsible to any problems that may appear.
6. Close the Visual Basic Editor and Start Running the Program by Turning off the Design Mode: In the Developers Tab, Click the Design Mode Button Icon
7. Click the Command Button to Generate numbers depending on the range (COLUMN H).
If you are having some difficulty in creating the example above, Download here (.xlsm Macro-enabled Excel Document)
For the old office 2000-2003 format. .xls file, download here
NOTE: Opening the Excel File
By default, All Macros in Excel is disabled and a security message will appear. To enable: read here first
How I came up with the formula?
References: Microsoft Excel and Visual Basic 6.0 Help Files
| Generating Random Value Between two Numbers
Int((Upperbound - Lowerbound + 1) * Rnd + Lowerbound) MS Excel Help File
(Upperbound - Lowerbound) * Rand + Lowerbound |
REAL-WORLD APPLICATIONS
Creating random numbers is very important. The simplest example is the famous MP3 player called WinAmp. When you click the option Shuffle, It will play all your Music Files in a Random way.
Did you experience playing your Playlist in Random but some songs was repeated? :)
I hope everybody have learn something in this example.
ENJOY!!!
