Forum Discussion

SpartanBoy02's avatar
SpartanBoy02
Copper Contributor
Feb 06, 2022
Solved

=RANDBETWEEN NO DUPLICATES BETWEEN 1-52

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 du...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Feb 06, 2022

    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.

MdreyerZA's avatar
MdreyerZA
Copper Contributor
May 20, 2022
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?
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 21, 2022

    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)
  )
    • David_Braden's avatar
      David_Braden
      Copper Contributor
      May 22, 2022
      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

Resources