SOLVED

=RANDBETWEEN NO DUPLICATES BETWEEN 1-52

Copper Contributor

Hi, 

I have been struggling with this for some time, I have office 2019, and I want to use that formula as stated in Subject to get a row of random numbers everytime, but the number may have no duplicates, any help would be higly appreciated/

19 Replies
best response confirmed by SpartanBoy02 (Copper Contributor)
Solution

@SpartanBoy02 

In the attached file in cell A1 you can enter the number of random numbers which will be displayed in range A3:A54. Click the button in cell D2 to start the macro. Maybe this is what you want to do.

@SpartanBoy02 

How many random numbers do you want?

Thank you, will check it out now.

@SpartanBoy02 

Just noticed that i had

x = Int(Rnd * 50 + 1)

instead of 

x = Int(Rnd * 52 + 1)

Attached please find the adapted file for randbetween 1-52. 

You can use this simple approach if you are using excel 365
INDEX(UNIQUE(RANDARRAY(n^2, 1, min, max, FALSE)), SEQUENCE(n))
=INDEX(UNIQUE(RANDARRAY(10^2,1,1,20,TRUE)),SEQUENCE(10))

 

 

in fact, iCan easily use this alone without duplicates

=UNIQUE(RANDARRAY(20,1,2,20,1))

@SpartanBoy02 

A low-tech approach that works in all versions of Excel:

Enter the numbers 1 and 2 in A1 and A2.

Select A1:A2, then fill down to A52.

In B1, enter the formula =RAND()

Fill down to B52.

Click in B1, then sort ascending.

The numbers in A1:A52 are now in random order.

You're right! i was thinking he is using office 365,
By the way, i also like your method! absolutely i love your own method

I learnt something new today courtesy of you

@Donald_Genes_ 
image_2022-02-07_054147.png

Thank you for your response, I tried both and the unique and sequence formula does not pick up. I am in the latest version that is why I do not understand why some formulas are working and others not. Please see attached picture for my version. 

Thank you for your reply, If i use RAND then i get values of 0.1555 or 0.8555, I can use =INT to round it to closest number, But this way I will still sit with duplicates, everytime i click on anything it must still recalculate and give me random number, no 0.1888 numbers and then also no duplicates.

I used this one now, it stil give me duplicates though.
=INT(RAND()*52)
This partially works, how can I make it use number from 1-52 and still have 52 cells, like all numbers must be shown everytime and when I click it it must give me random values but still 52 cells. Every number must be shown from 1-52 everytime I recalculate?

@SpartanBoy02 

If you enter 52 in cell A1 it will give you 52 different values for every recalculation.

@SpartanBoy02 

I felt sorry for Excel 2019 users in that they missed the cut and didn't get the new dynamic arrays.  Perhaps there should be a trade-in offer to move to Excel 2021 or 365?

 

= SORTBY(
      SEQUENCE(52),
      RANDARRAY(52)
  )

 

is neat and self contained.  Prior to that, a helper range is needed, so that the random numbers can be ranked without the values changing throughout the calculation

 

"helper column, 'random'"
= RAND()

"Formula column, array or implicit intersection"
= RANK(random, random)

 

@SpartanBoy02 

365 with some fancy formatting!

image.pngimage.png

Thank you so much, I was very tired last time i tried it, did not even see it. Thank you so much for the help, I appreciate it, and thanks to everyone that was willing to help. I will defnitely come back if I have blonde moments again :)
Hi guys, trying to follow this an i like the screen above. Kind of what I want to achieve.
So, wanna draw 7 cards from deck of 52. How do i ensure that same card is not drawn?
Why are there no move and copy buttons in the windows 11 toolbar ? ? ? The toolbar in the window 11 is useless !!!

@MdreyerZA 

The problem is much the same only you have to finish by picking a block of 7 values

= INDEX(RANK(random, random),{1;2;3;4;5;6;7})

or as I would prefer using 365 beta

= LET(
      k,       SEQUENCE(52),
      rand,    RANDARRAY(52),
      ordered, SORTBY(k,rand),
      TAKE(ordered,7)
  )
A VBA macro to sample without replacement. If your version of Excel doesn't support dynamic arrays, then select a column of cells at least the size of the sample size you want and use ctrl-shift-enter for the function.


Function SampleNoReplace(ByVal lo As Long, ByVal hi As Long, ByVal SampleSize As Long) As Variant
'Returns a sample, without replacement, of SampleSize from the range lo to hi
'Written 1999/7/7 David J. Braden
'PLEASE retain all comments: Originally posted to microsoft.public.excel as HGSample2a
'To see sample in older versions of Excel, select a column of cells the size of the sample,
'then ctrl-shift-enter the function

Dim hiP1 As Long, i As Long, j As Long
Dim ret() As Variant, temp As Variant

Application.Volatile 'comment out this line for single shot

'The following allows more flexible specification of the support.
If lo > hi Then temp = lo: lo = hi - 1: hi = temp Else lo = lo - 1

ReDim temp(1 To hi - lo)
For i = hi - lo To 1 Step -1
temp(i) = i
Next
hiP1 = UBound(temp) + 1
'If SampleSize > UBound(temp) Then SampleSize = UBound(temp)
ReDim ret(1 To SampleSize, 1 To 1)
For i = 1 To SampleSize
j = i + Int(Rnd * (hiP1 - i))
ret(i, 1) = temp(j) + lo: temp(j) = temp(i)
Next i
SampleNoReplace = ret
End Function
1 best response

Accepted Solutions
best response confirmed by SpartanBoy02 (Copper Contributor)
Solution

@SpartanBoy02 

In the attached file in cell A1 you can enter the number of random numbers which will be displayed in range A3:A54. Click the button in cell D2 to start the macro. Maybe this is what you want to do.

View solution in original post