Rrandom Numbers with no Duplicate

zubair009

New Member
Joined
Sep 8, 2012
Messages
27
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
Joined
May 7, 2008
Messages
21,700
Office Version
2010
Platform
Windows
Enter the numbers 1-13 in A1:A13

In B1 and copy down, =RAND()

Sort by col B
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
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
Joined
May 9, 2009
Messages
16,623
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
Joined
Sep 8, 2012
Messages
27
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
Joined
Mar 1, 2010
Messages
644
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
Joined
May 9, 2009
Messages
16,623
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
Joined
Mar 1, 2010
Messages
644
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)
    
    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
ABCD
1Randomise BVolatileRandomise DStatic
213
32
412
511
68
74
81
95
1010
119
123
136
147
Sheet1
 
Last edited:

Forum statistics

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

Some videos you may like

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...
Top