+ Reply to Thread
Results 1 to 16 of 16

Pick a random person from a list of students

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    Wausau, Wisconisn
    MS-Off Ver
    Excel 2010
    Posts
    19

    Pick a random person from a list of students

    I would like to pick a random student from a list. I will paste in new students each week, so the range may increase or decrease each time. I have command button to bring up a from, and in the form another command button to generate a random student and display that person in a label box. One problem is that the students first name is in column A and last name is in column B.
    So for you Excel masters, can I get a vba code to put in the cmdGenerate button to randomly pick one student and display that student in the lblStudent box? Many thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Pick a random person from a list of students

    Hello jmeddaugh,

    Could you please upload a sample of your actual Workbook, with the Code?
    Last edited by Winon; 10-14-2013 at 06:41 PM.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Registered User
    Join Date
    07-09-2013
    Location
    Wausau, Wisconisn
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Pick a random person from a list of students

    Here it is, I apologize I added the first excel doc before saving..
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-09-2013
    Location
    Wausau, Wisconisn
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Pick a random person from a list of students

    Argh...I will upload a new one...my userform is still missing. Sorry


    OK, attached is now the Correct (macro-enabled) sheet:
    4 command buttons. I really need help with the one to generate a random student and also the "save student to speadsheet" in Sheet "Past Students".
    Attached Files Attached Files
    Last edited by jmeddaugh; 10-14-2013 at 06:52 PM.

  5. #5
    Registered User
    Join Date
    10-12-2013
    Location
    Seattle, USA
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    36

    Re: Pick a random person from a list of students

    You don't need VBA to do this - it can be done perfectly easily with Excel's built-in functions.

    In any vacant cell on the worksheet that contains the list of names, type: =RANDBETWEEN(1,COUNTA(B:B))
    This will count the number of cells in column B that contain data, and then generate a random integer between 1 and that number.

    Let's say you've entered the above function in Cell D1. Then, in another cell, type: =INDEX(A:A,D1)
    This uses the random number in D1 to select a first name from column A.

    To extend this to add the corresponding surname to the first name, you'd type instead: =INDEX(A:A,D1)&" "&INDEX(B:B,D1)
    (The concatenation operators add a blank space between the first name and the last name.)

    Each time you want a new random name, you just generate a new random number by recalculating the worksheet. This will happen every time you make a new entry on the sheet, or you can force a recalculation by using the keyboard shortcut "Control =" (or, on a Mac, "Command =").

    BTW, this assumes that there are no headers in your list of names. If there are, change the formula that generates the random number to (assuming just one header row): =RANDBETWEEN(1,COUNTA(B:B)-1)+1
    This still counts the number of cells used in column B, but then subtracts 1 to allow for the header - it then chooses a random number between 1 and (the_number_of_filled_cells)-1.

    Then 1 is added to the random number, so the index function will start one row further down (from the second row of the list, after the header), and will still be able to extend to the last entry in the list.
    Last edited by ianpage; 10-14-2013 at 07:19 PM.

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Pick a random person from a list of students

    @ Ianpage,

    Welcome to the Forum.

    What you are saying is true, and please see the attached sample Workbook, which I have prepared in advance. It might just be that the OP, for reasons of his own, requires a UserForm with VBA.

    Regards
    Attached Files Attached Files

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Pick a random person from a list of students

    Hello jmeddaugh,

    Can you make do with the sample Workbook as posted above, or do you want to continue with the Userform?

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Pick a random person from a list of students

    In column D number each student in order from 1 to 204.

    In E1 enter

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


    In the cell where you want the student's name enter:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  9. #9
    Registered User
    Join Date
    07-09-2013
    Location
    Wausau, Wisconisn
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Pick a random person from a list of students

    Sweet! Great Job!! Thanks!

  10. #10
    Registered User
    Join Date
    07-09-2013
    Location
    Wausau, Wisconisn
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Pick a random person from a list of students

    This does work, but if it is not too much trouble, I would love to see the code to use in a Userform. I have been using Excel much more in the School District, and would absolutely use the userform sometime.

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Pick a random person from a list of students

    Thank you for the Rep point.

    I can't help you with the user form but I think that Winon might be able to help you with it.

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Pick a random person from a list of students

    Hello jmeddaugh,

    I would love to see the code to use in a Userform.
    I thought so, since you have stated from the outset that you would like to capture the Name, and I presume, the Date as well, on another Sheet.

    So, here you go. Try the attached Workbook, which I have compiled in a xls format, for the benefit of other Forum Members as well.

    Thank you for adding to my Reputation as well.

    @ newdoverman,

    Thank you for your vote of confidence!
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-09-2013
    Location
    Wausau, Wisconisn
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Pick a random person from a list of students

    THANKS WINON!! THAT IS AWESOME,This is exactly what I was looking for and more!!!! FUNNY PIC TOO! HAHAHA

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Pick a random person from a list of students

    You are welcome.

    Glad I could help.

    I knew you would just LOVE the Pic!!! What is life without a little fun?

    If you are satisfied with the solution I had given you, then please mark your Thread as Solved.

    Regards

  15. #15
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Pick a random person from a list of students

    Hello jmeddaugh,

    Oops, I have made a mistake by sending you an incomplete Workbook. The one you have is not quite correct. It will allow nothing to be copied to the other Sheet, with a Date and Time stamp!

    I was actually working on two copies of the Workbook, saving one as a backup, and putting final touches to the other.

    Please use the attached Workbook, which is now the correct one.

    Hope you understand, and sorry for the inconvenience.

    Regards
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    07-09-2013
    Location
    Wausau, Wisconisn
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Pick a random person from a list of students

    Quote Originally Posted by Winon View Post
    Hello jmeddaugh,

    Oops, I have made a mistake by sending you an incomplete Workbook. The one you have is not quite correct. It will allow nothing to be copied to the other Sheet, with a Date and Time stamp!

    I was actually working on two copies of the Workbook, saving one as a backup, and putting final touches to the other.

    Please use the attached Workbook, which is now the correct one.

    Hope you understand, and sorry for the inconvenience.

    Regards
    No Worries! I really appreciate your help, it is nice to know there are still people that will help someone simply out of the kindness of their heart.
    Thanks Again!!

+ 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. Pick random names from list
    By ThomB in forum Excel General
    Replies: 3
    Last Post: 04-08-2013, 10:55 AM
  2. random pick names from a list
    By jladika in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-06-2011, 05:31 PM
  3. [SOLVED] Pick a Random name from a list.
    By David M Fritzke in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2006, 12:40 PM
  4. [SOLVED] Pick a Random name from a list.
    By David M Fritzke in forum Excel General
    Replies: 8
    Last Post: 06-06-2006, 10:55 AM
  5. Pick a Random name from a list.
    By David M Fritzke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-06-2006, 10:55 AM

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