+ Reply to Thread
Results 1 to 11 of 11

VBA to randomly select a GROUP of data

  1. #1
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    VBA to randomly select a GROUP of data

    Hi,

    I have a worksheet with two sheets - Sheet 1 contains the table below:

    (Sheet 1)
    ID Name
    1 Tom
    2 Tom
    3 Tom
    4 Tom
    5 Tom
    6 Tom
    7 Tom
    8 Tom
    9 Joe
    10 Joe
    11 Joe
    12 Mary

    I need a VBA to randomly copy AT LEAST 1 record from EVERY unique Name GROUP but no more than 5.
    So, the output should be 1 - 5 records from EACH Name group and the entire row should be copied to Sheet 2 as follows:

    (Sheet 2)
    ID Name
    1 Tom
    2 Tom
    3 Tom
    4 Tom
    5 Tom
    9 Joe
    10 Joe
    11 Joe
    12 Mary

    Thanks!

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA to randomly select a GROUP of data

    Maybe :

    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: VBA to randomly select a GROUP of data

    Hi Karedog, and thx for the help! I ran this macro which produced the following:

    ID Name
    1 Tom
    2 Tom
    3 Tom
    4 Tom
    5 Joe
    6 Joe
    7 Joe
    8 Mary

    Two Issues:
    The ID numbers for each Name should remain the same as in Sheet1 - It looks like the macro renumbered the list (See Joe).
    Also there should be a Max of 5 but Tom only appears 4 times.

    Thanks again!

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA to randomly select a GROUP of data

    Hi symaxf,

    Quote Originally Posted by symaxf View Post
    Also there should be a Max of 5 but Tom only appears 4 times.
    But you said that :
    Quote Originally Posted by symaxf View Post
    I need a VBA to randomly copy AT LEAST 1 record from EVERY unique Name GROUP but no more than 5.
    So, the output should be 1 - 5 records from EACH Name group
    Isn't 4 Tom's is "AT LEAST 1 record but no more than 5" ?

  5. #5
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA to randomly select a GROUP of data

    This code will keep the ID, but doesn't fix the "Max of 5" issue, since it is still not clear to me :

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: VBA to randomly select a GROUP of data

    Max of 5 is correct - my bad.

    This is my result of running the last code.
    The results look correct but the data was moved into different columns:

    ID Name
    Tom 5
    Tom 6
    Tom 2
    Joe 11
    Mary 12

    Also, since the number of columns will vary, would it be too much trouble to modify this code to copy the ENTIRE row for each selection into 'Sheet 2'?!

    Thx again karedog!

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA to randomly select a GROUP of data

    You are welcome. This code will fix the column ordering and also works for multiple columns (more than 2 columns) :

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: VBA to randomly select a GROUP of data

    karedog, you are awesome! this macro works perfectly. I failed to mention the fact that the ID's contained in Col A contain a hyperlink. How can I modify your code to include these hyperlinks when the rows are copied to "Sheet2"? I'll gladly open up a new thread for this question if I need to. Thanks again!!!

  9. #9
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA to randomly select a GROUP of data

    If this doesn't work, then you must post your workbook with actual data layout (not masked) :

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: VBA to randomly select a GROUP of data

    Works great - Thx again and take care!

  11. #11
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA to randomly select a GROUP of data

    You are welcome, thanks for marking the thread as solved.


    Regards

+ 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. Replies: 7
    Last Post: 06-09-2016, 09:48 PM
  2. VBA to group a list of names randomly
    By Ironman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-30-2014, 03:54 AM
  3. [SOLVED] Randomly group N elements (where N is a factor of 7) into groups of 7
    By nomadic23 in forum Excel General
    Replies: 14
    Last Post: 10-10-2013, 06:07 AM
  4. [SOLVED] VBA Randomly Select m amount from n amount of data, where m<n
    By doglover in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-23-2013, 03:44 AM
  5. Randomly select the data in excel
    By vish2025 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 11-02-2010, 03:47 AM
  6. Randomly relisted a group of names onto another Book
    By Don Juan in forum Excel General
    Replies: 3
    Last Post: 09-09-2006, 12:44 AM
  7. How do I shuffle a group of cells randomly in Excel?
    By golfmoab in forum Excel General
    Replies: 1
    Last Post: 01-22-2005, 10:06 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