Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. Is this as difficult as it appears? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare records in multiple sheet -> report | Excel Discussion (Misc queries) | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
how do i delete multiple records from my database | Excel Discussion (Misc queries) | |||
Counting Unique Records with multiple conditions | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) |