+ Reply to Thread
Results 1 to 16 of 16

pick names at random

  1. #1
    Registered User
    Join Date
    02-18-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    36

    pick names at random

    Im trying to make a pick names at random from a list on excel Column A to C. but I don't want them to repeat once they are chosen how do I do that? Or can it be done? I have this formula
    =INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)
    and also the column B shouldn't be selected by employee more than 2 people. what formula I used to stop selecting from more that 2 names on column B form column A
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: pick names at random

    I'd use a couple helper columns
    One to create random non repeating numbers. =RAND()
    And one to RANK those random numbers =RANK()

    Then index the original data using the rank column.


    I don't understand the other question regarding column B.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-18-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: pick names at random

    Hello,
    the individuals in column B, Peer 1, should not be chosen more than twice by column A. What is the formula to use

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: pick names at random

    Well, since they already can't be chosen more than once, that will take care of not being chosen more than twice.

    Sorry, I still don't understand the 2nd question.

  5. #5
    Registered User
    Join Date
    02-18-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: pick names at random

    Hello,

    let me clarify- Column A is the group of employees who wants to do their evaluation by selecting their buddies.
    Column B are the buddies names who are chosen by Column A
    but their is a restriction (no more than two employees can choose the same name ) However, column A does not know who the person they chose has been requested more than twice by other people in Column A.
    IS there a formula that be created to identify if a Column A person has been selected more than twice in column B.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: pick names at random

    I don't think you can do this with formulas.
    Excluding specific items from the pool to randomly select from seems like a task for VBA.

  7. #7
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: pick names at random

    You can do random excluding x values with formulas. Give me a moment and ill post something (have to dig it up).
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  8. #8
    Registered User
    Join Date
    02-18-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: pick names at random

    or how to identify duplicates more than two with same name (like a cell color change or something similar)

  9. #9
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: pick names at random

    Here is my sample file with randbetween excluding x numbers. Its got plenty of comments/explanation along with it for those who care to learn.

    This doesnt directly address the OP's question, which is a bit confusing as they are asking for multiple things that seem to conflict. I need to clarify some things for myself to be able to help.

    Please explain your needs as bullet points, it may help us better understand each goal. As I understand it now:
    • You want to randomly pick a name (you never said from which list or into which column)
    • You want the random selection to eliminate previous selections as an option for following random choices
    • You want people to make a selection and limit that selection to a max of 2 total?

    The confusing part of this is you cannot randomly chose AND have people pick. You havent been clear about where the choice is made and where the random selection happens. They CANT be in the same place, thats like saying its cold and hot at the same time, hence peoples confusion.

    Please explain your goals more clearly. If it helps to explain, comment your sample file to point us to what you are mentioning. When you explain use references in the file so we can follow (IE: "I have this formula =formulax" doesnt help us, "I have formulax in cell A2, filled down to A10..." helps us follow what you describe).
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-18-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: pick names at random

    Hello,
    I have three columns in the spreadsheet (A, B, C). I got the formula for how to do the random pick without duplicate on Column C. now I need to know how to identify the duplicates more than two same name in Column B.
    I hope this will explain very easily.
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: pick names at random

    To prevent more than 2 of the same entry in Column B use this formula in a custom data validation rule in B2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Highlight B2:B11 (or whatever the last row in B is you need to check) and click data validation, it will ask if you want to expand the selection, say yes, then hit ok on the validation window to close it. Any existing duplicates beyond 2 entries will remain, any new entries that result in more than 2 duplicates in B will fail and not allow the entry with a warning dialog (customize it via data validation rule).

    Also you say your random works. What does it need to exclude from (only column C random picks, or also the entries in column B)?

    For example should the 4th row be allowed to do this (Thomas in B and C):

    cap13213.PNG

    EDIT: I also noticed from your random pick that column A can = column C. In its current rendition you could end up with the same name in all 3 columns (which I presume isnt valid either). So you can end up with A and B being the same, A and C being the same, B and C being the same or A,B,C all being the same. Is that ok?
    Last edited by Zer0Cool; 01-09-2018 at 12:22 PM.

  12. #12
    Registered User
    Join Date
    02-18-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: pick names at random

    Hi,
    I tried the rule to identify duplicate more than two entry in column B. but not working. can you help me to insert in the spreadsheet. also the random in column C should pick from Colum A. why the random pick in column C is always changing?
    yes you were right, the random pick should should validate the column A and B. do not duplicate the same name in Column C
    Last edited by ancherilvm; 01-09-2018 at 01:10 PM.

  13. #13
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: pick names at random

    I will post a sample in a bit with the data validation. (attached has data validation in it working as far as I can tell)

    So if I am understanding correct the random name should follow these rules:
    • Be sourced from the names in Col A
    • Never be the same as the name in Col A on the same row
    • Never be the same as the name in Col B on the same row
    • Never be the same as any previous randomly generated names in Col C

    I can attempt this, but my gut tells me (without doing the math) that that combination opens the possibility of exhausting all options for a random name towards the bottom of the list. IE: lets say "John" is in A2, was only used 1 time in B and hasn't been randomly chosen yet in C. We are on the last row, A <> John, B is John (2nd time) and for C the only name not previously randomly selected is John. We are now in a hypothetical situation in which we cant randomly pick the only name left. What would you want to do if that happens?
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: pick names at random

    I knew this going in, but my random exclude x formula does not scale well (as you need to add additional not sets to it for each value to exclude). This is ok for a small handful but I would say 10 or more values to exclude becomes an issue. I am currently searching for a way to do an array vs array exclude (in which the range of values and exclude range can be different sizes) which may not be possible. At some point I may give up trying with a formula and consider using a macro to accomplish the random generation.

    EDIT: unfortunately I dont have the time to continue researching this. You could adapt my formula as is to the sample, but any more rows and it will not be feasible. If someone can do an array to array comparison instead (something like A2:A10<>B2:B5) and get it to return an array of the values from A that <> any value in B, then it will be easy to adapt my function for randomizing names. It may be that VBA is easier to derive a solution from than a formula in this case.
    Last edited by Zer0Cool; 01-09-2018 at 02:35 PM.

  15. #15
    Registered User
    Join Date
    02-18-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: pick names at random

    Hello, thank you for the formula for the random pick and avoid duplicate. but there is a problem, every time I close the file and reopen the random values changes. is there any way to protect stop changing the values after randomly select.

  16. #16
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: pick names at random

    No not in a formula. To elaborate its not only closing and reopening (presuming you have auto calculation on), its any time you make a change to the files contents (changing any cell value) and/or recalc the sheet (F9) The only way to prevent it would to be removing the formulas by pasting the results over them, in which case everything is then static and no longer randomly does anything ever again unless you enter the formulas back in.

    Otherwise, if you want to generate stuff "on demand" then you need a macro. Press a button, run the macro, generate your desired output, place it in cells...rinse/repeat as need be.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to pick 3 random names from a list
    By philenty in forum Excel General
    Replies: 3
    Last Post: 11-15-2016, 09:00 AM
  2. pick a name out of list of 20 names without repeating names picked
    By gammoneer2b in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-06-2014, 10:14 AM
  3. uner what creteria random formulae pick up next random value?
    By roofi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-16-2014, 12:55 AM
  4. Pick random names from list
    By ThomB in forum Excel General
    Replies: 3
    Last Post: 04-08-2013, 10:55 AM
  5. random pick names from a list
    By jladika in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-06-2011, 05:31 PM
  6. Replies: 8
    Last Post: 08-14-2009, 11:22 AM
  7. random pick names from the list
    By jinvictor in forum Excel General
    Replies: 1
    Last Post: 06-26-2006, 10:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1