Rrandom Numbers with no Duplicate

[zubair009]

Hi,
I need 13 random numbers from cell A1 to A13 if i press the button all cells should select random numbers but each cell should not have duplicate number from A1 TO A13. please help.

thanks alot.
regards

 

[shg]

Enter the numbers 1-13 in A1:A13

In B1 and copy down, =RAND()

Sort by col B

 

[Rick Rothstein]

I need 13 random numbers from cell A1 to A13 if i press the button all cells should select random numbers but each cell should not have duplicate number from A1 TO A13.

What is the range for your random numbers (1 to 13, 13 numbers from some larger group of source numbers, all whole numbers, fractions permitted, etc.)?

Also, are you looking for a formula solution where the random numbers will change everytime the worksheet recalculates, or did you want VB code where the random numbers would not change with the sheet recalculation, but rather would only change when you manually ran the VB code?

 

[T. Valko]

Hi,
I need 13 random numbers from cell A1 to A13 if i press the button all cells should select random numbers but each cell should not have duplicate number from A1 TO A13. please help.

thanks alot.
regards

Here's one way...

Create this defined expression.
Name: Nums
Refers to: ={1;2;3;4;5;6;7;8;9;10;11;12;13}

There must be a cell above the first formula cell and that cell must not contain one of the numbers. So, enter this array formula** in A2 and copy down to A14:

=SMALL(IF(ISNA(MATCH(Nums,A$1:A1,0)),Nums),INT(RAND()*(13-(ROWS(A$2:A2)-1)))+1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

It will generate the numbers 1 thru 13 in random order without repeats.

 

[zubair009]

thanks, it work great is it possible to use command button as soon as i press button all 13teen cells get refreshed with new random numbers. actually its a chess game ...... thank once again for your help. kind regards.

 

[Marcol]

As a note to this thread.

If you have the MoreFunc add-in you could use MRAND()

Select A1:A13
Type this in the formula bar

=MRAND(13,1,13)

Confirm with Ctrl+Shift+Enter

You should now have a list of numbers from 1 to 13 in random order in A1:A13

Press f9 to recalculate

 

[T. Valko]

thanks, it work great is it possible to use command button as soon as i press button all 13teen cells get refreshed with new random numbers. actually its a chess game ...... thank once again for your help. kind regards.

I'm not much of a programmer so I can't help you with a command button.

However, you don't need a button, you can just hit function key F9 and the numbers will refresh.

If you absolutely have to have a button I would just use a Forms button and attach this simple one line macro to it:

Sub ShuffleTheNums()
    Calculate
End Sub

 

[Marcol]

If you absolutely have to have a button .....

In my opinion Buttons on a worksheet are nothing but trouble, especially when sheets are copied time and time again.
The only advantage that I can see them having is they can be hidden when printing!

I prefer to use the Selection_Change Event to simulate a button.

Try this using MRAND()

Set up a worksheet as shown below

Select B2:B14
Enter this in the formula bar

=MRAND(13,1,13)

Confirm with Ctrl+Shift+Enter

Leave D2:D14 empty

In the Sheet Module paste this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Select Case Target.Address(0, 0)
        Case "A1"
            Range("B2:B14").Calculate
            Target.Offset(0, 1).Select
        Case "C1"
            Range("D2:D14").FormulaArray = "=MRAND(13,1,13,1)"
            Target.Offset(0, 1).Select
    End Select
    
End Sub

Now select either A1 or B1 at random to see the results.

I have used the MoreFunc function MRAND() in this example for a reason
If you want control over when the list randomises, Column D (static) will only change when you decide.
The volatile alternatives will change at every recalculation, this might prove undesirable.

Excel Workbook
	A	B	C	D
1	Randomise B	Volatile	Randomise D	Static
2		13
3		2
4		12
5		11
6		8
7		4
8		1
9		5
10		10
11		9
12		3
13		6
14		7
Sheet1