Pick a Name at Random from a List - Excel Formula

[@BimoNuksmo]

Much thanks! This is the only one working out for me between bunch of "just copy this formula and press enter" on many google articles

[@Computergaga]

You're welcome. Glad it helped.

[@bellarosa009]

Great video!! I’m so glad that you mentioned how to stop the formula from running by selecting the calculation option. I learned something new - always a good thing! 😁

[@Computergaga]

Thank you, Bella 😊

[@Alexander-mg4pr]

Thank you! But how do you prevent repeated names?

[@dpot2231]

Assign each name a random number using =rand() for the cell next to the name and then sort by that

[@jimmyxrs]

Simple, clear and straight to the point. Thanks

[@adonakis]

Hi...this is a big help. However, I can't figure out how to add two more columns. I have a 11 members, that i need to schedule 3 people each Sunday for Church duty. How can I randomly pick 3 people per week, and have 3 names per row.

[@gavinstokes]

SO many videos, over complicate this simple question. Thanks for that easy and clear to follow

[@Computergaga]

Thank you Gavin.

[@Lomaxient]

You are the daddy! There's a couple of others that go deep, but you just keep knocking 'em out of the park with simple, powerful tricks.

[@Computergaga]

Thank you Lomax. Much appreciated.

[@Lomaxient]

@@Computergaga - BTW, have you got a video about restricting the calculations of volitile functions like Rand() or RandBetween()? Basically I'm trying to create a sheet with lots of randomly generated numbers without them changing every time I click on a different cell. Essentially I'm after a button that pings out a bunch of random numbers. I know you can turn of automatic calculations in the formulas tab, but that stops every other calculation too.

[@Computergaga]

Hi Lomax, I don't think I do and you are right in what you say. You can create a button to run the calculations, but you won't be able to (I believe) stop just those cells calculating automatically. So the best answer is probably not to use those formulas, and have the button run some macro code to do the rand and randbetween instead.

[@Lomaxient]

@@Computergaga - Turns out it was easier to get sorted than I expected. So here is the solution for turning off random calculations in Excel 2010. 1) Under the file tab, select options>formulas. Enable iterative calculation and set the maximum iterations to 1. This allows you to generate a circular reference without getting a warning message. 2) Insert a Check Box from the Developer Tab. 3) Right click on the Check Box & select Format Control>Control and then link the Check Box to a cell of your choice (A1 in this example). So in the “Cell Link” box, simply type “$A$1”. 4) In cell B1 type this “=IF(A1,RANDBETWEEN(1,100),B1)” THAT’S IT! Now whenever the Check Box is selected the value of A1 will be TRUE and the RANDBETWEEN function in Cell B1 will trigger. If the Check Box is not selected, the value of A1 will be FALSE and the value of B1 will reference itself (the last randomly generated number).

[@Computergaga]

Nice work Lomax.

[@wyldfire77]

This was exactly what I needed for my hobby. Thank you ComputerGaga For anyone who just wants to copy-paste the string, it's =INDEX(X#:Y#,RANDBETWEEN(1, Z)) where Z equals Y# minus X#. So if Index(A4:A19) then Z = 15 (19-4)

[@HaggenKennedy]

It's actually Z = [Y - X] + 1. If you leave it only as "Y-X", you get one less cell. To illustrate, if you have 3 cells, and you do RandBetween with the former calculation (3-1 = 2) you would get "RandBetween (1,2)". Meaning you'd get only 2 cells (the first and the second) out of the 3 you used. Therefore, it must always be Z = Y - X + 1. E.g., if you use cells 5 through 10, that would be "RandBetween (1,6)", and so on and so forth.

[@fencejumpa2365]

thanks man, using my spreadsheet as flashcards and wanted to test myself out of order :D

[@Computergaga]

Awesome!!

[@RodrigoRodriguez-wl9vl]

come here to do the same

[@GP-zv9re]

Thank you for explaining this so simply! It was very easy to understand.

[@Computergaga]

You're very welcome! Thank you for your comments.

[@janeorourke6946]

Very helpful, thanks! Is it possible to pull more than one? For example pull 15 names from a list of 400 in one go?

[@Computergaga]

