Random Name from List in Google Sheets || Select Random Number

No video

Random Name from List in Google Sheets || Select Random Number

Рет қаралды 11,183

Жыл бұрын

How to pick a random name or item from a very long list of names or another database list. Use formulas and functions to pick a random item from a very long list.
If your list has over 30,000 rows and you need to select RANDOM names of customers, employees, or items, you need to make sure you do so objectively. That is where the RANDBETWEEN function comes to play. Additionally, you can also use the COUNTA function to count the rows, which may be updated frequently.
Finally, the INDEX function will help you pull the randomized row information (such as the data item selected) and the VLOOKUP function will pull additional data for the winning name or item (such as phone numbers, dob, email addresses or anything else).
-------LINKS---------- --
All the Google Sheets shortcuts for Mac & PC:
support.google...
Google Sheet List of Functions:
support.google...
#akaakidesign #akistepinska

Пікірлер: 18

@irmareyes9805 9 ай бұрын

Awesome video and information! I was able to create an auto meal planner for each day of the week using these formulas :)

@akistepinska 9 ай бұрын

That is such a great use for this function @irmareyes9805! Thank you for sharing ( ＾◡＾)っ

@TheQuizzyMind 3 ай бұрын

Very good tutorial. Solved my problems 👏👏🙂🙂

@akistepinska 3 ай бұрын

Thank you so much @TheQuizzyMind!. 🍀

@matacra 2 ай бұрын

are the formulas always different to one another? or just doesnt matter cuz its a big sample size? im doing this with 4 cells, i want them to not repeat, i dont know how to make it work the way i wanted, anyways great video, really helped me even if its not exactly the way i want

@Stencilpirate 11 ай бұрын

Great video! I was able to follow it al lthe way, is there a way to trigger the pick function without needing to edit the sheet? like a button needing to be clicked or something

@akistepinska 11 ай бұрын

Thanks @Stencilpirate, You could create a function or a button that would do that, but it . Here is a forum discussion and code: An easier solution is to make like typing a letter or deleting a letter in any cell or refreshing the sheet. Manual but still effective. ( ＾◡＾)っ Cheers!

@pincer2885 3 ай бұрын

is there a way to make it so that the randomizer only changes when you click a button or change a cell?

@akistepinska 3 ай бұрын

Hi @princer2885, You would have to either create a Script or a Macro, which can, unfortunately, be complex. Here are couple of forum discussions that I found that talks about how to do it. stackoverflow.com/questions/32290795/run-function-on-button-click-as-the-creator-user-google-spreadsheet shorturl.at/vbb1P www.reddit.com/r/excel/comments/rxgpgk/run_formula_when_button_pressed/ Good Luck🍀

@zackyep Ай бұрын

idk if this was what you were looking for but i just put a checkbox above and used =if([ABOVE CELL]=true,index('[RANGE],RANDBETWEEN(1,counta('[RANGE]))))

@user-lk6on8gl3c 5 ай бұрын

Is there a way to ensure that each name is used in the randomized list once? I am using short lists of 10-12 words and find that when randomizing, the list is doubling up on some words and not including others.

@akistepinska 5 ай бұрын

Hi, I see the problem. You would have to modify the formula as follows: 1. Enter the Rand formula in B2, and copy it down the column: 2. Put the below formula in C2 to extract a random value from column A: 3. Copy the above formula to as many cells as you want selected. Here is a much better step-by-step explanation. Scroll down to the "How to randomly select in Excel without duplicates" section: www.ablebits.com/office-addins-blog/excel-random-selection-sample/?AVGAFFILIATE=55741&__c=1 Good Luck🍀

@dakotahedwards1670 Жыл бұрын

How can you keep track of the winners?

@akistepinska Жыл бұрын

Hi @dakotahedwards1670, There are two ways you can lock or keep track of the winners: 1. You can either COPY & PASTE VALUES to another place or Tab; or 2. Take a screenshot Here is a Google Sheets response; they also mention using scripts, but that is a more advanced solution. support.google.com/docs/thread/8555804/randbetween-result-convert-into-static-numbers?hl=en Hope that helps. Good Luck!🍀

@cathymathias9805 Жыл бұрын

it's hard to understand, too much information at once. you have to be specific. I couldn't get anything at all

@akistepinska Жыл бұрын

Hello @cathymathas9805 To generate a random name using the RANDBETWEEN formula, follow these steps. First, use the RANDBETWEEN formula to obtain a random integer between the specified numbers in column A (A2:A). If you want to keep the list dynamic, you may need to add a COUNTA function to define the last row. However, if you want to retrieve an actual name as a result, you need to wrap this function in the INDEX formula. The INDEX function allows you to retrieve the value of a specified cell, which we found using the RANDBETWEEN formula. And if you also want to include additional information, such as an email address, phone number, or other data for the randomly selected name, you can use the VLOOKUP function. Here is the final formula you can use to generate a random name: =INDEX(A2:A, RANDBETWEEN(1, COUNTA(A2:A))) All these concepts are intermediate level and just take a little practice. Testing it out a few times will make everything clearer with time. Cheers, & good luck! 🍀

@cathymathias9805 Жыл бұрын

Thank you very much, now it's clear. Thanks once again

@akistepinska Жыл бұрын

@@cathymathias9805 You are very welcome ;-)