Pick a Name at Random from a List - Excel Formula 

Thank you, Bella 😊

You're welcome.

Thank you Kauri.

You're welcome Jonathan.

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

Thank you Gavin.

Thank you Wayne.

You're welcome. Thank you.

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.

Great! Happy to help, Denisher.

You're welcome, Brendon.

You're welcome Amit, thank you.

Thank you very much 👍

Thanks Stephanie, your welcome.

You're very welcome Julien.

You're welcome.

Thank you 😊

You're welcome. Glad it helped.

Thanks Sergio.

Great! You're welcome, Valerene.

Awesome!!

come here to do the same

Thanks, Matthew.

You're welcome Saiful.

Glad it helped!

Thank you Lomax. Much appreciated.

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

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.

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

Nice work Lomax.

You're welcome, Erica.

Great to hear Mariam, thanks.

Excellent! 👍

Glad it helped, Mike.

My pleasure 👍

You're welcome Phlebas. Thank you for watching.

You're welcome. Thank you.

Thank you 😊

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.

Welcome 😊

You're welcome.

You're welcome

This! Has anyone found an answer to this yet?

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

You're very welcome.

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

You're welcome.

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.

Thank you, Farhad.

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.

Thank you Kayamat.

Have you found the answer to this?

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

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.

Thanks noted buddy❤️

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

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.

Great! 😊

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

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

+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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vgr9rDw1sRQ.html

This video shows how to select multiple names from a list at once ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vgr9rDw1sRQ.html

Hi Sath, This link show how to extract multiples from a list using Excel VBA - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vgr9rDw1sRQ.html This may provide a better solution. The error message you are currently getting indicates that a cell reference s not valid.

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

Of course! Various techniques including single formula and multiple helper columnd approaches. One formula is the below. This works on the idea of a list of names in a Name column of a Table named Table 1. It also returns 3 unique names. =TAKE(SORT(HSTACK(Table1[Name],RANDARRAY(ROWS(Table1[Name]),1,0,1,FALSE))),3,1)

I have a video doing this with VBA - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vgr9rDw1sRQ.html

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

I have this video on picking multiple names from a list at random if that was what you need - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vgr9rDw1sRQ.html. Choose how many and run the random pick.

Having same problem. Did you find the solution

I have a video on that here Gary - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vgr9rDw1sRQ.html

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.

I have a video doing this with VBA - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vgr9rDw1sRQ.html To do it with formulas would require more helper columns to test, and too much. More a programming task.

I have a video on picking multiple names from a list - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vgr9rDw1sRQ.html

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

Good work fixing your formula Fonzy.

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

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

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

Yes that explains a lot. Thanks anyways

You're welcome, thanks Fezury.