auto assign project

[newbie_010108]

Hi, im using excel 2003. Would like to build a macro where in this will auto
assign workloads to the analysts according to their current workloads. The
idea is whoever analysts has the least # of provs in his current workloads
will be assigned a new workload base on the new inventory. All the request id
who has the biggest number of "days on hand" should assign first.
Here is the data:

--new inventory sheet--
New request id days on hand # of provs assign to

1 11 2
2 11 2
3 11 1
4 10 1
5 10 3
6 10 1
7 9 1
8 9 1
9 9 2
10 8 2
11 8 1
12 8 1
13 8 5
14 7 3
15 7 1
16 7 1

---current workloads of the analysts--- there are total of 5 analysts

analysts # of providers
Karen 3
Ferdy 5
Analyn 11
Leo 2
Edison 1

Base on the current workloads Edison has the least # of providers, so the
request id 1 should assign to him, now he will have a total of 3 providers.
So next request id 2 should assign to Leo because he has only 2 providers on
his inventory, now he will have 4 providers. Next request id 3 should assign
to Karen, which will make her have a total of 4 provs. Request id 4 will be
assign to Edison, since he has only 3 providers. and so on and so forth.....

Hope you can help me design a macro code here..... very important.

 

[Patrick Molloy]

assume jobs are in A3-A18
assume analysts are in A24-B28 (headers are in row 23)
if the analysts are sorted by the number of jobs, then the top of the list
is the next analyst to get allocate
dso for each job, get the analyst with the least jobs, increment that
analyst's jobcount and the analysts name to the job table
repeat for each job

paste this into a new standard code module:

Option Explicit
Sub assignAnalysts()
Dim job As Range
Dim analysts As Range
Set analysts = Range("A24:B28")
For Each job In Range("A3:A18").Cells
'sort analysts
analysts.Sort analysts.Range("B1")
'assingn the job
job.Offset(, 3) = analysts.Range("A1")
analysts.Range("B1") = analysts.Range("B1") + 1
Next
End Sub

 

[Patrick Molloy]

http://www.xl-expert.com/ExcelNewsGroupStuff.htm

 

[Jacob Skaria]

With your new request list in ColA:ColD and your list of analysts in
ColF:ColG the below should do what you want. Launch VBE using Alt+F11. Insert
a module and paste the below code. Try and feedback.

Dim rngAnalysts As Range
Dim strLastAT As String
Sub Mac()
Dim lngRow As Long, lnglastRow As Long
Set rngAnalysts = Range("F2:G6")

lnglastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lnglastRow
AllocateID Range("C" & lngRow), Range("D" & lngRow)
Next
End Sub

Sub AllocateID(intProv As Integer, rngPut As Range)
Dim intTemp As Integer
Dim rngTemp As Range
Dim strProvider As String
For Each Row In rngAnalysts.Rows
If Cells(Row.Row, Row.Column) <> strLastAT Then
If intTemp = 0 Then
intTemp = Cells(Row.Row, Row.Column + 1)
strProvider = Cells(Row.Row, Row.Column)
Set rngTemp = Cells(Row.Row, Row.Column + 1)
End If

If Cells(Row.Row, Row.Column + 1) < intTemp Then
intTemp = Cells(Row.Row, Row.Column + 1)
strProvider = Cells(Row.Row, Row.Column)
Set rngTemp = Cells(Row.Row, Row.Column + 1)
End If
End If
Next
rngPut = strProvider
rngTemp = intTemp + intProv
End Sub

 

[newbie_010108]

thanks! its working!

assume jobs are in A3-A18
assume analysts are in A24-B28 (headers are in row 23)
if the analysts are sorted by the number of jobs, then the top of the list
is the next analyst to get allocate
dso for each job, get the analyst with the least jobs, increment that
analyst's jobcount and the analysts name to the job table
repeat for each job

paste this into a new standard code module:

Option Explicit
Sub assignAnalysts()
Dim job As Range
Dim analysts As Range
Set analysts = Range("A24:B28")
For Each job In Range("A3:A18").Cells
'sort analysts
analysts.Sort analysts.Range("B1")
'assingn the job
job.Offset(, 3) = analysts.Range("A1")
analysts.Range("B1") = analysts.Range("B1") + 1
Next
End Sub

 

[newbie_010108]

hi jacob.. this also works! thanks! really appreciate it! but have you
checked the one from patrick molloy the code is much simpler.. wondering
whats the difference between your code and his code..

 

[newbie_010108]

Hi Patrick,

got a problem... i just notice that the job increment by 1.. it should not..
the analysts job count should increment base on # of provs assign to her.
like for ex. Karen has 0 job count. If request id 1 which has 3 provs, this
should add up to her jobcount. the increment should be base on # of prov in
that request. hope you can help me.. thanks!

 

[Patrick Molloy]

ok that wasn't clear from your initial query.

change this
analysts.Range("B1") = analysts.Range("B1") + 1

to
analysts.Range("B1") = analysts.Range("B1") + job.Offset(, 2)

 

[Patrick Molloy]

web page - with the example workbook has been updated too.
there's a check one can do here. before running the code, the sum of the
analuysts proves was 22.
After the run, that total increased to 50, and since there were 28 provs in
the new jobs, this is the correct total

 

[newbie_010108]

perfect!
thanks!

web page - with the example workbook has been updated too.
there's a check one can do here. before running the code, the sum of the
analuysts proves was 22.
After the run, that total increased to 50, and since there were 28 provs in
the new jobs, this is the correct total