+ Reply to Thread
Results 1 to 5 of 5

Randomly selecting cells with text criteria

  1. #1
    Registered User
    Join Date
    10-22-2017
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    2

    Randomly selecting cells with text criteria

    Hi there,

    Basically I have a 10 by 10 cell array. Individual cells contain three separate pieces of information separated by spaces, the first piece of which contains a three letter abbreviation (off which there are 4 different types) and the second and third are simply numbers corresponding to the cells position in x and y space respectively. So, for example a cell could read 'SPN 5 2' or 'PHL 5 4'

    I basically wish to randomly select a cell from the array that must contain a specific three letter abbreviation (e.g. ‘SPN’)

    I can randomly select a cell in the 10 by 10 array by using '=INDEX(array,RANDBETWEEN(1,10),RANDBETWEEN(1,10))', I just can't fathom how to select a cell with a specific abbreviation.

    Any help would be greatly appreciated,

    Cheers,

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,830

    Re: Randomly selecting cells with text criteria

    It could be done with long formula, but instead of thinking on it I wrote simple UDF:

    usage is just =random_tri(A1:J10,"SPN") or =random_tri(named_range,cell_with_the_leftmost_part), etc
    random selection is based on a string, which not neccesserily have to be only 3 letter long. See attachment sample. Press F9 to recalculate sheet. Press Ctrl+F3 to change named range (Now is just one column)
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    10-22-2017
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Randomly selecting cells with text criteria

    Hi Kaper,

    Brilliant, thanks ever so much that works a charm! I should really get around to learning how to use UDF,

    Cheers again, very much appreciated!

    Brendan

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,830

    Re: Randomly selecting cells with text criteria

    Glad I could help :-)

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Randomly selecting cells with text criteria

    If you're interested you could do that with a formula like this:

    =INDIRECT(TEXT(SMALL(IF(LEFT(A1:J10,3)="SPN",COLUMN(A1:J10)+ROW(A1:J10)*100),RANDBETWEEN(1,COUNTIF(A1:J10,"SPN*"))),"\R0\C00"),FALSE)

    confirm with CTRL+SHIFT+ENTER

    This assumes the range is A1:J10 - that can be varied but formula may need adjustment
    Audere est facere

+ 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. Randomly selecting words from text
    By Exceln00b95 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-13-2016, 12:38 PM
  2. Randomly select cells with text. (That are not Blank)
    By Ryder03 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-21-2015, 12:43 AM
  3. Randomly Selecting Variable From The Variables That Meet A Criteria
    By levitt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-16-2014, 08:25 PM
  4. Marking Cells randomly based on criteria
    By kmpoaquests in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2012, 10:31 AM
  5. Randomly fill cells with user-entered text
    By bsweet0us in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-21-2010, 11:53 AM
  6. randomly selecting a cell from a range
    By snuiter in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2008, 12:35 AM
  7. Randomly Selecting a Value from a Range
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-29-2005, 10:59 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