+ Reply to Thread
Results 1 to 5 of 5

Random Number Generation For Multiple Variable Ranges Within One List

  1. #1
    Registered User
    Join Date
    06-12-2015
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    91

    Random Number Generation For Multiple Variable Ranges Within One List

    Good afternoon,

    I've got list of names with multiples of each name. The amount of times each name appears is variable. I need to randomly assign an integer to each time the name appears.

    So say a name appears 10 times then each appearance would be assigned a random unique number from 1-10. The list of names changes every day, well, the names are the same but the amount they are on the list varies.

    Say the names are in column A then the random numbers would be in column C. The names in column A are in a random order but it's fine for them to be sorted if needed. They will be in a table called Table 1 with column A field called Names. I don't want the number to be part of the table.

    I've tried various combinations of things combining formulas and vba but I haven't come up with anything close to usable.

    I've included a sample sheet though there's not a lot of info to include. This with an example output in column C. I've included a couple of countifs for how many times the names are shown and this can be ignored or used as necessary.

    Hope that makes sense.

    Tim
    Attached Files Attached Files

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Random Number Generation For Multiple Variable Ranges Within One List

    .
    What about this ? ...

    Sort Col A lowest to highest.

    In C1 enter zero ( "0" )

    In C2 enter =C1+1

    Select C2

    Double click the small black dot in the lower right corner

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this !


    According to the attachment a VBA demonstration as a beginner starter :

    PHP Code: 
    Sub Demo1()
         
    Dim VWXC&(), P&, R&, L&, S&
             
    Randomize
             Union
    ([C2].CurrentRegion, [G1].CurrentRegion).Clear
        With 
    [A1].ListObject
             V 
    = .DataBodyRange.Value2
            
    .Range.AdvancedFilter 2, , [G1], True
             W 
    = [G1].CurrentRegion.Value2
             
    [G1].CurrentRegion.Clear
             X 
    Application.CountIf(.RangeW)
             
    ReDim C(2 To UBound(X))
        For 
    2 To UBound(X)
            
    X(P1) = Evaluate("COLUMN(" & [A1].Resize(, X(P1)).Address ")")
            If 
    Not IsArray(X(P1)) Then X(P1) = [{1}]
        
    Next
        
    For 1 To UBound(V)
            
    Application.Match(V(R1), W0)
            
    UBound(X(P1)) - C(P)
            
    C(P) = C(P) + 1
            S 
    Fix(Rnd L) + 1
            V
    (R1) = X(P1)(S)
            
    X(P1)(S) = X(P1)(L)
        
    Next
            
    .DataBodyRange.Columns(3).Value2 V
        End With
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 04-06-2021 at 02:13 PM. Reason: tiny optimization …

  4. #4
    Registered User
    Join Date
    06-12-2015
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    91

    Re: Hi ! Try this !

    Thank you for your responses. Logit, wouldn't this just give me a list of numbers in order as opposed to random?

    Marc L, this worked brilliantly. The only query I is if I needed to change the columns it refers to. I'm sorry I wasn't more specific but this will be part of a larger code and the exact column's haven't yet been decided. I was expecting to be able to understand more of the code to be able to alter but that make barely any sense to me. I can see that ".DataBodyRange.Columns(3).Value2 = V" would change the output column, but what would I need to alter in the code to change the column it refers to the the list of names in, for example, if that was in column D, not A?

    Cheers

    Tim

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Random Number Generation For Multiple Variable Ranges Within One List


    Please first well read & apply the direction just under the code in my post #3, thanks !

    In fact my demonstration refers not to any column but to a cell within the table like here the cell A1.
    So from this table it takes all the DataBodyRange as it contains only a single column …

+ 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. Place result of random number generation in a list
    By gpmattes in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-15-2016, 07:11 AM
  2. Random number generation across multiple ros/columns without repeats
    By Zodeeak in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-27-2014, 03:15 PM
  3. Random Number Generation
    By RalphJ in forum Excel General
    Replies: 1
    Last Post: 06-25-2010, 12:51 PM
  4. Random Number Generation
    By vioravis in forum Excel General
    Replies: 3
    Last Post: 02-27-2009, 08:39 PM
  5. [SOLVED] Random Number Generation
    By MB06 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-10-2006, 04:45 PM
  6. random number generation
    By scotjo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2006, 12:10 PM
  7. [SOLVED] Random number generation
    By Stratuser in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-15-2005, 09:06 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