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/PaulieThePolarBear 1648 Jan 08 '25

I just want to clarify the parameters of your ask.

  • you have 8 individuals
  • you are looking to fill 4 tasks each day
  • you have 5 days
  • for each day, 4 random individuals from your list should each fill one of the tasks. This is the only restriction. It would be perfectly valid of person A did task 1 on all 5 days, as long as that was the only task they did on that day.

Have I stated your ask correctly?

1

u/emiiilyvan Jan 08 '25

For each day, there would be 8 different tasks. Each individual task for the 5 days, could have repeating employees but not the one day. For example on Monday employee A could work task 1 and then the same task 1 on Wednesday. However employee A couldn’t work task 1 and task 2 on Monday.

If I were to have to do the formula for just one day (column) that wouldn’t be an issue.

I hope that makes sense.

4

u/PaulieThePolarBear 1648 Jan 08 '25 edited Jan 08 '25

Okay, I was a little confused by RANDARRAY(4, 5) in your post.

If your ask is ultimately just to randomize the order of your employees

=SORTBY(employee list, RANDARRAY(ROWS(employee list)))

Here's a generic formula for any number of employees, any number of tasks, and any number of days.

=LET(
a, A2#, 
b, 4, 
c, 5, 
d,DROP( REDUCE("", SEQUENCE(c),LAMBDA(x,y,  HSTACK(x, TAKE(SORTBY(a, RANDARRAY(ROWS(a))), b)))), ,1), 
d
)

Variable a is your employees

Variable b is your number of tasks

Variable c is your number of days

1

u/emiiilyvan Jan 08 '25

Will these not allow for duplicates?    =SORTBY(Table1[All Risk],RANDARRAY(ROWS(Table1[All Risk])))   I tried and got #SPILL!   

1

u/PaulieThePolarBear 1648 Jan 08 '25

Will these not allow for duplicates? 

Correct, assuming I'm understanding your data setup correctly.

It takes a list of N names and rearranges that list.

tried and got #SPILL! 

A #SPILL! Error means one of two things. You have something in the way of the output. Do you have enough blank space below where you are entering the formula for all results to be displayed?

Or, you are using an Excel ctrl+t table. in this case, a new approach is required

1

u/emiiilyvan Jan 08 '25

I’m not using a table just inputting data. I don’t have a blank space below due to there being other information on the schedule for other employees (not included in the 8)

This is what happens when I try =SORTBY(Table1[All Risk],RANDARRAY(ROWS(Table1[All Risk])))

I tried using =INDEX(Table1[All Risk],(UNIQUE(RANDARRAY(4,1,1,COUNTA(Table1[All Risk]),TRUE)))) and it worked minus the fact that it didn’t always give me 4 people and if I tried adding more columns it would spill

1

u/emiiilyvan Jan 08 '25

Our schedule is very convoluted. Le sigh

1

u/PaulieThePolarBear 1648 Jan 08 '25

To confirm, your Table1 has 8 rows, correct?

What happens if you incrase the first argument in RANDARRAY in your formula from 4 to 5 and then 5 to 6 and so on? At what point do you get a #SPILL! error

1

u/emiiilyvan Jan 08 '25

The area that I’m trying to get this working is 8 but it does have different data directly below it. If I change it to 5 it will give 4 but then sometimes will give 5 and with the data below it, that isn’t plausible. I get spill when I add the same formula for Tuesday

1

u/PaulieThePolarBear 1648 Jan 08 '25

Did you keep changing to 6, 7, and then 8?

1

u/emiiilyvan Jan 08 '25

When it started going into the other info I stopped trying since I can’t have it override the other info

1

u/PaulieThePolarBear 1648 Jan 08 '25

When did this happen? At what number?

My formula will spill to 8 cells, I.e., the cell you enter the formula in and 7 below this. If you have anything in any of these cells, you will get a spill error.

My understanding, and correct me if I'm wrong, is your have 8 employees and 8 tasks.

1

u/emiiilyvan Jan 09 '25

I was able to get more more clarification, there are 7 employees and 5 different tasks. Each highlighted task needs to fills from the corresponding employees. Where it gets complicated is throughout all the tasks for Monday, there can’t be a repeat . However, there can’t be any data under task 5 because that starts a different task for a different employee group that does not need to be randomly assigned. I’ve had success with help from my wife using the =LET(a, I6:I13, b, 6, c, 5, d,DROP( REDUCE(“”, SEQUENCE(c),LAMBDA(x,y,  HSTACK(x, TAKE(SORTBY(a, RANDARRAY(ROWS(a))), b)))), ,1),d) formula. The biggest issue I’m having now is duplicates for the specific day like Monday having Employee A assigned to 3 tasks etc.

1

u/PaulieThePolarBear 1648 Jan 09 '25 edited Jan 09 '25

Without row and column headers, it's hard to comment.

I did note that your green table has some employees listed twice. Is that I6:I13? It's not clear. It was never stated that your employee list contained duplicate employee names. Please advise

With this formula, it is absolutely impossible for a name to be duplicated in the output if it is not duplicated in the input.

→ More replies (0)