Thank you. Yes, you could use a formula like this =INDEX(Table1[Names],RANDARRAY(3,,1,ROWS(Table1[Names]),TRUE)) RANDARRAY produces a list of random row numbers to extract from. The number 3 in it takes three names. This can be changed to 15 or linked to a cell with a value.

[@oluwabunmiadebayo6996]

Thank you for this tutorial video. I really appreciate it. It is very useful for you.

[@Computergaga]

You're welcome.

[@emmajonsson5084]

Hi! Let's say I want to be able to add more values in column A and not be limited to values within certain cells, for example A2:A15, and still have it randomise one at a time, how would I do that? I'm also using Google spreadsheets if that's any help because this will be shared with others.

[@Healinginthesoul]

This helped me alot. Thank you so much. I wanted to know if I had two students with the same name and surname on the list how would one tackle that. If we were using unique ID for example s230 s232 and s230 and one needed to iterate through each item randomly... how could that be done . Thanks again

[@JaySharma-qf8ie]

Is there a way to weight particular elements of the list heavier than others?

[@HaggenKennedy]

Have you found the answer to this?

[@Jakneife]

Thanks for sharing this and making it so concise. Let's say I also had Random Age, Random Gender, Random Height, etc. Every time I hit F9, all the random variables refresh. I know how to create buttons and assign very basic macros to them. I tried a copy-paste macro and they all still refresh. Is there a way to isolate each category so only that refreshes when I push the respective button?

[@HaggenKennedy]

This! Has anyone found an answer to this yet?

[@biggirlgames5573]

Would I be able to use this to create a random-fill field in a mail merge? I want the "letter" to choose a random name from a list every time the letter is run. Does that make sense? I'm assigning something random to each recipient.

[@elainewilliams7796]

Great video! Thanks so much for the tutorial - works great! Is it possible to remove the name from the list after it has been chosen?

[@Computergaga]

Not with a formula. You would need a macro alternative for that Elaine. I have a blog post on using VBA for this - www.computergaga.com/blog/pick-names-at-random-from-a-list-excel-vba/ This does not remove the name that would just requrie an extra bit of code, but if interested this is a start.

[@gamer95z41]

Can you tell me how to get random names based on weighted probability such as the number of raffle tickets holding?

[@jagyachimp1987]

Sir for multiple columns (from Table ) am not able to apply the above formulation... Plz suggest how to do that. 🙏

[@40wattstudio41]

Just what I was looking for, thanks!

[@Computergaga]

You're welcome.

[@iWinsomeLosesome]

well explained and straight to the point. good video.

[@Computergaga]

Thank you 😊

[@TechTinkTronics]

Thanks for sharing! There are so many applications for this type function can apply.

[@Computergaga]

You're welcome. Thank you.

[@JonathanThompson1320]

Worked beautifully, thank you!

[@Computergaga]

You're welcome Jonathan.

[@joygimbel7760]

This is super helpful!

[@HomeWithMyBookshelf]

Let's say I put an x beside some names in column B, and I want the function to only pull from that selection, how would I do that?

[@wayneedmondson1065]

Hi Alan.. just came across this video. Nice technique to pick a name at random from a list. Thanks for the insights. Thumbs up!

[@Computergaga]

Thank you Wayne.

[@lala_ashalay]

So helpful, thank you!

[@Computergaga]

You're welcome. No worries!

[@RalucaSerban]

I have a dynamic list, meaning column A always changes in size(+- rows), how can I make it look at just what it's populated?

[@kauriforest2188]

Just what I needed and explained it easily and quickly. Thanks.

[@Computergaga]

Thank you Kauri.

[@matthewcadman718]

Great video! Thanks 🙂

[@Computergaga]

Thanks, Matthew.

[@onsteam7488]

great tutorial!, how do we make the randbetween picks from a random list equally. means the total count number of names repeated should match each others? any idea?

[@Computergaga]

Thank you. So you want to randomise the list. I would generate random numbers in an adjacent column and sort them.

[@KrishanSingh-gz9op]

What if the names was also in the B Column, i.e. from A2:B13? Then what adjustments do we have to our formula to select a random value from A2:B13?

[@denisherforbes4095]

Wow. This was so helpful. I really appreciate this

[@Computergaga]

