+ Reply to Thread
Results 1 to 7 of 7

Selecting Random Rows based on groups

  1. 04-11-2018, 11:48 AM #1
    coryjacques
    coryjacques is offline
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Selecting Random Rows based on groups

    Hi!

    I'm looking for a formula to select random rows based on groups that are present in their own column in the same row (see attached sample).

    The purpose of this is to define random test subjects (2 from each group) in a dataset of over 12756 rows. I have experience with the RAND function, but I believe that pulling rows of test subjects (all columns must be present) into a new worksheet is above my experience level.

    If this requires a Macro, I already have macros present in the original workbook and can add these.

    randsample.JPG
    Reply With QuoteRegister To Reply
  2. 04-11-2018, 11:59 AM #2
    dflak
    dflak is offline
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Selecting Random Rows based on groups

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
    Reply With QuoteRegister To Reply
  3. 04-11-2018, 12:04 PM #3
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Selecting Random Rows based on groups

    data must be in ascending order on column group
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-11-2018, 12:08 PM #4
    coryjacques
    coryjacques is offline
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Re: Selecting Random Rows based on groups

    Sheet has been added with groups in ascending order
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 04-11-2018, 12:09 PM #5
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,025

    Re: Selecting Random Rows based on groups

    One way, with a helper column: =RAND() then an array formula:

    =INDEX(A$2:A$11,MATCH(SMALL(IF($E$2:$E$11="Group 1",$F$2:$F$11,""),ROWS($1:1)),$F$2:$F$11,0))

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    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
    Reply With QuoteRegister To Reply
  6. 04-11-2018, 01:26 PM #6
    coryjacques
    coryjacques is offline
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Re: Selecting Random Rows based on groups

    Thanks Glenn,

    Let me just make sure I understand from this breakdown (because I feel like I'm doing this incorrectly).

    I'm using index to select the range of a unique identifier in column A. In your sheet this is their name. I imagine the value of these cells is meaningless, as long as we're selecting the left-most array.

    Then I'm completing the index match, but under the criteria that we're manually searching for the group name. If the group name is found in column E, then it will link over to the RAND function you've used, which will assign a random value. I'm not familiar with the "SMALL" function, so this part does trip me up a bit. If the group number specified is NOT found in column E, it will pull row by row (this part confuses me as well - does this actually pull row by row if the statement is true?).

    The final statement again pulls the RAND function to close out the INDEX with equal values.

    How far off am I in interpreting this?
    Reply With QuoteRegister To Reply
  7. 04-12-2018, 04:59 AM #7
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,025

    Re: Selecting Random Rows based on groups

    A littl out, but not too far:

    =INDEX(A$2:A$11,MATCH(SMALL(IF($E$2:$E$11="Group 1",$F$2:$F$11,""),ROWS($1:1)),$F$2:$F$11,0))

    Red: if column E is "Group 1", return the value in column F, otherwise return blank.

    Orange: return the smallest value - when ROWS($1:1)=1 on the first line and then the second smallest value on the next line - when ROWS($1:2)=2

    Cyan: return the row number of the value that exactly matches the smallest value.. and then

    Black: return the corresponding value from column A.

    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.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Placement of random assigned number groups without duplicates along both rows and columns
    By tiger963 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2016, 12:22 PM
  2. [SOLVED] Selecting random rows and paste it in different column.
    By rajani85 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2015, 09:03 AM
  3. Selecting random cells based on criteria
    By JamesTrethowan in forum Excel General
    Replies: 8
    Last Post: 10-28-2015, 11:15 AM
  4. Selecting random rows based on criteria in other columns
    By Insert Name in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2015, 01:35 AM
  5. selecting random rows for a sampling
    By NDMALLARD in forum Excel General
    Replies: 1
    Last Post: 01-09-2009, 07:03 PM
  6. Help selecting random rows of data
    By hnoshea in forum Excel General
    Replies: 2
    Last Post: 10-04-2006, 03:18 PM
  7. Selecting random rows
    By anar_baku in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-08-2006, 07:45 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1