thedeadzeds
Active Member
- Joined
- Aug 16, 2011
- Messages
- 442
- Office Version
- 365
- Platform
- Windows
Hi Guys,
I've been using excel to allocate work equally. Up until now this works ok but the formula i am using essentially looks at a list of work, looks at the staff members in the list called Blue Team Names and allocates out evenly. There are 2 tables below. To give you an idea the below formulas are used in the data table:
Column D (Additional work) =IF(OR(B2=B1, B1=B2, B2=B3), "Yes", "")
This column essentially looks at the number in the reg columns and if this is equal to the number above or below, then shows YES. Essentially picks up duplicates.
Column E (Allocated) =IF(COUNTIF(C$2:C2,C2)<=INT(COUNTIF($C$2:INDEX(C:C,MATCH(REPT("Z",255),C:C)),C2)/COUNTA(Blue_Team_Names[Name Lookup]))*COUNTA(Blue_Team_Names[Name Lookup]),INDEX(Blue_Team_Names[Name Lookup],MOD(COUNTIF(C$2:C2,C2)-1,COUNTA(Blue_Team_Names[Name Lookup]))+1),"Not allocated")
This column looks at all the types of work in the type column and equally allocates out the type of work based on the names in the blue team name list.
Column F (Allocated 2) =IF(D2="Yes",INDEX(E:E,MATCH(B2,B:B,0)),E2)
This column looks at the additional work column and if there is a yes, then it pulls in the name from the allocated column so essentially that person will be allocated all same regno numbers.
This works great but ideally i want to be able to allocate based on what i enter in the table below (allocate table).
I'm not even sure if this is possible but i hope this makes sense.
Many thanks
Allocate Table
<tbody>
</tbody>
DATA TABLE BELOW:
<tbody>
</tbody>
I've been using excel to allocate work equally. Up until now this works ok but the formula i am using essentially looks at a list of work, looks at the staff members in the list called Blue Team Names and allocates out evenly. There are 2 tables below. To give you an idea the below formulas are used in the data table:
Column D (Additional work) =IF(OR(B2=B1, B1=B2, B2=B3), "Yes", "")
This column essentially looks at the number in the reg columns and if this is equal to the number above or below, then shows YES. Essentially picks up duplicates.
Column E (Allocated) =IF(COUNTIF(C$2:C2,C2)<=INT(COUNTIF($C$2:INDEX(C:C,MATCH(REPT("Z",255),C:C)),C2)/COUNTA(Blue_Team_Names[Name Lookup]))*COUNTA(Blue_Team_Names[Name Lookup]),INDEX(Blue_Team_Names[Name Lookup],MOD(COUNTIF(C$2:C2,C2)-1,COUNTA(Blue_Team_Names[Name Lookup]))+1),"Not allocated")
This column looks at all the types of work in the type column and equally allocates out the type of work based on the names in the blue team name list.
Column F (Allocated 2) =IF(D2="Yes",INDEX(E:E,MATCH(B2,B:B,0)),E2)
This column looks at the additional work column and if there is a yes, then it pulls in the name from the allocated column so essentially that person will be allocated all same regno numbers.
This works great but ideally i want to be able to allocate based on what i enter in the table below (allocate table).
I'm not even sure if this is possible but i hope this makes sense.
Many thanks
Allocate Table
| Dealer | Type | Bethan | Nicole F | Sonia | Hannah | Jodie | Not Allocated | Available to allocate | Total Allocated | Check |
| CVW | POLK 1st Wave Service Contacts First Attempt Made | 2 | 1 | 2 | 0 | 2 | 0 | 7 | 7 | Ok |
| CVW | POLK 1st Wave Service Contacts New Call | 0 | 1 | 0 | 1 | 0 | 0 | 2 | 2 | Ok |
| CVW | POLK Cam Belt Due First Attempt Made | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Ok |
| CVW | POLK Cam Belt Due New Call | 1 | 0 | 0 | 1 | 0 | 0 | 2 | 2 | Ok |
| CVW | POLK 1st Wave MOT Contacts First Attempt Made | 1 | 0 | 0 | 1 | 1 | 0 | 3 | 3 | Ok |
| CVW | 3rd Chance MOT First Attempt Made | 1 | 0 | 0 | 0 | 1 | 0 | 2 | 2 | Ok |
| CVW | POLK Brake Fluid Change First Attempt Made | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | Ok |
| BVW | POLK 1st Wave Service Contacts First Attempt Made | 0 | 1 | 1 | 1 | 0 | 0 | 3 | 3 | Ok |
| BVW | POLK 1st Wave Service Contacts New Call | 3 | 3 | 3 | 0 | 0 | 0 | 9 | 9 | Ok |
| BVW | POLK Cam Belt Due First Attempt Made | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | Ok |
| BVW | POLK Cam Belt Due New Call | 0 | 1 | 0 | 1 | 1 | 0 | 3 | 3 | Ok |
| BVW | POLK 1st Wave MOT Contacts First Attempt Made | 1 | 1 | 1 | 0 | 0 | 0 | 3 | 3 | Ok |
| BVW | 3rd Chance MOT First Attempt Made | 1 | 4 | 1 | 1 | 2 | 0 | 9 | 9 | Ok |
| BVW | POLK Brake Fluid Change First Attempt Made | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | Ok |
| CVW | POLK 1st Wave Service Contacts First Attempt Made | 1 | 1 | 0 | 1 | 4 | 0 | 7 | 7 | Ok |
| CVW | POLK 1st Wave Service Contacts New Call | 1 | 1 | 0 | 0 | 0 | 0 | 2 | 2 | Ok |
| CVW | POLK Cam Belt Due First Attempt Made | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Ok |
| CVW | POLK Cam Belt Due New Call | 2 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | Ok |
| CVW | POLK 1st Wave MOT Contacts First Attempt Made | 1 | 1 | 1 | 0 | 0 | 0 | 3 | 3 | Ok |
| CVW | 3rd Chance MOT First Attempt Made | 2 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | Ok |
| CVW | POLK Brake Fluid Change First Attempt Made | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | Ok |
<tbody>
</tbody>
DATA TABLE BELOW:
| Dealer | RegNo | Type | Additional Work | Allocated | Allocated 2 |
| CVW | 1 | POLK 1st Wave Service Contacts First Attempt Made | Yes | Bethan | Bethan |
| NSK | 1 | POLK 1st Wave Service Contacts First Attempt Made | Yes | Nicole F | Bethan |
| MCH | 1 | POLK 1st Wave Service Contacts First Attempt Made | Yes | Sonia | Bethan |
| CVW | 2 | POLK 1st Wave Service Contacts First Attempt Made | Yes | Hannah | Hannah |
| NSK | 2 | POLK 1st Wave Service Contacts First Attempt Made | Yes | Jodie | Hannah |
| CVW | 3 | POLK 1st Wave Service Contacts First Attempt Made | Bethan | Bethan | |
| CVW | 4 | POLK 1st Wave Service Contacts First Attempt Made | Nicole F | Nicole F | |
| CVW | 5 | POLK 1st Wave Service Contacts First Attempt Made | Sonia | Sonia | |
| MCH | 6 | POLK 1st Wave Service Contacts First Attempt Made | Hannah | Hannah | |
| BVW | 7 | POLK 1st Wave Service Contacts First Attempt Made | Jodie | Jodie | |
| CF | 8 | POLK 1st Wave Service Contacts First Attempt Made | Bethan | Bethan | |
| BVW | 9 | POLK 1st Wave Service Contacts First Attempt Made | Nicole F | Nicole F | |
| CF | 10 | POLK 1st Wave Service Contacts First Attempt Made | Sonia | Sonia | |
| CVW | 11 | POLK 1st Wave Service Contacts First Attempt Made | Hannah | Hannah | |
| NSK | 12 | POLK 1st Wave Service Contacts First Attempt Made | Jodie | Jodie | |
| CVW | 13 | POLK 1st Wave Service Contacts First Attempt Made | Not allocated | Not allocated | |
| BVW | 14 | POLK 1st Wave Service Contacts First Attempt Made | Not allocated | Not allocated | |
| MCH | 15 | POLK 1st Wave Service Contacts First Attempt Made | Yes | Not allocated | Not allocated |
| NSK | 15 | POLK 1st Wave Service Contacts First Attempt Made | Yes | Not allocated | Not allocated |
| CVW | 15 | POLK 1st Wave Service Contacts New Call | Yes | Bethan | Not allocated |
| BVW | 15 | POLK 1st Wave Service Contacts New Call | Yes | Nicole F | Not allocated |
| CF | 19 | POLK 1st Wave Service Contacts New Call | Sonia | Sonia | |
| CF | 20 | POLK 1st Wave Service Contacts New Call | Hannah | Hannah | |
| CF | 21 | POLK 1st Wave Service Contacts New Call | Jodie | Jodie | |
| NSK | 22 | POLK 1st Wave Service Contacts New Call | Bethan | Bethan | |
| BVW | 23 | POLK 1st Wave Service Contacts New Call | Nicole F | Nicole F | |
| MCH | 24 | POLK 1st Wave Service Contacts New Call | Sonia | Sonia | |
| BVW | 25 | POLK 1st Wave Service Contacts New Call | Hannah | Hannah | |
| BVW | 26 | POLK 1st Wave Service Contacts New Call | Jodie | Jodie | |
| CF | 27 | POLK 1st Wave Service Contacts New Call | Bethan | Bethan | |
| NSK | 28 | POLK 1st Wave Service Contacts New Call | Nicole F | Nicole F | |
| CF | 29 | POLK 1st Wave Service Contacts New Call | Sonia | Sonia | |
| NSK | 30 | POLK Cam Belt Due First Attempt Made | Not allocated | Not allocated | |
| CF | 31 | POLK Cam Belt Due First Attempt Made | Not allocated | Not allocated | |
| BVW | 32 | POLK Cam Belt Due First Attempt Made | Not allocated | Not allocated | |
| MCH | 33 | POLK Cam Belt Due First Attempt Made | Not allocated | Not allocated | |
| BVW | 34 | POLK Cam Belt Due New Call | Bethan | Bethan | |
| CF | 35 | POLK Cam Belt Due New Call | Nicole F | Nicole F | |
| CF | 36 | POLK Cam Belt Due New Call | Sonia | Sonia | |
| BVW | 37 | POLK Cam Belt Due New Call | Hannah | Hannah | |
| NSK | 38 | POLK Cam Belt Due New Call | Yes | Jodie | Jodie |
| BVW | 38 | POLK Cam Belt Due New Call | Yes | Not allocated | Jodie |
| CVW | 38 | POLK Cam Belt Due New Call | Yes | Not allocated | Jodie |
| CVW | 38 | POLK Cam Belt Due New Call | Yes | Not allocated | Jodie |
| NSK | 42 | POLK 1st Wave MOT Contacts First Attempt Made | Bethan | Bethan | |
| NSK | 43 | POLK 1st Wave MOT Contacts First Attempt Made | Nicole F | Nicole F | |
| CVW | 44 | POLK 1st Wave MOT Contacts First Attempt Made | Sonia | Sonia | |
| BVW | 45 | POLK 1st Wave MOT Contacts First Attempt Made | Hannah | Hannah | |
| MCH | 46 | POLK 1st Wave MOT Contacts First Attempt Made | Jodie | Jodie | |
| BVW | 47 | POLK 1st Wave MOT Contacts First Attempt Made | Bethan | Bethan | |
| NSK | 48 | POLK 1st Wave MOT Contacts First Attempt Made | Nicole F | Nicole F | |
| BVW | 49 | POLK 1st Wave MOT Contacts First Attempt Made | Sonia | Sonia | |
| CVW | 50 | POLK 1st Wave MOT Contacts First Attempt Made | Hannah | Hannah | |
| CVW | 51 | 3rd Chance MOT First Attempt Made | Bethan | Bethan | |
| BVW | 52 | 3rd Chance MOT First Attempt Made | Nicole F | Nicole F | |
| BVW | 53 | 3rd Chance MOT First Attempt Made | Sonia | Sonia | |
| BVW | 54 | 3rd Chance MOT First Attempt Made | Hannah | Hannah | |
| NSK | 55 | 3rd Chance MOT First Attempt Made | Jodie | Jodie | |
| MCH | 56 | 3rd Chance MOT First Attempt Made | Bethan | Bethan | |
| CF | 57 | 3rd Chance MOT First Attempt Made | Nicole F | Nicole F | |
| BVW | 58 | POLK 1st Wave Service Contacts New Call | Hannah | Hannah | |
| BVW | 59 | POLK 1st Wave Service Contacts New Call | Jodie | Jodie | |
| MCH | 60 | POLK 1st Wave Service Contacts New Call | Bethan | Bethan | |
| CVW | 61 | POLK 1st Wave Service Contacts New Call | Nicole F | Nicole F | |
| BVW | 62 | POLK 1st Wave Service Contacts New Call | Sonia | Sonia | |
| NSK | 63 | POLK Brake Fluid Change First Attempt Made | Not allocated | Not allocated | |
| CVW | 64 | POLK Brake Fluid Change First Attempt Made | Not allocated | Not allocated | |
| NSK | 65 | POLK Brake Fluid Change First Attempt Made | Not allocated | Not allocated | |
| BVW | 66 | POLK Brake Fluid Change First Attempt Made | Not allocated | Not allocated | |
| CVW | 67 | POLK 1st Wave MOT Contacts First Attempt Made | Jodie | Jodie | |
| CVW | 68 | 3rd Chance MOT First Attempt Made | Sonia | Sonia | |
| BVW | 69 | 3rd Chance MOT First Attempt Made | Hannah | Hannah | |
| BVW | 70 | 3rd Chance MOT First Attempt Made | Jodie | Jodie | |
| BVW | 71 | 3rd Chance MOT First Attempt Made | Bethan | Bethan | |
| NSK | 72 | 3rd Chance MOT First Attempt Made | Nicole F | Nicole F | |
| MCH | 73 | 3rd Chance MOT First Attempt Made | Sonia | Sonia | |
| CF | 74 | 3rd Chance MOT First Attempt Made | Hannah | Hannah | |
| BVW | 75 | POLK 1st Wave Service Contacts New Call | Hannah | Hannah | |
| BVW | 76 | POLK 1st Wave Service Contacts New Call | Jodie | Jodie | |
| BVW | 77 | 3rd Chance MOT First Attempt Made | Jodie | Jodie | |
| BVW | 78 | 3rd Chance MOT First Attempt Made | Not allocated | Not allocated | |
| BVW | 79 | 3rd Chance MOT First Attempt Made | Not allocated | Not allocated | |
| NSK | 80 | 3rd Chance MOT First Attempt Made | Not allocated | Not allocated | |
| MCH | 81 | 3rd Chance MOT First Attempt Made | Not allocated | Not allocated |
<tbody>
</tbody>
Last edited:
