I want to allocate the IDs among the 4 employees listed in “Table-Employees” to another table. The IDs are repeating in the list. Every repeating ID should be allocated to a single employee. I want the distribution to be unbiased; i.e. the count of IDs that an employee gets should be equal (almost). Can anyone help me? Thanks in advance.
ID distribute.xlsx
Does placing a 1 in the first cell and then the following formula the in B3 and then extend the formula work for you
Please Login or Register to view this content.
Let us know if you need something different for your solution
-If you think you are done, Start over - ELeGault
Thank you very much for your reply ELeGault. But unfortunately, this is not the thing exactly what I am looking for. If you see, duplicates have been allocated to different employees. E.g. M7723005 in row 3 and 13 have been allocated to “2” and “4” respectively. If I am allocating M7723005 in A3 to “Anna”, I would like it to be repeated in the list, and I don’t want “John” or anyone else to have the same ID.
When I do it manually, first of all I would find the count. Then I sort it in descending order, firstly by the count, then by ID. After that I would allocate among the four, checking each time whether all are getting equal number of IDs. However, this is so tedious. Do you have any other solution for this?
If your dataset is only 20 rows, then getting something really random is not that easy. If it's much larger then it'll work.
In this example I have first returned all the unique IDs with an array formula. This must be set with CTRL + SHIFT + ENTER. It also uses a named range. I can explain that a bit more if the solution works for you. I've then used EXCELs RANDBETWEEN function to assign a number randomly to each ID and wrapped that up with a lookup to associate a name with it.
Every time you change something (or hit F9) the random distribution repeats itself. So don't be surprised if the names and numbers change. If the small dataset is what you have, then hit F9 until you're happy with the distribution of the counts between individuals, select the values in column D, copy & paste values. this will over-write the random number generation and fix the allocation forever...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
gotchya I completely looked right past that - how about this
B2
Highly likely that we can reduce this, it is getting late - but that should do itPlease Login or Register to view this content.
IF(COUNTIFS(A$1:A2,A2)>1,"",
What we are doing here is first checking to see if this is a duplicate ID, if so be BLANK or "". If it is the first time it shows than we are going to do the following
COUNTA(B$1:B1)-COUNTIFS(B$1:B1,"")
Now I need to know how many non dup entries we have had, so I am counting total checks minus those we mark as blank since they are duplicates
Now I need to trim off sets where the Max user Value has been met. To do this I am dividing by that value and rounding it down to get the total full sets that have been achieved already and taking it out of the original count. Like this
ROUNDDOWN((COUNTA(B$1:B1)-COUNTIFS(B$1:B1,""))/MAX($F$6:$F$9),0)*MAX($F$6:$F$9)
Now if this is a full set our result will end in a 0 so I need to wrap that in an if and check all that, if it is 0 then it must be the max value of the user list, otherwise repeat the same steps to get the other values
I suppose if you wanted their name instead of their ID then you could wrap it up in a VLOOKUP
Again, I am sure I have over done the length of the formula and there is a shorter approach in there...Please Login or Register to view this content.but this lists them in order as they appear so the person in the end of the list has the highest likely hood of not getting an equal amount where the person up front will have the max possible always per user
Wow! That was a great one Glenn. Just one thing… Could you please tell me how that is possible if I want the allocation names to be placed next to the ID in the original dataset instead of the ‘Unique list’?
Like this...
Perfect! Thank you very much....
Thank you very much ELeGault…. Let me appreciate your effort.
You're welcome. Can you now mark the thread as solved?
I'd like to ask for help on how to distribute equally among individuals. There are four employees and need to have them check Fire Extinguishers monthly inspections. there are 24 buildings each with a different number of fire extinguishers. There's a total of 190, but each building has different, but the average person would have 47.5. But I'd like to keep the numbers of buildings and checks the same. So, I'm wondering how to do this on excel. I'm not going to get even numbers, but I want everyone to be close to 47. I understand some will do extra or some will do less. This is the list I'm needing. # FE Buildings
4 Bulding 1
6 Bulding 2
7 Bulding 3
6 Bulding 4
16 Bulding 5
5 Bulding 6
5 Bulding 7
3 Bulding 8
6 Bulding 9
9 Bulding 10
7 Bulding 11
6 Bulding 12
5 Bulding 13
3 Bulding 14
13 Bulding 15
11 Bulding 16
11 Bulding 17
3 Bulding 18
13 Bulding 19
13 Bulding 20
18 Bulding 21
1 Bulding 22
1 Bulding 23
18 Bulding 24
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks