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	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

Solution

=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,FILTER([Spotcheckers],[Spotcheckers]<>""),
     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)),
           ""),
   ""))`

First f will store an array of all (nonblank) values in Spotcheckers. Next r sorts f by a random array of the same size.

Then x performs a COUNTIF on the column WeeklySpotCheckers (including header) up to the row prior to the current on r.

Unused values from r will count 0 else the count if returns the number it was used already.

Finally we check if Active equals 1 if so it returns the first value in r where x counted 0 if there are no more unused the filter will look for the first value of r where x counted 1. If there are no more values unused twice, it returns "" a blank value.