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)),
           ""),
   ""))`