CODEWITHSUNDEEP
excelexcel-formula
Izehiuan Ideho
Izehiuan Ideho

Reputation: 11

How do i use Excel Random list generation without duplicate

I want to randomly fill a table of 50 rows without duplicate with 40 name. Also, when a cell is blank, i want to omit it from the cells to be randomly filled. So it only fills 40 of the 50 cells. My formula still account for the empty cells. even though it does not fill them, it still assigns names.

so far, my formula is
=@IF([@Active]=1,INDEX((SortlistDB[Spotcheckers]),RANK(@SortList!J:J,(SortlistDB[SCSorter])),1),"")

The problem with my current formula is I'm filling a table with 50 rows. if the first 5 rows are blank, and if SortlistDB[Spotcheckers] has 40 names, it skips the first 5rows but only fills the next 35 rows which leaves 5 names unused.

Sample of table

Spotcheckers SCSorter SpotCheckSpotchecker SCSCSorter RandEligibleList WeeklySpotCheckers WeeklySCSC Active WorkCenter
MMC Adrianna 0.7720959 XO 0.011475278 LTJG Asia LTJG Asia 1 CA01
LT Ahmed 0.971935389 CO 0.885803121 MMC Adrianna MMC Adrianna 1 CA02
STGC Alisha 0.418148226 CMC 0.468142635 ENS Kathryn ENS Kathryn 1 CC01

WeeklySpotcheckers is the column to be filled. Active determines what row in the WeeklySpotCheckers to be left blank

Upvotes: 1

Views: 143

Answers (2)

P.b
P.b

Reputation: 11415

=LET(e,IFERROR,s,TOCOL([Spotcheckers],1),r,SORTBY(s,RANDARRAY(ROWS(s))),x,LAMBDA(y,@FILTER(r,COUNTIF([[#Headers],[WeeklySpotCheckers]]:INDEX([[#All],[WeeklySpotCheckers]],ROW([@Active])-1),r)=y)),IF([@Active]=1,e(e(x(0),x(1)),""),""))

For Excel 2021 I hope this is compatible:

=LET(s,TOCOL([Spotcheckers],1),
     r,SORTBY(s,RANDARRAY(ROWS(s))),
     x,COUNTIF(Table1[[#Headers],[WeeklySpotCheckers]]:INDEX(Table1[[#All],[WeeklySpotCheckers]],ROW([@Active])-1),r),
IF([@Active]=1,
   IFERROR(
           IFERROR(
                   @FILTER(r,x=0),
                   @FILTER(r,x=1)),
           ""),
   ""))`

Upvotes: 1

Black cat
Black cat

Reputation: 6097

In the example there are 20 randomized names in the range D1:D20.
A1:A31 is the range of the table to fill.

=LET(tab,HSTACK(TAKE(TOCOL(IF(A$1:A$31=0,1/0,ROW(A$1:A$31)),2),20),D$1:D$20), IFERROR(VLOOKUP(ROW(),tab,2,0),""))

For 40 names, and 50 rows change formula to this

`=LET(tab,HSTACK(TAKE(TOCOL(IF(A$1:A$50=0,1/0,ROW(A$1:A$50)),2),40),D$1:D$40),
IFERROR(VLOOKUP(ROW(),tab,2,0),""))`

The formula is in cell B1 and drag down.

The formula first create the tab table with the non-empty rows and the corresponding names.
VLOOKUP select the name for the actual row.

enter image description here

Upvotes: 1

Related Questions