ExcelBanter - divide records equally among multiple employees

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - divide records equally among multiple employees (https://www.excelbanter.com/excel-worksheet-functions/107238-divide-records-equally-among-multiple-employees.html)

divide records equally among multiple employees

each employee gets 200 records. An expression using the MOD function and the
record number will do what you want.

"Martin Leffler" wrote in message
...
example: I have 1000 sorted records. I have 5 employees. I want 1st record
to
go to emp 1; 2nd record to emp 2 ... 5th record to 5th emp. Then I want to
start new assignment of records: emp 2 gets 1st rec; emp 3 gets 2nd
rec....
emp 1 gets 5th record, thus rotating which emp gets first record. Is this
as
difficult as it appears?

divide records equally among multiple employees

Here is a nested IF statement that could work for you also. This presumes
your records start on row 1, you can put this in ANY cell on row 1 and then
extend it down through all of the rows your records are on.
=IF(ROW(A1)<1000/5*1+1,"Employee #1",IF(ROW(A1)<1000/5*2+1,"Employee
#2",IF(ROW(A1)<1000/5*3+1,"Employee #3",IF(ROW(A1)<1000/5*4+1,"Employee
#2","Employee #5"))))

If your records start somewhere farther down the sheet, Row 10 for this
example, you can adjust it a couple of ways, one would be like this:
=IF(ROW(A10)-9<1000/5*1+1,"Employee #1",IF(ROW(A10)-9<1000/5*2+1,"Employee
#2",IF(ROW(A10)-9<1000/5*3+1,"Employee #3",IF(ROW(A10)-9<1000/5*4+1,"Employee
#2","Employee #5"))))

the 1000 is the number of records, the 5 is for number of employees. Just
one alternative.

"Martin Leffler" wrote:

example: I have 1000 sorted records. I have 5 employees. I want 1st record to
go to emp 1; 2nd record to emp 2 ... 5th record to 5th emp. Then I want to
start new assignment of records: emp 2 gets 1st rec; emp 3 gets 2nd rec....
emp 1 gets 5th record, thus rotating which emp gets first record. Is this as
difficult as it appears?

divide records equally among multiple employees

"Martin Leffler" skrev i en
meddelelse ...
example: I have 1000 sorted records. I have 5 employees. I want 1st record
to
go to emp 1; 2nd record to emp 2 ... 5th record to 5th emp. Then I want to
start new assignment of records: emp 2 gets 1st rec; emp 3 gets 2nd
rec....
emp 1 gets 5th record, thus rotating which emp gets first record. Is this
as
difficult as it appears?

Hello Martin

If I have understood you correctly, here is one way.
Assuming records in A1:A1000 and names of employees in D1:H1

1. In D2 enter this formula as one line:
=INDEX($A$1:$A$1000,(ROW()-ROW($D$2))*5+MOD(5-(MOD(ROW()-ROW($D$2),5)-(COLUMN()-COLUMN($D$2))),5)+1)
2. Copy D2 to E2:H2 with the fill handle (the littel square in the
lower right corner of the cell)
3. Copy D2:H2 to D201:H201 with the fill handle

Just curious :-)
Why not give them every fifth record?

--
Best regards
Leo Heuser

Followup to newsgroup only please.

divide records equally among multiple employees

example: I have 1000 sorted records. I have 5 employees. I want 1st
record to go to emp 1; 2nd record to emp 2 ... 5th record to 5th emp.
Then I want to start new assignment of records: emp 2 gets 1st rec;
emp 3 gets 2nd rec.... emp 1 gets 5th record, thus rotating which emp
gets first record. ...

Maybe something like this would help:
=CHOOSE(MOD(ROW(),5)+1,"Pat","Leslie","Evelyn","Ch ris","Glenn")

After reading the original post more carefully, I noticed that the
"CHOOSE" should've been more like this:

=CHOOSE(MOD(ROW()-1+INT((ROW()-1)/5),5)+1,
"Pat","Leslie","Evelyn","Chris","Glenn")