Great! Happy to help, Denisher.

[@SteveNichols77]

Do you know if there is a way to start on a name you wanted?

[@siddharthkapkar9168]

SIMPLE & BEST VIDEO...BUT WHAT ABOUT VALUES THAT ARE REPEATING AGAIN ?

[@Computergaga]

Thank you, Siddharth. In this example, there is only value so there would be no repeat. If you multiple, there are numerous methods available from RANDARRAY to creating rankings which I have done in a few of my videos on this channel.

[@siddharthkapkar9168]

Thanks noted buddy❤️

[@jlamping231]

How do you produce the random value if it is linked? It seems to only be generating the text version, even though where it is pulling from is hyperlinked.

[@atiluluyuce]

Hello Jacob, I'm looking for the answer to the same question, I wonder if you found an answer to your question?

[@brendonvanniekerk2140]

Thanks for the thorough tutorial !

[@Computergaga]

You're welcome, Brendon.

[@mikew2610]

Outstanding. Thank you in 2023.

[@Computergaga]

Thank you 😊

[@Phlebas]

Thanks! For silly nerdy reasons, I was trying to write an excel program that can generate a sort of pseudo-history. (Eg. King Oleg II ruled from 271 AD to 295 AD, was succeeded by Leonid, etc....) I was going to brute force it by generating a list of random numbers (taken from a website), assign each number to a name and then have a long if/then sort of setup. But I figured there had to be a more elegant way.

[@Computergaga]

You're welcome Phlebas. Thank you for watching.

[@adhirikahrabiou6591]

PLEASE HOW CAN I WITH PRECISION FOR EXAMPLE IN OUR COUNTRY WE HAVE FOURTH DAYS PLAYING GAME PROGAMME EVERY DAY MY GENERATOR?

[@owenjohnson4754]

According to the comments this works! But could you make one for html in something like sublime?

[@shorterericar]

Super Easy Thank you!!!!

[@Computergaga]

You're welcome, Erica.

[@mikehart44]

good video, One question... how can you pick random names, lets say 5, without duplicates. preferably using randbetween

[@Computergaga]

+Michael Hart Sounds like a task better suited to VBA. For a formula we would need 5 so one for each name with some conditional elements to ensure they don't draw the same name twice.

[@mikehart44]

+Computergaga im not familiar with vba at all. can you do another video explaning it?

[@Computergaga]

+Michael Hart As requested a video explaining how to extract 5 or any number of unique and random names from a list. Excel VBA code is also provided via the link to be copied and used kzbin.info/www/bejne/rJjVaqV6rJamiLM

[@tafur1110]

Soooooo helpful thank you so much

[@Computergaga]

Glad it helped, Mike.

[@mohdkhalid7163]

hello, what will be the formula if its a row instead of a column

[@Computergaga]

If the names are in a row, range A2:E2. The formula would be =INDEX(A2:E2,1,RANDBETWEEN(1,5))

[@srikinkonchandradas3986]

If Don't want a same name twice.then what should I do?

[@bluefeather8989]

I have a list that has numbers 101-136. some of the cells contain 0. How do i use this formula to random select a number grater than 0?

[@YouTubeJulien]

very useful. tx :-)

[@Computergaga]

You're very welcome Julien.

[@gomair]

This video saved the day! =)

[@Computergaga]

Excellent! 👍

[@nikoneathery5052]

Is there any way to do this without picking a name more than ounce?

[@Computergaga]

I have a video doing this with VBA - kzbin.info/www/bejne/rJjVaqV6rJamiLM

[@marceloribeirosimoes8959]

Hi Computergaga !!! Thanks for you really helpful video!!!!!! My question would be, "how to have an "exercise column" and you want to use your instructions to have a different (random) exercise on each line (so they need never repeat the same exercise)???" I mean, each column is a weekday. Each line represents a different exercise from a list "range". So, each line cell must have to have an exercise BUT they have to be a different exercise on the same day (same column). COLUMNS NAMES = Monday Tuesday Wednesday Thursday Friday Saturday Sunday ROWS NAMES = Jump Rope Seats Squat Pushups List of exercises (the exercises names origin) Now, randomize them into the DAYS. Do you know how to do that?

[@Computergaga]

I think I would use a similar technique to the one I used in my lottery number generator video. Randomly generate a number. Use COUNTIF and RANK to make them unique integers. Then use VLOOKUPs in all the weekday cells to pull over an exercise.

[@kalimbhatti2381]

Very nice, extremely helpful. I was looking into software that would help me, we have a drug testing service and need to select randomly selected drug testing candidates here at All Better Lab in Harrisburg. Your information is priceless. Thank you!

[@Computergaga]

You're very welcome.

[@kelseyrussell9919]

Is there a way to use this formula and make sure that all 12 names are cycled through before repeating them?

[@Computergaga]

That might be easier using a VBA solution. Or just randomise the 12, so that it changes the order for the shifts or whatever you are using it for. And then do again. So it is not really extracting 4 at a time or whatever, but you could get that effect.

[@KidTxxic]

Thank you

[@Computergaga]

You're welcome

[@AmitSharma-iv6qf]

BIG THANKS ! VERY USEFUL!

[@Computergaga]

You're welcome Amit, thank you.

[@valereneganadores3594]

Thanks, very useful!

[@Computergaga]

Great! You're welcome, Valerene.

[@shangrilabank-xz4qh]

Sometime I get a '0' when i use this in the option. Can you please explain why this happens?

[@Computergaga]

A 0 sounds like it is return a value form a blank cell. I would check the range used in the formula.

[@peezmol]

Thanks, one of us had to deliver the presentation and no one wants to do it, so this will help🤣

[@sathp591]

I have a list of 35 and they are reference no.s such as A0055, A0058 etc. I want to pick 20 out of 35 at random. You formula works, but some of the cells return #REF. I change the values again, and another cell gives #REF??? Can you pls help me.

[@Computergaga]

Hi Sath, This link show how to extract multiples from a list using Excel VBA - kzbin.info/www/bejne/rJjVaqV6rJamiLM This may provide a better solution. The error message you are currently getting indicates that a cell reference s not valid.

[@shayanusman8207]

Amazing

[@richardnixon3722]

What if you don't want them to repeat?

[@rudranarayan2472]

if i put number, name should be come automatically in google sheet, how ?

[@s.kgymfitness9842]

Thanks man

[@Computergaga]

You're welcome.

[@TheBridgeofNEFL]

Great video! Thanks for the help!

[@Computergaga]

Thanks Stephanie, your welcome.

[@xitcode]

Thank you My dear, It was a great help.

[@Computergaga]

Glad it helped!

[@washim369]

thank you so much,

[@Computergaga]

Welcome 😊

[@rajakaz4075]

Thanks mate!

[@Computergaga]

You're welcome. Thank you.

[@manymay381]

Thanks

[@Computergaga]

You're welcome.

[@IZA3]

So helpful. 🫶🏻🫶🏻🫶🏻

[@Computergaga]

Great! 😊

[@futurehr3545]

sir I have to adjust the attendance random like a,,,,,b,,,c,,,g how

[@cinos1985]

I followed to the letter, instead of a random name* it says "#ERROR!" P.S. I did it in Google Sheets, and I was trying to use it as a random game picker?

[@maxwellpedersen9453]

is there any possible way to do this for multiple cells at once without using the same name twice in a row?

[@Computergaga]

I have this video on picking multiple names from a list at random if that was what you need - kzbin.info/www/bejne/rJjVaqV6rJamiLM. Choose how many and run the random pick.

[@charmedforever4245]

Having same problem. Did you find the solution

[@DiaborMagics]

Thank you, but can you help me with the column and an error I'm getting? You see, I use the Dutch version so I had to look up the function translations and I have an ID column and an actual names column. The ID column is to remain untouched, but I want to pull names from the name column. My name clumn is B. I have a header and then 16 names, for a total of 17 cells in it. This is my formula and it's not taking it: =INDEX(Tabel1[Starter Extender];ASELECTTUSSEN(1;16);Tabel1[Starter Extender]) It says there is an incorrect value?

[@Computergaga]

I would remove the last bit of the formula and just use =INDEX(Tabel1[Starter Extender];ASELECTTUSSEN(1;16))

[@sergioventura260]

Nice video! very helpful.

[@Computergaga]

Thanks Sergio.

