Create a List of Random Numbers without Repeats

Подписаться 78 тыс.

Просмотров 126 тыс.

50% 1

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.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 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.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

Опубликовано:

20 мар 2018

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 105

@DougHExcel 4 года назад

For more videos that cover random number or value generation see ru-vid.com/group/PL-n8f1cY_Qw_FyrtQzHiqktUC6PuqEzQE

@DougHExcel 3 года назад

...

@robertbays7136 3 года назад

@@DougHExcel make one about pick 3

@phanindrababu5686 2 года назад

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

@excelisfun 6 лет назад

That is a really cool trick - I may have to teach this in my Stats Class : ) Thanks, Teammate!

@DougHExcel 6 лет назад

Thanks Mike glad you liked!

@HallMade 4 года назад

This was the best! Exactly what I needed! Thanks heaps bro, From Australia

@DougHExcel 4 года назад

Glad it helped!

@kabootty 6 лет назад

Excellent. Very useful. Thanks Doug.

@DougHExcel 6 лет назад

Hi Kunhimoidu Abootty, thanks for the comment!

4 года назад

Espectacular y sencillo.... muy útil. Muchas gracias Doug - Thanks a lot Doug!

@DougHExcel 4 года назад

De nada!

@excelisfun 6 лет назад

Thanks for the no-repeat : )

@DougHExcel 3 года назад

Hi Mike, thanks for the comment!

@edgespace3777 3 месяца назад

THANKS! LIFE SAVER!

@m47kr3nt0n 6 лет назад

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

@abdanomer 6 лет назад

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!!

@DougHExcel 3 года назад

Hi Mark Renton, thanks for the comment!

@SyafiqZaidi71 4 года назад

Thanks, brother. Now I can generate random number for my board games. Splendid!

@DougHExcel 4 года назад

Have fun!

@Jmaxxx 3 года назад

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.

@DougHExcel 3 года назад

Hi Joshua W, thanks for the comment; you could record a macro and attach it do a button to refresh the calculation.

@ayizeakono8492 6 лет назад

Hey Doug...nice trick....why didn't u use columns as the 'k' argument for large though, instead of hard-coding the numbers?

@DougHExcel 6 лет назад

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!

@F_A_R_man 6 месяцев назад

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.

@guruprasadbm1987 6 лет назад

Thank you Doug :-)

@DougHExcel 6 лет назад

You're Welcome!

@davidharris6974 3 года назад

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?

@DougHExcel 3 года назад

in other cells you can =sum(x,y,...z) that range

@barttitulaerexcelbart9400 6 лет назад

Cool trick thank you. there are plenty of cases where this is useful, for example select 5 volunteers from a group of 10.

@DougHExcel 6 лет назад

Hi Bart Titulaer, glad you liked it, thanks for commenting!

@nicong3491 5 лет назад

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

@DougHExcel 5 лет назад

maybe one of these videos can help ru-vid.comsearch?query=random

@jasonsullivan5807 3 года назад

FREAKING GENIUS!!!!!!!!!!!!!!!

@DougHExcel 3 года назад

Hi jason sullivan, thanks for the comment!

@raghudas5266 2 года назад

How many random numbers we can select from 1 to 52 numbers with out duplicate please advise

@rockguitarist8907 6 лет назад

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.

@DougHExcel 6 лет назад

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 :-)

@MrSunshanmu 2 года назад

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

@MrSunshanmu 2 года назад

Hey man I figured it out. This is an awesome tut for reals man thanks

@MrSunshanmu 2 года назад

Actually, I'm stuck on how to prevent the repeat in the 2 bonus numbers. need help.

@tj9382 4 года назад

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 ?

@DougHExcel 4 года назад

There's always a possibility and my 1st thought is that it'll involved testing by continually refreshing...which would take some time 😉

@jackparker6472 3 года назад

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.

@DougHExcel 3 года назад

maybe one of these can help ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-68pZ0urMFkQ.html

@raghudas5266 2 года назад

Total how many random numbers qe can do from 1 to 52 with our duplicate

@yel4j 4 месяца назад

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?

@DougHExcel 4 месяца назад

this might give some idea >> ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-paym2CQ5sw0.html

@shubhammathur7810 3 года назад

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?

@DougHExcel 3 года назад

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.

@shubhammathur7810 3 года назад

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

@kajskoglund3570 2 года назад

On a laptop.. fn+f4 not just f4, also ; instead of , for some versions of excel.

@Kobusprins 6 лет назад

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 ?

@DougHExcel 6 лет назад

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 :-)

