MS Excel: Unique random number generator within a range
2014-04
I searched for a similar question but couldn't find one..
I want to generate 10 groups out of numbers ranging from 1-60 (including both), with each and every group containing random and non-repeating numbers. How can I do this in excel?
How to use it
- Open Excel & VBA editor (Alt+F11)
- Insert the code below under
Sheet1
- Go back to Excel and select your desired range to fill with random & non-repeating numbers
- Execute the macro (Alt+F8)
Sub randomNumbers()
Low = Application.InputBox("Enter first valid value", Type:=1)
High = Application.InputBox("Enter last valid value", Type:=1)
Selection.Clear
For Each cell In Selection.Cells
If WorksheetFunction.CountA(Selection) = (High - Low + 1) Then Exit For
Do
rndNumber = Int((High - Low + 1) * Rnd() + Low)
Loop Until Selection.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
cell.Value = rndNumber
Next
End Sub
I love those small and simple solutions so much
Hi I need to ensure the values unique in range with if possible with a high performance way. Sample data as shown below:
A B
1 Serial From - Serial To
2 364500 364600
3 326900 326940
4 120000 120300
5 154700 154800
6 460 480
- Table is unsorted
- There are two numbers in each row which always A is less than B
- I always enter new values to next empty row
There musnt be any number that match the any range above. If I enter 120150 and 120700 (because of 4th row) it must give error. How could I do that? I use Excel 2007. Thanks
{=IF(SUM(((A6>=$A$1:A5)*(A6<=$B$1:B5))+((B6>=$A$1:A5)*(B6<=$B$1:B5)))>0,"OVERLAP","")}
If you put this in C6 and you put your next numbers in A6 and B6, C6 will say 'overlap' if any do.
Don't type the curly braces {}. Instead enter the formula using control+shift+enter, because it's an array formula.