+ Reply to Thread
Results 1 to 4 of 4

Random selection with multiple column criteria

  1. #1
    Registered User
    Join Date
    09-02-2009
    Location
    Monterey, California
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    2

    Random selection with multiple column criteria

    example.xls
    Hello!

    I am trying to go one step further than the query and answer found in this thread:
    http://www.excelforum.com/excel-gene...om-symbol.html

    What I want to do is randomly pull from a field of data, MINUS any cells that do not meet the criteria in other columns.

    So if column A is my target column for the random selection (containing 10 unique entries), and column B has 10 repeating colors, and column C has 10 repeating animals, what can I use to perhaps create this effect:
    A random pull from column A, but only from rows in which column B says red and column C says cat? (The pull being 1, 4, 7, or 9 in the below example, of course.)

    Data (this example is also attached in an excel doc):
    One Red Cat
    Two White Dog
    Three Blue Horse
    Four Red Cat
    Five White Cat
    Six Blue Cat
    Seven Red Cat
    Eight Red Dog
    Nine Red Cat
    Ten Red Horse

    I have tried variations on IF and INDEX to no avail. Perhaps something with SUMPRODUCT?

    -k

    Many thanks to the wise contributors in advance- this may be my first post, but only because I've been able to find all my previous questions already answered in previous threads!
    Last edited by kleick; 09-03-2009 at 03:30 PM. Reason: Solved

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Random selection with multiple column criteria

    I assume you're using a cell to indicate "red" and another for "cat". So let's add those into the mix.

    column A = numbers
    column B = colors
    column C = animals
    column D =RAND()
    ...copy that formula down the whole set

    E1 = "red"
    F1 = "cat"
    G1 = random choice from column a with the formula:
    =INDEX($A$1:$A$10, MATCH(SMALL(IF($B$1:$B$10 & $C$1:$C$10 = $E$1 & $F$1, $D$1:$D$10), 1), $D$1:$D$10, 0))

    EDIT: That's an array formula, so you have to enter it and confirm with CTRL-SHIFT-ENTER
    Attached Files Attached Files
    Last edited by JBeaucaire; 09-03-2009 at 02:19 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-02-2009
    Location
    Monterey, California
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    2

    Re: Random selection with multiple column criteria

    Ah, yes, thanks!

    If I'm tracking correctly:
    IF determines an abridged array based on the criteria being met,
    SMALL selects the lowest of the random values in that array,
    MATCH indicates what row the selection is in based on position,
    and INDEX determines what text value to display in the target column.

    Some of that was new to me, so I took the time to learn why and how the functions worked- thanks for the education and the assist!

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Random selection with multiple column criteria

    Quote Originally Posted by kleick View Post
    IF determines an abridged array based on the criteria being met,
    SMALL selects the lowest of the random values in that array,
    MATCH indicates what row the selection is in based on position,
    and INDEX determines what text value to display in the target column.

    Very good, grasshopper. (old Kung Fu joke)

    I'm not sure I could have summarized that as briefly as you did. Well done.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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