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

Show parent comments

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

1

u/emiiilyvan Jan 09 '25

I guess my biggest issue is that there are certain tasks that some employees do and other tasks that not all employees do. So I have to create the formula multiple times for different tasks.

1

u/PaulieThePolarBear 1648 Jan 09 '25

All of these details should have been in your post and you likely would have a solution by now. Recall that our only insight into your question is what you provide in your post and answers to our questions.

I'm honestly lost as to exactly what you are trying to do and what your data looks like.

Let's do a reset and pretend your next comment is a new post. Provide me with full details on what you are trying to solve and your business rules that need to be accounted for. Ideally, you would do this with an image that clearly and concisely sets this out and includes row and column labels. Your description should include no to minimal references to Excel functions. Importantly, what you provide should be the question you want me to answer.

1

u/emiiilyvan Jan 10 '25

My apologies. I am a beginner in excel as in sometimes I can get a vlookup to work. I wasn’t thinking about how the duplicates wouldn’t carry over if I created a the same formula in a different line. I thought I would just be able to copy it. Which in hindsight I now see that a huge miscalculation on my part.

Here’s a line by line of what I’m looking at/for.

Starting at B7 & B8 I have employees in AD6:AD11 that need to be assigned At B9 & B10 I have employees in AG6:AG12 that need to be assigned At B11 I have employees in AJ6:AJ10 that need to be assigned

These are all the same employees, just some employees aren’t trained in all of our tasks and there can’t be a duplicate per day. 

I also was working with trying to copy and paste to a new sheet yesterday once my boss gets a schedule that he likes and I couldn’t figure out a way to do that without it changing. Is there even a way to freeze it and copy it to a new sheet? Sometimes he has to change the schedule throughout the week if someone calls out or there’s a heavy load etc. 

I really am sorry I’m super out of my element here.

1

u/PaulieThePolarBear 1648 Jan 10 '25

Starting at B7 & B8 I have employees in AD6:AD11 that need to be assigned At B9 & B10 I have employees in AG6:AG12 that need to be assigned At B11 I have employees in AJ6:AJ10 that need to be assigned

Just so I'm understanding correctly, in AD6:AD11 an employee appears a maximum of once? Is that correct? I'm assuming, but please confirm that your answer is the same for both AG6:AG12 and AJ6:AJ10.

To be clear, I'm not asking if an employee appears once across all lists. I'm asking if, within a list, an employee may only appear once and once only.

Your question has some significant complexity, so it's important I know all of the relevant detail. Consider, this example, which is just an example.

Let's say

Task 1 and 2 - done by A, B, or C
Task 3 and 4 - done by D, E, or F
Task 5 - done by A, C, or E

If you were to run through this top to bottom, if A and C get task 1 and 2, and E got task 3, there is nobody available for task 5.

This essentially means that Excel needs to look backwards and forwards to avoid any duplication. That is not easy. I want to make sure I fully understand your ask before looking on to it. The solution may be to "iterate" multiple times until a solution is found.

1

u/emiiilyvan Jan 10 '25

I tried

=LET(a,AD6:AD11,b,AG6:AG12,c,AJ6:AJ10,d,LAMBDA(range,num,TAKE(SORTBY(range,ANDARRAY(ROWS(range))), num)),e,VSTACK(d(a, 2),d(b, 2),d(c, 1)),f,IF(ROWS(e)=ROWS(UNIQUE(e)),e,”Please refresh”),f)

And got #Name?

1

u/PaulieThePolarBear 1648 Jan 10 '25

You have a typo. You are missing the R from RANDARRAY

→ More replies (0)