Pick Names at Random from a List

No video

Pick Names at Random from a List - Excel VBA

Рет қаралды 40,272

8 жыл бұрын

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

Пікірлер: 52

@brahimakchfour 9 ай бұрын

Hello, can you please tell me the mouse icon, how can I add it

@TechTinkTronics 4 жыл бұрын

Thanks for sharing! A little modification and I could have a VBA lottery!

@Computergaga 4 жыл бұрын

Thank you.

@tonykaram4735 5 жыл бұрын

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.

@Computergaga 5 жыл бұрын

Sure, sounds good.

@adolfolopezaraya2108 Жыл бұрын

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.

@DavidFlores-mc6wv 4 жыл бұрын

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?

@Computergaga 4 жыл бұрын

Great to hear, David. Different group of numbers? You can specify the number of names from the cell.

@Gayatri204 3 жыл бұрын

Thank you Sir,

@Computergaga 3 жыл бұрын

You're very welcome, Shruthi.

@Gayatri204 3 жыл бұрын

Sir how to connect all columns on this please help on this

@piotrwalendzik6716 4 жыл бұрын

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

@emilygiguere3420 2 жыл бұрын

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?

@mikehart44 8 жыл бұрын

also if i have more than one sheet in the workbook, do i have to specify "counta(range(!Sheet4.A:A))"

@shanollah 4 жыл бұрын

I dont see an "A" icon at the top portion of my sheet. What is the command formula for the VBA to be useful?

@AjetovsKi 3 жыл бұрын

This is really nice. I was wondering tho, if it's possible to do this without VBA...using helper columns & array formulas?

@Computergaga 3 жыл бұрын

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 - kzfaq.info/get/bejne/jdijodKd1czJmmw.html This can be improved in modern versions to get multiple names.

@Paul-ly1pw 5 жыл бұрын

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?

@Computergaga 5 жыл бұрын

I guess so, but I don't have the code for that.

@marzellesalvo9579 5 жыл бұрын

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?

@Computergaga 5 жыл бұрын

For sure. Though I don't have the code to do this to hand.

@mrundiemuncher 5 жыл бұрын

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?

@Computergaga 5 жыл бұрын

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.

@mrundiemuncher 5 жыл бұрын

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

@Computergaga 5 жыл бұрын

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.

@mikehart44 8 жыл бұрын

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.

@Computergaga 8 жыл бұрын

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

@sheldonharrish3857 6 жыл бұрын

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

@samanthascott6933 3 жыл бұрын

How can I delete the row that the name has been chosen from? Is this possible in this sequence? Thanks!

@samanthascott6933 3 жыл бұрын

Nevermind! I just adapted it to check the row that I already have the chosen names on. Thank you!!!

@leonardoduarte7647 3 жыл бұрын

@@samanthascott6933 Hi trying, do the same, but I am a newbie in VBA could you please share your modified code with me?

@samanthascott6933 3 жыл бұрын

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

@Syrkrasi 7 жыл бұрын

Great info. Is there a way to retrieve the data from another sheet?

@Computergaga 7 жыл бұрын

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

@Syrkrasi 7 жыл бұрын

Thanks!! this worked like a charm

@SheridanWhiteside 3 жыл бұрын

Computergaga I typed “NoOfNames=Application.CountA Worksheets (“Sheet2”).Range (“A:A”)-1” but the syntax is wrong please help? Thank you.

@siddhkishan 6 жыл бұрын

How can I setup required number against all listed name and all name picked by automatically

@Computergaga 6 жыл бұрын

Sorry, I don't understand your question.

@Segaco4 7 жыл бұрын

Is there a way to make the results appear on different cells? i.e. A1, B4, H3

@Computergaga 7 жыл бұрын

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.

@codymak3678 4 жыл бұрын

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?

@codymak3678 4 жыл бұрын

I understand now, thank you!

@NateProctor 3 жыл бұрын

@@codymak3678 I'm having the same issue. What was your solution?

@NateProctor 3 жыл бұрын

Never mind. Solved it :)

@halameshmosh4687 5 жыл бұрын

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.

@Computergaga 5 жыл бұрын

Where the Cells object is used in the code, it references column 1 aka A:A. This needs to be changed to 2.

@halameshmosh4687 5 жыл бұрын

thank you a lot for replaying .the problem fixed

@Computergaga 5 жыл бұрын

Excellent!