CODEWITHSUNDEEP
excelvbarandomexcel-formulagenerator
Its_Hink
Its_Hink

Reputation: 1

Generate Random Cell inputs from a list across multiple Columns without repeats across Rows

I'm attempting to generate a random output off the same list of inputs across multiple columns, but without having the same input repeated more than an ~n number of times across the same row. There are a couple items I'm working on using this for, but for simplicity, I'm using the following example/data.

I have a column of names (Column C), 1-6, First name listed in Row 2. In Row 1, spanning columns D-O, are periods of time. To keep it simple, I've labeled them "Week 1" ; "Week 2" ; etc.

In column A, I have a list of 6 generic 'assignments' to match with the 6 names. My formula is in D3:O3, and is the following:

=INDEX(SORTBY($A$3:$A$8,RANDARRAY(COUNTA($A$3:$A$8))),SEQUENCE(6))

This gives me a random output of the list in column A for each respective Week, but I cannot figure out how to set the number of times an item from the list in col A can appear across col D-O (Week 1-12). See example below, where I have 6 'assignments' in the list, however, the repetition of each could be 0-6. I'd like to have an input to determine the number of times an item in the list can repeat across the row, for example -- with 6 items in the list, I'd like to limit each to only appear twice for a given row/name. If there's 12+, they can only appear once, if there's 3, they can appear 3 times, etc.

enter image description here

I'm not sure if this is possible using a formula, or if VBA would be required/more practical, but I haven't attempted to go down that road yet.

Attempted to incorporate the UNIQUE( function as well, but couldn't determine how to incorporate it properly, and multiple google searches turned up empty.

EDIT #1 -- I realized I forgot to include the 2nd parameter, where I'm looking to having an additional restriction on the # of repeats that occur down the columns, in addition to the # of repeats across the rows. With this example, the ideal output would look something like this, but randomized vs. this 'step-down' look. enter image description here

Upvotes: 0

Views: 428

Answers (1)

Ike
Ike

Reputation: 13024

if you have Excel 365 you can use this formula:

=LET(l,list[List],
maxRepeat,C2,
cntWeeks,COLUMNS(F1:Q1),
names,E2:E5,
λRandom, LAMBDA(l,maxRepeat,cntWeeks,
                LET(repeatedList, INDEX(l,SEQUENCE(ROWS(l)*maxRepeat,,1,1/maxRepeat)),
                    randomSort,SORTBY(repeatedList,RANDARRAY(ROWS(l)*maxRepeat)),
                    TOROW(TAKE(randomSort,cntWeeks)))),
DROP(REDUCE("",names,LAMBDA(r,x,VSTACK(r,λRandom(l,maxRepeat,cntWeeks)))),1))

λRandom creates the extended list of values based on maxRepeat, then sorts this list randomly and takes the first 12 (= cntWeeks) values.

This is repeated for each name in E2:E5.

enter image description here

UPDATE

With this formula within a week every task appears exactly one time (6 tasks, 6 names):

=LET(weeks,D1:O1,tasks,list[List],
cntTasks,ROWS(tasks),cntWeeks,COLUMNS(weeks),
λRotateTasks,LAMBDA(start,SCAN(start-1, SEQUENCE(cntTasks),
                                                   LAMBDA(a,v,IF(a=cntTasks,1,a+1)))),
tasklist,DROP(REDUCE("",SEQUENCE(cntWeeks),
                                     LAMBDA(result,i,HSTACK(result,SORTBY(list[List],λRotateTasks(MOD(i-1,cntTasks)+1))))),,1),
SORTBY(tasklist,RANDARRAY(1,cntWeeks)))

λRotateTasks creates - based on the start parameter - a list of numbers, e.g. 3,4,5,6,1,2 for start = 3. This is used to sort the tasklist.

These sorted tasklists are then randomly stacked together ...

enter image description here

Upvotes: 1

Related Questions