SOLVED

=RANDBETWEEN NO DUPLICATES BETWEEN 1-52

%3CLINGO-SUB%20id%3D%22lingo-sub-3123089%22%20slang%3D%22en-US%22%3E%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3123089%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20been%20struggling%20with%20this%20for%20some%20time%2C%20I%20have%20office%202019%2C%20and%20I%20want%20to%20use%20that%20formula%20as%20stated%20in%20Subject%20to%20get%20a%20row%20of%20random%20numbers%20everytime%2C%20but%20the%20number%20may%20have%20no%20duplicates%2C%20any%20help%20would%20be%20higly%20appreciated%2F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3123089%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3123198%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3123198%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298669%22%20target%3D%22_blank%22%3E%40SpartanBoy02%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%20in%20cell%20A1%20you%20can%20enter%20the%20number%20of%20random%20numbers%20which%20will%20be%20displayed%20in%20range%20A3%3AA54.%20Click%20the%20button%20in%20cell%20D2%20to%20start%20the%20macro.%20Maybe%20this%20is%20what%20you%20want%20to%20do.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3123216%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3123216%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298669%22%20target%3D%22_blank%22%3E%40SpartanBoy02%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20many%20random%20numbers%20do%20you%20want%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3123311%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3123311%22%20slang%3D%22en-US%22%3EThank%20you%2C%20will%20check%20it%20out%20now.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3123318%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3123318%22%20slang%3D%22en-US%22%3E1-52%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3123350%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3123350%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298669%22%20target%3D%22_blank%22%3E%40SpartanBoy02%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20noticed%20that%20i%20had%3C%2FP%3E%3CP%3Ex%20%3D%20Int(Rnd%20*%2050%20%2B%201)%3C%2FP%3E%3CP%3Einstead%20of%26nbsp%3B%3C%2FP%3E%3CP%3Ex%20%3D%20Int(Rnd%20*%2052%20%2B%201)%3C%2FP%3E%3CP%3EAttached%20please%20find%20the%20adapted%20file%20for%20randbetween%201-52.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3123366%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3123366%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20use%20this%20simple%20approach%20if%20you%20are%20using%20excel%20365%3CBR%20%2F%3EINDEX(UNIQUE(RANDARRAY(n%5E2%2C%201%2C%26nbsp%3Bmin%2C%26nbsp%3Bmax%2C%20FALSE))%2C%20SEQUENCE(n))%3CBR%20%2F%3E%3DINDEX(UNIQUE(RANDARRAY(10%5E2%2C1%2C1%2C20%2CTRUE))%2CSEQUENCE(10))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20fact%2C%20iCan%20easily%20use%20this%20alone%20without%20duplicates%3C%2FP%3E%3CP%3E%3DUNIQUE(RANDARRAY(20%2C1%2C2%2C20%2C1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3123487%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3123487%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298669%22%20target%3D%22_blank%22%3E%40SpartanBoy02%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20low-tech%20approach%20that%20works%20in%20all%20versions%20of%20Excel%3A%3C%2FP%3E%0A%3CP%3EEnter%20the%20numbers%201%20and%202%20in%20A1%20and%20A2.%3C%2FP%3E%0A%3CP%3ESelect%20A1%3AA2%2C%20then%20fill%20down%20to%20A52.%3C%2FP%3E%0A%3CP%3EIn%20B1%2C%20enter%20the%20formula%20%3DRAND()%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20B52.%3C%2FP%3E%0A%3CP%3EClick%20in%20B1%2C%20then%20sort%20ascending.%3C%2FP%3E%0A%3CP%3EThe%20numbers%20in%20A1%3AA52%20are%20now%20in%20random%20order.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3123520%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3123520%22%20slang%3D%22en-US%22%3EYou're%20right!%20i%20was%20thinking%20he%20is%20using%20office%20365%2C%3CBR%20%2F%3EBy%20the%20way%2C%20i%20also%20like%20your%20method!%20absolutely%20i%20love%20your%20own%20method%3CBR%20%2F%3E%3CBR%20%2F%3EI%20learnt%20something%20new%20today%20courtesy%20of%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3124004%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3124004%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1146826%22%20target%3D%22_blank%22%3E%40Donald_Genes_%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image_2022-02-07_054147.png%22%20style%3D%22width%3A%20293px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345720i8F8A48CE9F50E24F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image_2022-02-07_054147.png%22%20alt%3D%22image_2022-02-07_054147.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20your%20response%2C%20I%20tried%20both%20and%20the%20unique%20and%20sequence%20formula%20does%20not%20pick%20up.%20I%20am%20in%20the%20latest%20version%20that%20is%20why%20I%20do%20not%20understand%20why%20some%20formulas%20are%20working%20and%20others%20not.%20Please%20see%20attached%20picture%20for%20my%20version.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3124062%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3124062%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20reply%2C%20If%20i%20use%20RAND%20then%20i%20get%20values%20of%200.1555%20or%200.8555%2C%20I%20can%20use%20%3DINT%20to%20round%20it%20to%20closest%20number%2C%20But%20this%20way%20I%20will%20still%20sit%20with%20duplicates%2C%20everytime%20i%20click%20on%20anything%20it%20must%20still%20recalculate%20and%20give%20me%20random%20number%2C%20no%200.1888%20numbers%20and%20then%20also%20no%20duplicates.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20used%20this%20one%20now%2C%20it%20stil%20give%20me%20duplicates%20though.%3CBR%20%2F%3E%3DINT(RAND()*52)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3124063%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3124063%22%20slang%3D%22en-US%22%3EThis%20partially%20works%2C%20how%20can%20I%20make%20it%20use%20number%20from%201-52%20and%20still%20have%2052%20cells%2C%20like%20all%20numbers%20must%20be%20shown%20everytime%20and%20when%20I%20click%20it%20it%20must%20give%20me%20random%20values%20but%20still%2052%20cells.%20Every%20number%20must%20be%20shown%20from%201-52%20everytime%20I%20recalculate%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3125860%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3125860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298669%22%20target%3D%22_blank%22%3E%40SpartanBoy02%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20enter%2052%20in%20cell%20A1%20it%20will%20give%20you%2052%20different%20values%20for%20every%20recalculation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3126843%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3126843%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298669%22%20target%3D%22_blank%22%3E%40SpartanBoy02%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20felt%20sorry%3CSPAN%3E%26nbsp%3Bfor%20Excel%202019%20users%20in%20that%20they%20missed%20the%20cut%20and%20didn't%20get%20the%20new%20dynamic%20arrays.%26nbsp%3B%20Perhaps%20there%20should%20be%20a%20trade-in%20offer%20to%20move%20to%20Excel%202021%20or%20365%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20SORTBY(%0A%20%20%20%20%20%20SEQUENCE(52)%2C%0A%20%20%20%20%20%20RANDARRAY(52)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eis%20neat%20and%20self%20contained.%26nbsp%3B%20Prior%20to%20that%2C%20a%20helper%20range%20is%20needed%2C%20so%20that%20the%20random%20numbers%20can%20be%20ranked%20without%20the%20values%20changing%20throughout%20the%20calculation%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%22helper%20column%2C%20'random'%22%0A%3D%20RAND()%0A%0A%22Formula%20column%2C%20array%20or%20implicit%20intersection%22%0A%3D%20RANK(random%2C%20random)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3129927%22%20slang%3D%22en-US%22%3ERe%3A%20%3DRANDBETWEEN%20NO%20DUPLICATES%20BETWEEN%201-52%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298669%22%20target%3D%22_blank%22%3E%40SpartanBoy02%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E365%20with%20some%20fancy%20formatting!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20944px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345882i3BA2929AD539FF27%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20943px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345883i68754C7AD21FA459%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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/

17 Replies
best response confirmed by SpartanBoy02 (Occasional 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 !!!