# Rrandom Numbers with no Duplicate

#### zubair009

##### New Member
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

##### MrExcel MVP
Enter the numbers 1-13 in A1:A13

In B1 and copy down, =RAND()

Sort by col B

#### Rick Rothstein

##### MrExcel MVP
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

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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
Code:
``=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

Last edited:

#### T. Valko

##### Well-known Member
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:

Code:
``````Sub ShuffleTheNums()
Calculate
End Sub``````

#### Marcol

##### Well-known Member
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
Code:
``=MRAND(13,1,13)``
Confirm with Ctrl+Shift+Enter

Leave D2:D14 empty

In the Sheet Module paste this
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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
ABCD
1Randomise BVolatileRandomise DStatic
213
32
412
511
68
74
81
95
1010
119
123
136
147
 Sheet1

Last edited:

1,081,418
Messages
5,358,559
Members
400,504
Latest member
RedSquirrel

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...