+ Reply to Thread
Results 1 to 3 of 3

Randbetween with the list of exception

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-25-2012
    Location
    Coimbatore, India
    MS-Off Ver
    Excel 2010
    Posts
    445

    Randbetween with the list of exception

    Hello Friends

    Please find the attached 2010 version file <randbetween except 170715.xlsx>

    In Cell C1 formula to be entered which can capable of =RANDBETWEEN(1,99) and also can omit / except the values from the list of A1:A20.

    thanks in advance

    thilag
    Attached Files Attached Files

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,876

    Re: Randbetween with the list of exception

    Maybe a UDF like -

    Function RandNumbers()
    
        Dim R As Long, MatchId As Integer
        
    Here:
        MatchId = 0
        R = Application.WorksheetFunction.RandBetween(1, 99)
        On Error Resume Next
        MatchId = Application.WorksheetFunction.Match(R, Range("A1:A20"), 0)
        If MatchId > 0 Then GoTo Here:
        
        RandNumbers = R
    End Function
    A slightly more customizable version, where you can select the Min, Max Number & Range. Should be entered like this - =RandNumbers(1,99,A1:A20)

    Function RandNumbers(Min As Integer, Max As Integer, Rng As Range) As Integer
    
        Dim R As Long, MatchId As Integer
        
    Here:
        MatchId = 0
        R = Application.WorksheetFunction.RandBetween(Min, Max)
        On Error Resume Next
        MatchId = Application.WorksheetFunction.Match(R, Rng, 0)
        If MatchId > 0 Then GoTo Here:
        
        RandNumbers = R
    End Function
    Last edited by NeedForExcel; 07-17-2015 at 02:51 AM.
    Cheers!
    Deep Dave

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Randbetween with the list of exception

    Assuming all the A1:A20 values will also be integers in the range 1 to 99 you could use this "array formula" in C1

    =SMALL(IF(COUNTIF(A1:A20,ROW(INDIRECT("1:99")))=0,ROW(INDIRECT("1:99"))),INT(1+RAND()*(99-SUM(IF(FREQUENCY(A1:A20,A1:A20),1)))))

    Confirm with CTRL+SHIFT+ENTER
    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. Replies: 10
    Last Post: 07-03-2015, 04:29 PM
  2. Replies: 2
    Last Post: 03-10-2015, 10:09 AM
  3. Using LARGE with an exception list
    By Phillycheese5 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-20-2014, 01:50 PM
  4. exception of zero
    By stefrgv in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-24-2013, 11:56 AM
  5. [SOLVED] =RANDBETWEEN Function for growing price list with no duplicates...
    By unclejemima in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-18-2013, 03:29 PM
  6. COM exception
    By grasyl in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-25-2011, 04:41 AM
  7. exception to MsgBox
    By L Scholes in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-14-2006, 02:46 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