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.
According to the attachment a VBA demonstration as a beginner starter :
PHP Code:
Sub Demo1() Dim V, W, X, C&(), 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(.Range, W) ReDim C(2 To UBound(X)) For P = 2 To UBound(X) X(P, 1) = Evaluate("COLUMN(" & [A1].Resize(, X(P, 1)).Address & ")") If Not IsArray(X(P, 1)) Then X(P, 1) = [{1}] Next For R = 1 To UBound(V) P = Application.Match(V(R, 1), W, 0) L = UBound(X(P, 1)) - C(P) C(P) = C(P) + 1 S = Fix(Rnd * L) + 1 V(R, 1) = X(P, 1)(S) X(P, 1)(S) = X(P, 1)(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 …
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?
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 …
Bookmarks