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.

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" <(E-Mail Removed)> wrote in message
news:E5C8B0D7-8C6A-4046-B4AB-(E-Mail Removed)...
> 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.

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


"Patrick Molloy" <(E-Mail Removed)> wrote in message
news:E736B01E-95A3-4511-829C-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:E5C8B0D7-8C6A-4046-B4AB-(E-Mail Removed)...
>> 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.
>

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

--
If this post helps click Yes
---------------
Jacob Skaria


"newbie_010108" wrote:

> 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.

thanks! its working!

"Patrick Molloy" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:E5C8B0D7-8C6A-4046-B4AB-(E-Mail Removed)...
> > 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.
>

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..

"Jacob Skaria" wrote:

> 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
>
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "newbie_010108" wrote:
>
> > 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.

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" wrote:

> http://www.xl-expert.com/ExcelNewsGroupStuff.htm
>
>
> "Patrick Molloy" <(E-Mail Removed)> wrote in message
> news:E736B01E-95A3-4511-829C-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:E5C8B0D7-8C6A-4046-B4AB-(E-Mail Removed)...
> >> 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.
> >

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)



"newbie_010108" <(E-Mail Removed)> wrote in message
news:E9021AF6-3B9F-46A4-9086-(E-Mail Removed)...
> 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" wrote:
>
>> http://www.xl-expert.com/ExcelNewsGroupStuff.htm
>>
>>
>> "Patrick Molloy" <(E-Mail Removed)> wrote in message
>> news:E736B01E-95A3-4511-829C-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in
>> > message
>> > news:E5C8B0D7-8C6A-4046-B4AB-(E-Mail Removed)...
>> >> 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.
>> >

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


"Patrick Molloy" <(E-Mail Removed)> wrote in message
news:C0CDF348-AEF1-460F-82F7-(E-Mail Removed)...
> 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)
>
>
>
> "newbie_010108" <(E-Mail Removed)> wrote in message
> news:E9021AF6-3B9F-46A4-9086-(E-Mail Removed)...
>> 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" wrote:
>>
>>> http://www.xl-expert.com/ExcelNewsGroupStuff.htm
>>>
>>>
>>> "Patrick Molloy" <(E-Mail Removed)> wrote in message
>>> news:E736B01E-95A3-4511-829C-(E-Mail Removed)...
>>> > 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" <(E-Mail Removed)> wrote in
>>> > message
>>> > news:E5C8B0D7-8C6A-4046-B4AB-(E-Mail Removed)...
>>> >> 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.
>>> >

perfect!
thanks!

"Patrick Molloy" wrote:

> 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
>
>
> "Patrick Molloy" <(E-Mail Removed)> wrote in message
> news:C0CDF348-AEF1-460F-82F7-(E-Mail Removed)...
> > 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)
> >
> >
> >
> > "newbie_010108" <(E-Mail Removed)> wrote in message
> > news:E9021AF6-3B9F-46A4-9086-(E-Mail Removed)...
> >> 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" wrote:
> >>
> >>> http://www.xl-expert.com/ExcelNewsGroupStuff.htm
> >>>
> >>>
> >>> "Patrick Molloy" <(E-Mail Removed)> wrote in message
> >>> news:E736B01E-95A3-4511-829C-(E-Mail Removed)...
> >>> > 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" <(E-Mail Removed)> wrote in
> >>> > message
> >>> > news:E5C8B0D7-8C6A-4046-B4AB-(E-Mail Removed)...
> >>> >> 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.
> >>> >
>