[@Saifulislam-fy9tg]

thanks bro for uploading this video we learn from this video how work excel easily and it is use random Excel formula

[@Computergaga]

You're welcome Saiful.

[@mariam-lm3kz]

Thank you its really useful

[@Computergaga]

Great to hear Mariam, thanks.

[@keern5824]

how can we select 5 out of 13 sir??

[@YouCubeParadigm]

What would I do if I wanted to select, say, 5 different names at once, so the "Random Name From List" would show for instance "Gita Patel David Jackson Karen Christie Bob Liston Harry Johnson" ? Naturally, in my case it would not be names, but rather notations used, but I need them randomly given to me. Thank you in advance

[@Computergaga]

This video shows how to select multiple names from a list at once kzbin.info/www/bejne/rJjVaqV6rJamiLM

[@HamFrensh]

thanks

[@Computergaga]

You're welcome!

[@garydunham335]

If I were to pick a random 3 names from the list, how could I edit the function so no names repeat?

[@Computergaga]

I have a video on that here Gary - kzbin.info/www/bejne/rJjVaqV6rJamiLM

[@gerardvaneggermond9967]

Hello, your video is very hard to see maybe you can play this formula through to me, it might help me with the problem I am facing.

[@3040ZM]

What if i want to pick from two lists.

[@xitcode]

=INDEX(A2:A13,RANDBETWEEN(1,12))

[@gauravtiwari7724]

please tell how do we accomplish this same task without a single chance of any name getting repeated. please tellllll.

[@Computergaga]

I have a video doing this with VBA - kzbin.info/www/bejne/rJjVaqV6rJamiLM To do it with formulas would require more helper columns to test, and too much. More a programming task.

[@nunomarques7782]

It doesnt work for me dunno why...

[@farhadhossain7253]

nice video

[@Computergaga]

Thank you, Farhad.

[@xornedge8204]

I keep trying to make a bunch of cells like this to choose Random names but then excel puts a 0 in red. Can someone help?

[@Computergaga]

I have a video on picking multiple names from a list - kzbin.info/www/bejne/rJjVaqV6rJamiLM

[@lb4397]

Thanks so much!! :-)

[@Computergaga]

My pleasure 👍

[@wishing-golden6377]

I used this as a random topic picker cause the online roulette didn't work on my work computer

[@mortiezgaming4838]

Nice

[@Computergaga]

Thank you Kayamat.

[@wilhelmschoeman2166]

What do you do when you have a Colom you need to select. Not all selections are as simple as this one. You need to explain the complete formula!!!

[@Computergaga]

The best way when you have a column to select is to format the range as a table. Then use the table reference to select the column. That is not always the way I show in my videos, because I want to demonstrate different techniques as not all spreadsheet set up by people are the same. But tables are best.

[@mitulsuchak2122]

Say I wanted to pick a random name based on how many years the employee has worked at the company e.g. generate a random employee who has been at the company for less the 2 years, and print their name, obviously assuming that data is part of the population

[@DutchFrenzy]

It doesn't work for me. I keep getting problems with the formula

[@Computergaga]

Double check what you have compared to the video Fezury. There must be something different.

[@DutchFrenzy]

I found the solution. Apparently I cannot use a comma, but I have to use ;

[@Computergaga]

Ah yes. In most of Europe the semi colon is used because the comma is used as a decimal separator.

[@DutchFrenzy]

Yes that explains a lot. Thanks anyways

[@Computergaga]

You're welcome, thanks Fezury.

[@rachelpalfrey6419]

But what if you have more than one column in your table? You skipped over that bit

[@Computergaga]

Once a name is returned. Lookup formulas (VLOOKUP, INDEX/MATCH) could be used to return data from other columns.

[@fqras]

And now for apple numbers :D

[@xxrxinbow_teaaxx2364]

I watch that because I was trying to pick a name

[@YoDaPro]

Kleiner Tipp für alle die Excell auf Deutsch haben. Dort heisst es "Zufallsbereich" ;).

[@cinos1985]

nevermind I used the wrong "as you called it - Bracket"

[@Computergaga]

Good work fixing your formula Fonzy.

[@AS-cs9gi]

here after animal crossing new horizons abandoned randomized dream towns