+ Reply to Thread
Results 1 to 4 of 4

Random Name picker based on 2 conditions

  1. #1
    Registered User
    Join Date
    01-14-2019
    Location
    Rotherham
    MS-Off Ver
    2017
    Posts
    2

    Random Name picker based on 2 conditions

    Hi,

    I am trying to create a spreadsheet that will randomly pick a name based on criterions.
    I have names in Column A, Subject in Column B and KS3 0r KS4 in Column C. I want to randomly pick a name from a student from each subject and each KS. For example, pick a random student from English in KS4.
    So far I have something that s the long way round and their must be a simpler way as if I continue this way then it will take some time.
    I think I need to do INDEX and MATCH but I am not an expert on these and needs some guidance.
    I will upload what we have done so far.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Random Name picker based on 2 conditions

    One way:

    =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$37)/(($B$2:$B$37=$E5)*($C$2:$C$37=F$4)),RANDBETWEEN(1,COUNTIFS($B:$B,$E5,$C:$C,F$4)))),"")

    copied across and down.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    01-14-2019
    Location
    Rotherham
    MS-Off Ver
    2017
    Posts
    2

    Re: Random Name picker based on 2 conditions

    Glen, This is amazing. It works. Thank you.

    I have tried to understand what is going off in the formula as I actually need to change it around due to the fact that the data I need to check is in different columns to the one that I uploaded. But I have tried and it keeps messing up. I clearly do not know enough to be able to manipulate the formula/functions.
    Please would you be able to amend the formula to do the same thing but with the columns in the order of the attachment I am attaching now.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Random Name picker based on 2 conditions

    =IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$2:$C$37)/(($B$2:$B$37=$H5)*($E$2:$E$37=I$4)),RANDBETWEEN(1,COUNTIFS($B:$B,$H5,$E:$E,I$4)))),"")

    will do it.

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Attached Files Attached Files

+ 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. create a random team based on conditions?
    By faithy in forum Outlook Formatting & Functions
    Replies: 5
    Last Post: 09-07-2016, 07:23 AM
  2. Random number based on other cell conditions
    By Allen_dulles in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2016, 09:20 AM
  3. [SOLVED] file-picker thinks it is a folder-picker if the folder picker runs first?
    By brucemc777 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-01-2016, 01:14 PM
  4. [SOLVED] Random 2 number picker
    By harry h in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-11-2014, 06:26 AM
  5. Name Picker (Random)
    By Andrew.Trevayne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2013, 07:25 AM
  6. A Random Student Name Picker
    By Transitory in forum Excel General
    Replies: 1
    Last Post: 09-29-2012, 12:59 AM
  7. Random team picker
    By Sheepkin_Coat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-03-2011, 04:01 PM

Tags for this Thread

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