+ Reply to Thread
Results 1 to 11 of 11

Random selection of data that meets criteria in text string

  1. #1
    Registered User
    Join Date
    01-29-2015
    Location
    Perth, Australia
    MS-Off Ver
    2007 & 2010
    Posts
    4

    Exclamation Random selection of data that meets criteria in text string

    Hi,

    I have a large table of data from which I am pulling out random selections.
    My first random selection is random-random for which I am using =INDEX($G$67:$G$1092,ROUND(RAND()*COUNTA($G$67:$G$1092),0))
    This works perfectly.
    Column G has a concatenate of 3 values, vendor+purchase order+invoice

    My next selection has to be a random selection of the vendor.
    I dont want to be pulling the data into another table, I'd love to be able to get a singular formular to do this.
    I have tried inputting Countif instead of count A and using the "*"&"vendor"&"*" - this does not work.
    I have also tried nesting the Match function in various locations but this also is not working.
    The RandBetween function only works with numerical values as far as I can understand.

    Does anyone have any suggestions - all the posts I have read only seem to pertain to numerical values.

    Help would be very much appreciated.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,864

    Re: Random selection of data that meets criteria in text string

    Sample file (see http://www.excelforum.com/forum-rule...rum-rules.html ) would be useful.
    Anyway, I do not think the potential formula will be "an easy one", so may be a good way would be to use UDF. For instance such one shall do:

    Please Login or Register  to view this content.
    in worksheet you use it as standard excel function:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached sample (I reused file from http://www.excelforum.pl/topics1/sam...sz-vt45264.htm)
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,864

    Re: Random selection of data that meets criteria in text string

    there were some problems with exxcelforum.com server so I'm re-attaching sample file ...
    Attached Files Attached Files
    Last edited by Kaper; 01-31-2015 at 05:12 AM.

  4. #4
    Registered User
    Join Date
    05-10-2013
    Location
    Pomáz, Hungary
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Random selection of data that meets criteria in text string

    Do you want to 'COUNTIF' vendor as a word?

  5. #5
    Registered User
    Join Date
    01-29-2015
    Location
    Perth, Australia
    MS-Off Ver
    2007 & 2010
    Posts
    4

    Re: Random selection of data that meets criteria in text string

    Hi Istvan,

    I have tried using CountIf and whilst it keeps the fomula valid, it wont pull the correct vendor. Perhaps I am doing something wrong here..?

  6. #6
    Registered User
    Join Date
    01-29-2015
    Location
    Perth, Australia
    MS-Off Ver
    2007 & 2010
    Posts
    4

    Smile Re: Random selection of data that meets criteria in text string

    Hi Kaper,

    I will try your suggestion - thank you for putting this one together.

    I have put together a little demo of what Im trying to achieve - essentially in the yellow box I would like a random selection from column "A" that contains "Intel".
    I need to use the concatenate as this is how I will get a unique value populated in the yellow box.

    Thank you again
    Attached Files Attached Files

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,941

    Re: Random selection of data that meets criteria in text string

    a13=INDEX($A$2:$A$7,SMALL(INDEX(ISERROR(SEARCH("Intel",$A$2:$A$7))*10^10+ROW($A$2:$A$7)-ROW($A$2)+1,0),RANDBETWEEN(1,COUNTIF(A$2:A$7,"*Intel*"))))
    Try this
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  8. #8
    Registered User
    Join Date
    01-29-2015
    Location
    Perth, Australia
    MS-Off Ver
    2007 & 2010
    Posts
    4

    Re: Random selection of data that meets criteria in text string

    Thank you Siva.
    This solution works absolutely perfectly.

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,941

    Re: Random selection of data that meets criteria in text string

    You are welcome and thanks for your feedback

  10. #10
    Registered User
    Join Date
    05-10-2013
    Location
    Pomáz, Hungary
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Random selection of data that meets criteria in text string

    Quote Originally Posted by SerenaJoselyn View Post
    Hi Istvan,

    I have tried using CountIf and whilst it keeps the fomula valid, it wont pull the correct vendor. Perhaps I am doing something wrong here..?
    For example, if you want ’COUNTIF’ „table” in A1:

    =COUNTIF(A1,"*table*")

  11. #11
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,864

    Re: Random selection of data that meets criteria in text string

    Quote Originally Posted by SerenaJoselyn View Post
    Hi Kaper,
    I will try your suggestion - thank you for putting this one together.
    And ... have you tried it?
    In your file the formula would be:
    Please Login or Register  to view this content.
    PS. Proper formula for random-random shall be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    note ROUNDUP instead of ROUND.
    With ROUND you occasionaly (when RAND()*COUNTA.. is <0.5) try to pick element number 0 in index causing an error.
    Attached Files Attached Files

+ 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. [SOLVED] Need help returning text if Data meets certain criteria
    By sumner06 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-24-2014, 01:06 PM
  2. [SOLVED] Formula to find string in range that meets certain criteria
    By satania in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-16-2013, 12:22 PM
  3. highlight selection cell that meets criteria and multiple cells if not
    By rona_ele in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2011, 09:17 AM
  4. Replies: 4
    Last Post: 02-06-2009, 11:18 PM
  5. Replies: 1
    Last Post: 10-30-2008, 07:40 PM

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