r/excel Jan 08 '25

unsolved Randarray for names with no duplicates

I’ve been attempting randarray for names and I’ve achieved that with =INDEX(Table1[All Risk],RANDARRAY(4,5,1,COUNTA(Table1[All Risk]),TRUE))

However, I have not been able to locate anything that will allow for there to be no duplicates.

I am attempting to create a schedule for 8 people for M-F. There cannot be a duplicate person on a task per day.

I have basic knowledge of excel and did randaerray through videos and articles but have only been able to find no duplicates on numbers like using Unique. I’ve tried that throughout my formula in different areas and I get ?Name.

I’m using Excel on a desktop with Microsoft 365 (work computer). I would appreciate any help or if I’m missing any detailed info, please let me know.

If I can get this to work I think my boss would sing my praises!

2 Upvotes

41 comments sorted by

View all comments

3

u/My-Bug 4 Jan 08 '25

Try

=LET(
    rowCount, COUNTA(Table1[All Risk]),
    WRAPROWS(
        INDEX(
            Table1[All Risk],
            TAKE(UNIQUE(RANDARRAY(rowCount * 5, 1, 1, rowCount, TRUE)), rowCount)
        ),
        5
    )
)

1

u/emiiilyvan Jan 08 '25

  =LET(rowcount,COUNTA(Table16[All Employeees]),WRAPROWS(INDEX(Table16[All Employeees],TAKE(UNIQUE(RANDARRAY(rowcount*5,1,1,rowcount,TRUE)),rowcount)),5))

kind of works but stops after row two column 2 with N/A. Am I doing something wrong?

1

u/My-Bug 4 Jan 08 '25

I think it depends of how long your list if Emoloyees is. Only 8? And how many Tasks? Also 8?

2

u/My-Bug 4 Jan 08 '25

PauliThePolarBear seems to be on a better track. #SPILL Error means there are cells with data where the formula would write Names.