@LenardsD 5 лет назад

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

@mikemir121 6 лет назад

Question: Is there a possibility that two generated random numbers are the same on your 56 selection?

@DougHExcel 3 года назад

Hi M. Mir2, thanks for the comment. There's always a possibility😉

@GhostRider-mz1hl 4 года назад

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.

@DougHExcel 4 года назад

maybe one of these could give insight ru-vid.comsearch?query=permutation

@GhostRider-mz1hl 4 года назад

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

@mikemir121 6 лет назад

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.

@DougHExcel 3 года назад

Hi M. Mir2, thanks for the comment

@armen3543 4 года назад

Hello, how to setup random number generator to avoid using previous numbers in future ?

@DougHExcel 4 года назад

maybe one of the other video will help ru-vid.comsearch?query=random+number

@guruprasadbm1987 6 лет назад

I have one doubt. Can we put VLOOKUP formula for Google spreadsheet to excel sheet or Excel to Google spreadsheet

@DougHExcel 6 лет назад

not sure on the how it would work with google sheets :-|

@guruprasadbm1987 6 лет назад

Thank You Doug

@almiranteazote5125 4 года назад

thank you J

@DougHExcel 4 года назад

Very welcome

@mark91345 Год назад

This does not work when using whole numbers (I tried it), as I get duplicates.

@Dogflamingo 3 года назад

Thank you very much, this was very helpful. It worked perfectly in google sheets, but it's a shame F9 doesn't refresh

@DougHExcel 3 года назад

Glad it helped!

@muhammadj.chaudhry2702 2 года назад

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?

@DougHExcel 2 года назад

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.

@timlewis2211 2 года назад

Hello, if i have 20 favorite numbers how in excel can i create all possible combinations

@DougHExcel 2 года назад

Hi Tim Lewis, thanks for the comment! Sounds like a cartesian join. See ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4FsFJPuN6Ro.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-YWHyArE90QM.html

@andycobra49aor 4 года назад

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

@DougHExcel 3 года назад

Interesting...will look into this one :-)

@sandeepkumar-dw6xq 5 лет назад

is it possible to generate results based on previous results

@DougHExcel 5 лет назад

Not sure what you mean what kind of example?

@Maxitco 5 лет назад

@@DougHExcel I would guess he means from previous selected numbers; so a history of all the winning numbers.

@kps3827 4 года назад

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?

@DougHExcel 4 года назад

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.

@houarian 3 года назад

want to to know ho to select the Nth row ie D5:P5 where N is a random number in column A

@DougHExcel 3 года назад

Hi houarian, thanks for the comment! You may want to used INDEX/MATCH ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kxeSS8n3WNI.html

@houarian 3 года назад

@@DougHExcel Hi there, thanks. the INDEX/MATCH did not do it but INDIRECT Function did.

@SchizoaffectedGamer2112 4 года назад

Wow above my pay grade... I’m sure I’ll love it in like a year and a half.

@DougHExcel 4 года назад

Hi GUD MURNIN MISS WOOSTA, thanks for the comment! Have faith, you'll get it sooner rather than later :-)

@Kolian1274 3 года назад

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

@DougHExcel 3 года назад

Maybe something from the playlist can give an idea ru-vid.com/group/PL-n8f1cY_Qw8iJkY0bCyYxXijfeYIwbcd

@frozeneternity93 5 лет назад

This helped but it is not a "list of random numbers". I was looking for all possible combinations from an array

@DougHExcel 3 года назад

maybe something from these ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Z_b3iSEbvtA.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-68pZ0urMFkQ.html

@craigory1971 4 года назад

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.

@DougHExcel 3 года назад

Hi craigory1971, try a post on the mrexcel.com forum!

@kimberlyguillen7324 4 года назад

Maybe type the formula here for us to put in our sheet. I can't see the formula on this video

@DougHExcel 3 года назад

Cell D2 >> MATCH(LARGE($A$1:$A$56,1),$A$1:$A$56,0)

@kimberlyguillen7324 2 года назад

Ok so cool will give this a try now.

@anushkasingh5431 3 года назад

How to generate random number in multiples of 100?

@DougHExcel 3 года назад

maybe something in here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Z_b3iSEbvtA.html

@ashzole 5 месяцев назад

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.

@DougHExcel 5 месяцев назад

Maybe this will give an idea. Sort Columns Horizontally - Excel #Shorts ru-vid.comm6Vbsy5URCk?feature=share

@Hacks4Me 4 года назад

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.

@DougHExcel 3 года назад

maybe try some other vids here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Z_b3iSEbvtA.html