Pick Names at Random from a List - Excel VBA
HTML-код
- Опубликовано: 8 фев 2025
- Excel macro to pick names at random from a list. This video tutorial looks at using Excel VBA to generate a list of random and unique names from another list.
A user specifies the number of names they want picked at random from a list. The macro then returns the multiple names to the spreadsheet. All names are unique and not returned more than once.
The tutorial demonstrates many useful VBA skills such as array variables, Do and For loops and an If statement.
The VBA code can be found at the link below. This code can be copied and adapted to your needs.
www.computergag...
If you want to learn Excel VBA, take this online training course
bit.ly/37XSKfZ
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
This is a very usefull, thank for sharing. But I would like to create a list as you have done, but instead of it choosing products from the list according to a random probability, I would like the products to be chosen according to their probability of occurrence. Could you help me with this? thanks in advance.
This is great stuff. I don't have deep exposure to VBA programming but your explanation and approach are pretty informative. thanks. I just want your opinion on whether we can use another way to avoid choosing an already selected name in the array, such as creating an array of random numbers against each of the names in the list from A2 to A16. then we can sort them and start populating the array by either ascending or descending order. For larger size arrays, I believe this may require less computational resources.
Sure, sounds good.
Hello, can you please tell me the mouse icon, how can I add it
Hey this code was a great start for my project! I am building a cycle count location generator and this code allowed me to randomly select x number of locations to count. I have added code to input the date to the right of the location in the master list. is there a way to now avoid choosing this location again the next time i run the macro?
This is very helpful to what I need. The only other thing I could really use is a way for it to pick a different group of numbers?
Great to hear, David. Different group of numbers? You can specify the number of names from the cell.
Thanks for sharing! A little modification and I could have a VBA lottery!
Thank you.
Thank you Sir,
You're very welcome, Shruthi.
Sir how to connect all columns on this please help on this
This is really nice. I was wondering tho, if it's possible to do this without VBA...using helper columns & array formulas?
Thank you. With array formulas for sure. Here is a video of mine from back in 2015 using a formula to get a random name - ruclips.net/video/Wrsnolpfej8/видео.html
This can be improved in modern versions to get multiple names.
Hi great one really needed that ,can you help me I need more then one result , like you have in d6 I would need 6 more o try but lost
Is there a way to take out the how many option and instead have a list of cells covering an area from column B to M (eg B3, B12, C5, D7, E4, E9 etc) where, on the press of a button, those cells are infilled with the random names?
I guess so, but I don't have the code for that.
I dont see an "A" icon at the top portion of my sheet. What is the command formula for the VBA to be useful?
Great info. Is there a way to retrieve the data from another sheet?
Yes absolutely Syr. The procedure would be the same as in the video. The only exception being that when we reference the names written in column A, we would have the reference the other sheet i.e. Worksheets("Sheet2").Range("A:A")
Thanks!! this worked like a charm
Computergaga I typed “NoOfNames=Application.CountA Worksheets (“Sheet2”).Range (“A:A”)-1” but the syntax is wrong please help? Thank you.
How can I delete the row that the name has been chosen from? Is this possible in this sequence?
Thanks!
Nevermind! I just adapted it to check the row that I already have the chosen names on. Thank you!!!
@@samanthascott6933 Hi trying, do the same, but I am a newbie in VBA could you please share your modified code with me?
@@leonardoduarte7647 hi Leonard! My adaptation actually made it so that it went through both lists to choose names, which wasn’t helpful and caused repeats.
Are you using this for a raffle? If so I can give you some tips and tricks I picked up.
Sam
Very helpful video! I've just started learning VBA and I'm trying to create a form that would randomize names. Is it possible to have the results shown in a textbox instead of a cell?
For sure. Though I don't have the code to do this to hand.
I have try to copy your code, but there is a problem of "ReDim Names(1 To HowMany) 'Set the array size to how many "
I do not know why, how you help?
I understand now, thank you!
Hi. I know this comment is 3 yrs on lol..... but I found this and appreciate it. I was wondering how I can have the names removed from column A after it has been selected once. The reason being I'm trying to make it to be used for a competition drawing and need names that ave already been drawn removed. Is this possible?
Sure. Anything is possible with VBA :)
Upon drawing we will need a small piece of code to either delete, or maybe cut and paste the name to another safe area for reuse next competition.
@@Computergaga Thanks for your reply mate. I was not sure you would seeing this is a clip from 3yrs ago. I a m not the best with VBA and am about to enroll in a course to help me with Excel and VBA but that wont start for 8 weeks. I know I may be asking too much but is there any chance you could help with that code mate? I have now subscribed to your channel as your vids are very good and very appreciated.
Thanks MADAussie. I have my own online Excel VBA course here - www.udemy.com/excel-vba-course/?couponCode=VBA1099
I just looked at the code I used in the video (as you say it was 3 years ago) and it checks if the name has already been picked. So we wouldn't need to remove a name. If we did we could scrap the check if the name has been picked part of the code.
also if i have more than one sheet in the workbook, do i have to specify "counta(range(!Sheet4.A:A))"
Hi, I have the same one subscriber problem we want results from two different columns, for example, A: A and B: B,, with two macros. The problem is that the second macro is also picking the names from column A: A and not from B: B, although I modified the range in the second macro to B: B! please answer.
Where the Cells object is used in the code, it references column 1 aka A:A. This needs to be changed to 2.
thank you a lot for replaying .the problem fixed
Excellent!
Alan, Thank you this will be very helpful.
couple questions
can you set this macro up to preform numerous time within the same sheet?
can you preset the number of names in the list you're extracting the names from? for example list 1 has 9 names out of 15 cells but the other cells are not blank (numbers etc), list 2 has 14 names and so on.
+Michael Hart All of which you ask can be done. Loops can be set to repeat within a sheet or loop through the sheets of a book. We can also set it to find the range rather than set column A.
Message me if you want something specific set up.
Is there a way to set the range based on filtered results? Having the complete list is in column A, but based on another criteria in a separate column, when applying the filter, selecting from only the visible cells. For example, I have a list of staff and clients that participated in a fitness challenge, now I want to filter the list so that I can select 3 random winners that have completed 20 or more classes (filtered by the # of classes column) and that are clients (filtered by participant type column). In this instance, my total list changes from 111 to 66 people, but of course, the cells aren't 2 thru 67, they're still from 2 thru 112...
How can I setup required number against all listed name and all name picked by automatically
Sorry, I don't understand your question.
Is there a way to make the results appear on different cells?
i.e. A1, B4, H3
For sure. The loop at the end of the macro that prints the array to the worksheet would need editing. You would just need a way of coming up with these random cell ranges.
can this be used with numbers
Absolutely
I can't make that A button at the top to show.
ruclips.net/video/VVpTP9W39R0/видео.html