Create a List of Random Numbers without Repeats
Вставка
- Опубліковано 12 вер 2024
- Here's a video that will show you how to create a list of random numbers that don't repeat. I'm sure there are many other uses for this trick, but the only one I can think up now is if you didn't want to rely on a lottery machine to perform the quick pick. Aside from getting your own lottery balls, throwing them around and picking up the "lucky" numbers, Excel can also let you create your own quick pick random numbers.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
For more videos that cover random number or value generation see ua-cam.com/play/PL-n8f1cY_Qw_FyrtQzHiqktUC6PuqEzQE.html
...
@@DougHExcel make one about pick 3
Hi sir good info, but I have a doubt,How to note this numbers automatically in Excel sheet or other Excel sheet when we press F9. Every time we have to note it down means it is very difficulty. please clarify. Thank you
This was the best! Exactly what I needed! Thanks heaps bro, From Australia
Glad it helped!
That is a really cool trick - I may have to teach this in my Stats Class : ) Thanks, Teammate!
Thanks Mike glad you liked!
This is great!
My method was to create a macro to copy rand value onto another column, then rank and sort it accordingly.
Gotta try this tomorrow, thanks a lot
Great method
I already made a macro for random between 1 to 20, but in Doug method it is easier to using function only but i have two questions:-
1. How to make only one time random list in sheet?
2. How to be sure there will be no exact random number in the random list? such in my case for the 20 numbers only!!
Hi Mark Renton, thanks for the comment!
Here easy way:
In A column just fill series between 1 and what you want, for exp. 10000 . Then in B column type =RAND() and let it till the end of A column. And then sort B column from small to large or vice verse and expand selection in option window that will appear. And you`ll get randomly sorted and none repeated nums between 1 and 10000 in A column. Delete column B.
Thanks, brother. Now I can generate random number for my board games. Splendid!
Have fun!
THANKS! LIFE SAVER!
Espectacular y sencillo.... muy útil. Muchas gracias Doug - Thanks a lot Doug!
De nada!
Thanks for the no-repeat : )
Hi Mike, thanks for the comment!
On a laptop.. fn+f4 not just f4, also ; instead of , for some versions of excel.
Excellent. Very useful. Thanks Doug.
Hi Kunhimoidu Abootty, thanks for the comment!
How do you sort them from low to high? But also react to the other sequences, that it will know what kind of sequence it will have the next time?
this might give some idea >> ua-cam.com/video/paym2CQ5sw0/v-deo.html
FREAKING GENIUS!!!!!!!!!!!!!!!
Hi jason sullivan, thanks for the comment!
This is really cool! Is there a shortcut to not change the k value for each cell? dragging the fill handle doesn't do the trick. What method could we use to generate 200 random numbers without repeat?
not sure I understand the question, but by default the k value is constant...if it's meant that to have it increment you can have the column header just be the numbers 1...200 and in the LARGE function reference k for the relative cell where the numbering is.
@@DougHExcel Yes I meant an increment for the k value. I don't get how can we reference k with a cell (k requires a numerical input). I basically want to create 10 sets of 20 numbers each without repeat ofcourse. The total array is 1-200.
HI thanks man this is an awesome tutorial. I was wondering if I had two bonus numbers how would I go about that? thanks in advance
Hey man I figured it out. This is an awesome tut for reals man thanks
Actually, I'm stuck on how to prevent the repeat in the 2 bonus numbers. need help.
Cool trick thank you. there are plenty of cases where this is useful, for example select 5 volunteers from a group of 10.
Hi Bart Titulaer, glad you liked it, thanks for commenting!
Hey Doug...nice trick....why didn't u use columns as the 'k' argument for large though, instead of hard-coding the numbers?
Hi Ayize Akono, thanks for the comment! You're right I could've used the COLUMNS function to do this :-) Excels is fantastic cause there's so many ways to solve a problem!
How many random numbers we can select from 1 to 52 numbers with out duplicate please advise
My first thought was to figure out the reason you are not using the RAND itself to get non-repeating numbers. Unfortunately, I believe the main reason is that we cannot modify the decimal random numbers into the sorted 1-5 numbers.
Hi M. Mir2, thanks for the comment
Doug is it possible to have 6 rows of start and end times that are generated randomly but add up to whatever total is chosen say a start at 7 am and Excell fills in the start and end time for each of 6 rows to meet a given time of 8 or whatever total hours is chosen without repeats over 30 rows?
in other cells you can =sum(x,y,...z) that range
Great Vid. Is there any way to have the random generator generate with a button press instead of pressing F9, doing it for football squares.
Hi Joshua W, thanks for the comment; you could record a macro and attach it do a button to refresh the calculation.
Works great. How about if my numbers in my box have to be a 2 digit number. I want to eliminate any single digits. I tried generation my random number list starting with row 10 but it still listed single digit numbers.
maybe one of these can help ua-cam.com/video/68pZ0urMFkQ/v-deo.html
Thank you very much, this was very helpful. It worked perfectly in google sheets, but it's a shame F9 doesn't refresh
Glad it helped!
Hello dear
Ihave 10 deferent numbers as Inout
I want to see how many random number I can create from that 10 number
For example my number is
03,05,07,11,13,15,17,19,21,23
Find out how many random number I can creat using above number
Thanks
Maybe something from the playlist can give an idea ua-cam.com/play/PL-n8f1cY_Qw8iJkY0bCyYxXijfeYIwbcd.html
Total how many random numbers qe can do from 1 to 52 with our duplicate
This does not work when using whole numbers (I tried it), as I get duplicates.
I’m guessing that it’s possible for the random numbers in column A to repeat (though unlikely), what would happen if two or more of them did ?
There's always a possibility and my 1st thought is that it'll involved testing by continually refreshing...which would take some time 😉
thank you J
Very welcome
Thank you Doug :-)
You're Welcome!
Could you technically have a repeat if the Rand() in two cells (let’s pretend a large dataset so it’s more likely for a repeat).Then the Match would return the same ordinal position; or am I wrong here? You did a “problem” similar to this in another video that used TRUE and false in an array to get a “” as a result for the position and then Excel would skip that value, in order for no repeats.
Technically yes it could have a repeat though you'd have to generate 10^13 numbers before this could happen according to support.microsoft.com/en-us/help/828795/description-of-the-rand-function-in-excel. And this was referencing Excel 2003 but applies to 2010. Thanks for referencing the previous video...I didn't even remember I had another one like this :-)
Its great but what if I need to generate random numbers for 300 entries? Do I need to go to 300 cells to change the rank from 1 to 300?
for that large amount you can rely on the COLUMNS function to count. Try =COLUMNS($A$1:A1) in place of the 1, 2, 3, etc rank.
Hello, if i have 20 favorite numbers how in excel can i create all possible combinations
Hi Tim Lewis, thanks for the comment! Sounds like a cartesian join. See
ua-cam.com/video/4FsFJPuN6Ro/v-deo.html
ua-cam.com/video/YWHyArE90QM/v-deo.html
want to to know ho to select the Nth row ie D5:P5 where N is a random number in column A
Hi houarian, thanks for the comment! You may want to used INDEX/MATCH ua-cam.com/video/kxeSS8n3WNI/v-deo.html
@@DougHExcel Hi there, thanks. the INDEX/MATCH did not do it but INDIRECT Function did.
Hi, i need help.
I followed your tutorial and it worked great, however i have a lucky draw that needs to select 10 names at one go for 20 rounds,
How do i eliminate the names that were already chosen? Else It starts to show up at the 3rd re-roll onwards.
Need help urgently, thanks..
maybe one of these videos can help ua-cam.com/users/dough517search?query=random
Hello, how to setup random number generator to avoid using previous numbers in future ?
maybe one of the other video will help ua-cam.com/users/dough517search?query=random+number
I want to create a math question for my daughter of multiplication like 4 x 5 =______ where 4 and 5 are the "random numbers". The numbers only go up to 12 (12 x 12). Is there a way that she can put in an answer and enter without the random numbers changing ?
yes...my 1st thought would be this can be done...but with some VBA code...unfortunately i don't know VBA that well...you might want to pose this question to the mrexcel.com forum...lots of gurus there :-)
I tried to make small thing of what u said. Make 1st column =RANDBETWEEN(1;12) and then another column =RANDBETWEEN(1;12) then next column leave it for answer and one column after make =IF(G5=C5*E5;"Correct";" ") ,,,, where column 1 is C5 and column 2 is E5 and column for answer is G5, so when she will type in correct answer in column H will appear CORRECT, otherwise it will be blank, ... Column D i did use only for visual which Shows X and column F was visual too which shows =
and u can make it as long as u want that sheet for multiplications
How about sorting those numbers generated? The winnings drawing always shows least to greatest. The lottery never shows the exact order the numbers pop out.
Maybe this will give an idea. Sort Columns Horizontally - Excel #Shorts
ua-cam.com/users/shortsm6Vbsy5URCk?feature=share
is it possible to generate results based on previous results
Not sure what you mean what kind of example?
@@DougHExcel I would guess he means from previous selected numbers; so a history of all the winning numbers.
Question: Is there a possibility that two generated random numbers are the same on your 56 selection?
Hi M. Mir2, thanks for the comment. There's always a possibility😉
Hi would you be able to do me a permutation for numbers? (without repetition or order), that would display the numbers as i type it in ect, I can send you an excel workbook including instructions. With regards Steve.
maybe one of these could give insight ua-cam.com/users/dough517search?query=permutation
@@DougHExcel None of them are really what i am looking for. For example I will type out in neumetric order 10 numbers, and then perm them numbers into lines of 5, any 5 from 10 = 252 lines. As I type out the 10 numbers, they will automatically start appearing below forming the lines of five, until I have typed out the last number. A s I mentioned before I can send you my excel workbook with instructions, and a small example of how it would look like. With regards Steve.
I have one doubt. Can we put VLOOKUP formula for Google spreadsheet to excel sheet or Excel to Google spreadsheet
not sure on the how it would work with google sheets :-|
Thank You Doug
Referring time 5:00. Once i generate a random decimal number, how do I drag down the random number to form a list? Everytime i drag it copies the same value. Anyone knows what I’m doing wrong?
seems like it's copying the value instead of the formula. When dragging the file handle to drag the selection box down to copy or file series there should be a small icon box that shows up that gives option to select copy, fill series, etc.
Wow above my pay grade... I’m sure I’ll love it in like a year and a half.
Hi GUD MURNIN MISS WOOSTA, thanks for the comment! Have faith, you'll get it sooner rather than later :-)
How to generate random number in multiples of 100?
maybe something in here ua-cam.com/video/Z_b3iSEbvtA/v-deo.html
Having hell with it. i Used 1 through 24 (Texas All or Nothing Numbers) and I typed the formula in the same format. The result will not compute. It tells me that because I entered the equal sign it wants me to use a plus sign or something. I used to love Excel until this experiment.
Hi craigory1971, try a post on the mrexcel.com forum!
This helped but it is not a "list of random numbers". I was looking for all possible combinations from an array
maybe something from these
ua-cam.com/video/Z_b3iSEbvtA/v-deo.html
ua-cam.com/video/68pZ0urMFkQ/v-deo.html
Maybe type the formula here for us to put in our sheet. I can't see the formula on this video
Cell D2 >> MATCH(LARGE($A$1:$A$56,1),$A$1:$A$56,0)
Ok so cool will give this a try now.
Mr. Douh H;
Thank you for the great insights video. I was looking for a video like that was based on NOT repeating combination number. Now, how about doing one like this one. I want to develop a formula that BASED on the combination LOTTERY number that already came out for a year or since the power ball or Mega ball began, won't REPEAT IT SELF again. Meaning, because the random world is random, is 1 out of a million chances that the same combination will come out again. For example, if the combination winning number of 2,4,18,34,23 came out, now we have to write a formula that investigate or search for other combination except that one. I hope you can understand my Latino Accent. If you don't please let me know and I will re-write it again. I'm just like to conduct research with formulas and numbers combination that won't repeat itself. Thanks for your time, if I ever hit the big one, I will share a percentage with you sir, god bless.
Respectfully;
Andy
SSG(P), USA
War Veteran Disable
Texas
Interesting...will look into this one :-)
what a misleading tutorial. Its not a random generator tutorial. Its about finding out the largest number from random numbers. Not useful as per title.
maybe try some other vids here ua-cam.com/video/Z_b3iSEbvtA/v-deo.html