Resource ,Activities ,Assignment and Loading_VBA Macro request

[Elovika]

Hi,
Please can anyone help me on Resource ,Activities ,Assignment and Loading_VBA Macro request.
I'm trying to automate creation of resource assignments on excel for resource assignments to activities and budgeted units (Man hours ) loading using multiple labor resources to multiple activities using excel VBA macro....but just don't know to go about it.
I have attached an excel file that contains typical input data sheet and Output sheet after VBA macro codes has been applied.
The input data is made of 5 to 500 rows of activities , available resources and man hours to be allocated.
The output data would be a row of an activity per resource (for each revision) with corresponding allocated man hours based on %.
The attached file has all the details.
Regards,

 

[vletm]

Elovika
I gotta modify Your Input-sheet.
Did You mean to paste data based range E11:AB16 to output-sheet?
Select Your output-sheet to get Your output.

 

[Elovika]

Vletm,
Many thanks for your quick response!
Much appreciated.
Yes please paste data based range E11:AB16 to output -sheet.
Output -sheet would be essentially B2:E77 for this example.
The data to populate are specifically are B2, C2, D2, E2 and so on till the 77th row.
Additionally, the solution should flexibly accommodate over 6 activities in the example.
It should take care of say about 500 activities categories made up of 4 rows each.
See attached updated file

 

[vletm]

Elovika
What was missing?

Did You select 'normal' output-sheet?
=== You could delete that sheet which ends with (o)! It's Your original output-sheet ===
... except
#1 Activity ID is plus one - I had to guess
#2 eg D01 CTR Catalogue <> D01 CTR ... Your data don't have that ( there are some other same kind of differences )

Why do You need to have those formulas eg in cell E11 in Your input-sheet?

 

[Elovika]

Hi,
Thanks again for your response.
Oh i missed that ; yes i can use the normal output sheet.
#1 Activity ID is plus one - I had to guess......Yes This can be increased by 1 or 10( the output should contain 4 rows for each activity with same Activity ID)
#2 eg D01 CTR Catalogue <> D01 CTR ... Your data don't have that ( there are some other same kind of differences )...
D01 , D02, D03, C01 are revisions contained in the "Input Data sheet"

Typical inputs are:

CTR
Project Schedule
PEFS

Output:
D01 CTR
D02 CTR
D03 CTR
C01 CTR
D01 Project Schedule
D02 Project Schedule
D03 Project Schedule
C01 Project Schedule
ETC
Why do You need to have those formulas eg in cell E11 in Your input-sheet?
Ok let me throw more light here:
The Budgeted Unit in hours for Engineering and Design (B11 - C16 ) are prorated D01(60%), D02(15%), D03(15%) C01(10%)
For "Engineering hours" , D01, D02, D03, C01 are prorated amongst resource IDs R03,R04 and R05 on 10%, 30% and 60% sharing ratio respectively.
For "Design hours" ONLT Resource R06 gets 60%, 15%,15%,10% for D01, D02,D03,C01.
Hope this clarifies.
Thanks a lot.

 

[vletm]

Elovika
What was missing?
... You didn't name ... anything ... okay?
#1 Your file shows that those have increased by 1.
You sample output rows number depend number of allocated resource IDs ... You wrote should contain 4 rows for each activity ... hmm?
#2 I refer with Your sample file's output which has different Resource Names than in Allocation Rules ... hmm?
# Formulas ..
Should I figure that ... You're solving something with those?
Those Your giving red writing could read from Allocation Rules ...

Could You try to give clear answers ...

1. Are those Yellow cells (below) data, which will give manually?
2. Do You really need double times those IDs eg R001 (in cells D11 & E8)
   ... why eg CTR has allocated to 4 x 2 IDs ... isn't to D01 enough?
3. How would below layout (as well as Your sample) will change, if there would be ID R007?
   ... this time - I don't ask case, if there would be 100 IDs.
4. Could Activity Name CTR allocate to ID R006?
   ... or do someone that allocation manually?
   1. If manually then some (eg #2) of question has solved.
5. If those allocated values are time (E11:AB16) , then those should show as time as below
   - or what kind of clocks do You use there?.
   

 

[vletm]

Elovika
You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

There are clear sentences, if someone uses Cross-Posting as You've done.
Ps. Same kind of rules are in every Forum.

 

[Elovika]

Hi,
Apologies for the late response.
And please pardon me for the initial unclear series of explanations.....
Please see my responses in bold below in the body of you questions:
What was missing?
... You didn't name ... anything ... okay?
#1 Your file shows that those have increased by 1.
You sample output rows number depend number of allocated resource IDs ... You wrote should contain 4 rows for each activity ... hmm?
#2 I refer with Your sample file's output which has different Resource Names than in Allocation Rules ... hmm?
# Formulas ..
Should I figure that ... You're solving something with those?
Those Your giving red writing could read from Allocation Rules ...YOU ARE SPOT ON; PLEASE THE NUMBER OF ROWS DEPENDS ON THE # OF ALLOCATED RESOURCES

Could You try to give clear answers ...

1. Are those Yellow cells (below) data, which will give manually?....YES
2. Do You really need double times those IDs eg R001 (in cells D11 & E8)
   ... why eg CTR has allocated to 4 x 2 IDs ... isn't to D01 enough?....Let me throw some light here ...2 resources(R01 &R02) are allocated to deliver 4 revisions (D01, D02 ,D02 C01) for "CTR" and "Project schedule"" And the formula for the man hours usage are indicated.
3. How would below layout (as well as Your sample) will change, if there would be ID R007?
   ... this time - I don't ask case, if there would be 100 IDs.
4. Could Activity Name CTR allocate to ID R006?
   ... or do someone that allocation manually? MANUALLY
   1. If manually then some (eg #2) of question has solved.
5. If those allocated values are time (E11:AB16) , then those should show as time as below
   - or what kind of clocks do You use there?. No, they are in hours duration not timestamps. eg 16.48 hours means 2.06 days on the basis of 8 hours per day
   
   

 

[vletm]

Elovika
Seems You have skipped those my blue writings ...
as well as ...
2. Do You really ... ? ... It's still very dark there.
... D-column - from row 11 has those IDs.
3. You wrote that there could be 100 IDs - I tried to get answer if there will be seven IDs ...
... but ... do You have some kind of image - what will it mean if You'll use Your layout?
5. Your original value for E11 is 16.8 ... there could be some challenges to figure it with 'normal' clock
-- that's why I offered 16:48 = 16hrs 48minutes ... next You wrote that should handle as 2.06 days ... hmm?
Do You mean two days á 8hrs and from third day 48minutes?
Do You have a clear idea - where do You need any of those values with input-sheet at all?
... and what values would be useful in output-sheet?

Did You skip my #7 reply?

Actually, it's You, who needs those answers, that someone else could try to help You.

 

[vletm]

Elovika
I did some tests based Your file and my guesses.
Do You have any answers or questions?

 

[Elovika]

Hi Vletm,
Apologies i have been out of town since Friday 18th,(very bad and intermittent network connections in the remote locations); arrived back to base today.
Thanks again and again for your kind assistance.
Will provide answers to your last sets of responses respectively:

Seems You have skipped those my blue writings ...
as well as ...I already addressed them when i said they are in hours duration not timestamps. eg 16.48 hours means 2.06 days on the basis of 8 hours per day and furthermore clock will not work with the intent : Resource loading on Primavera P6 scheduler
2. Do You really ... ? ... It's still very dark there.
... D-column - from row 11 has those IDs. Already addressed above
3. You wrote that there could be 100 IDs - I tried to get answer if there will be seven IDs ...Yes we can have up to 100 resources for different activity names
... but ... do You have some kind of image - what will it mean if You'll use Your layout?
5. Your original value for E11 is 16.8 ... there could be some challenges to figure it with 'normal' clock
-- that's why I offered 16:48 = 16hrs 48minutes ... next You wrote that should handle as 2.06 days ... hmm?
Do You mean two days á 8hrs and from third day 48minutes? Already addressed above; clocking NOT supported for the intent.
Do You have a clear idea - where do You need any of those values with input-sheet at all? Ok please let me keep it simple the input will be manually created by me and the Macro should create the Output sheet for resource lading on Primavera P6 scheduler
... and what values would be useful in output-sheet? Output should be with the template i sent earlier

Did You skip my #7 reply?

Actually, it's You, who needs those answers, that someone else could try to help You. Clocking is NOT supported on the Budgeted Unit column

Additionally, The macro should use the input sheet (provided earlier) to generate the output sheet (in the example format )
Reattached again.

 

[Elovika]

Elovika
I did some tests based Your file and my guesses.
Do You have any answers or questions?

Hi,
WOW! just seeing this!
Many thanks.
However, kindly tweak the macro to use ""Input sheet"" and ""Rescure IDs as inputs"" as inputs and create ""Output sheet""

 

[vletm]

Elovika
I read Your writings and I asked from myself
- did I find something new or useful?
You've repeated same phrases.
You even have skipped my question - what is missing?
Here, that means - everything is as I've written.
... and while I'm trying to write some positive writings
... You wrote something.

As I've written:
Actually, it's You, who needs those answers, that someone else could try to help You.

 

[Elovika]

Elovika
I read Your writings and I asked from myself
- did I find something new or useful?
You've repeated same phrases.
You even have skipped my question - what is missing?
Here, that means - everything is as I've written.
... and while I'm trying to write some positive writings
... You wrote something.

As I've written:
Actually, it's You, who needs those answers, that someone else could try to help You.

I was just providing answers in the body of your questions for others as well..........

 

[vletm]

Elovika
based Your #12 writing
... seems You skipped to test it
based You #14 writing
... seems You skipped to read my #13 reply
I can continue my own testings with this - if I'll have free time - but who knows about You.

 

[Elovika]

Hi Vletm,
Wow! I must applaud you for this effort. Thank you very much . I was finally able to figure out the Macro you wrote earlier.
Please pardon the earlier disconnects......
There is "Confirm Refresh " comment on the Output sheet that updates based on inputs sheet & Rescure IDs sheet.
We are almost there.
Some slight tweaking required for the Output sheet" "to produce desired data.
The attached shows the required sequence of activity name ,Resource IDs and Activity IDs on Öuput _Corrected sheet. (I have again created the proper output(Output_corrected))
Finally, it it possible to have the budgeted units(Column E) in Days ONLY? I can easily convert to hours duration later.
Thanks a lot.

 

[Elovika]

Hi Vletm,
Wow! I must applaud you for this effort. Thank you very much . I was finally able to figure out the Macro you wrote earlier.
Please pardon the earlier disconnects......
There is "Confirm Refresh " comment on the Output sheet that updates based on inputs sheet & Rescure IDs sheet.
We are almost there.
Some slight tweaking required for the Output sheet" "to produce desired data.
The attached shows the required sequence of activity name ,Resource IDs and Activity IDs on Öuput _Corrected sheet. (I have again created the proper output(Output_corrected))
Finally, it it possible to have the budgeted units(Column E) in Days ONLY? I can easily convert to hours duration later.
Thanks a lot.

please see attachment.

 

[vletm]

Elovika
You've still missed many my questions/wonderings
... below some of those:

What was missing?

What have You changed from output-sheet?

Your (I have again created the proper output(Output_corrected))
=>> mission impossible as written in #4 reply:
#2 eg D01 CTR Catalogue <> D01 CTR ... Your data don't have that ( there are some other same kind of differences )

Your Finally, it it possible to have the budgeted units(Column E) in Days ONLY? I can easily convert to hours duration later.
=>> Have Your check - what is Your given the proper output?
=>> Easy means double work.

#7 reply

 

[Elovika]

Hi Vletm(Excel Ninja),
Trust you are doing great!
Thanks again and again for your kind assistance.
With reference to your series of questions and advise; i had to start all over from your very first reply(#1 reply) ; funny enough i discovered you were spot on wrt to the data provided in the my 'resource Loading input data"; the corresponding out put sheet "Macro' worked perfectly!
I apologies here for not spotting this earlier; this is because I'm new to excel VBA Macro.
However, i ran into some debug error when i tested with more resources(18 resources as opposed to initial 6 resources) and more activities(46 activities as opposed to initial 6)...kept having same debug error on the output sheet VBA MACRO script as shown below; though also got some some output that stopped at the 15th activity(on row 25 (in the input sheet ) with 8 resources; the row is actually colored yellow for you to spot.
I have also attached the file with 18 resources and 46 activities.
Kindly help check what has gone wrong.
PLEEEEEEEEEEEEEEEEEEEEEEEEESSSSSSSSSSSSSSSSSE HEEEEEEEEEEEEEEEEEEEEEEP.

c = WorksheetFunction.Match(datas(2), .Range("B:B"), 0)

 

[Elovika]

Hi Vletm(Excel Ninja),
Trust you are doing great!
Thanks again and again for your kind assistance.
With reference to your series of questions and advise; i had to start all over from your very first reply(#1 reply) ; funny enough i discovered you were spot on wrt to the data provided in the my 'resource Loading input data"; the corresponding out put sheet "Macro' worked perfectly!
I apologies here for not spotting this earlier; this is because I'm new to excel VBA Macro.
However, i ran into some debug error when i tested with more resources(18 resources as opposed to initial 6 resources) and more activities(46 activities as opposed to initial 6)...kept having same debug error on the output sheet VBA MACRO script as shown below; though also got some some output that stopped at the 15th activity(on row 25 (in the input sheet ) with 8 resources; the row is actually colored yellow for you to spot.
I have also attached the file with 18 resources and 46 activities.
Kindly help check what has gone wrong.
PLEEEEEEEEEEEEEEEEEEEEEEEEESSSSSSSSSSSSSSSSSE HEEEEEEEEEEEEEEEEEEEEEEP.

c = WorksheetFunction.Match(datas(2), .Range("B:B"), 0)

In addition, see error snip below:

 

[vletm]

Elovika
based Your Kindly help check what has gone wrong.
Answer:
When do You start to real my writings?
I've asked few times that case - which I've notice a long time.
That old version won't work!
You could try that data with version in #10 reply .

 

[vletm]

Elovika
I added Your new data end of old data.
I modified some code - seems to work now.

 

[Elovika]

Vletm ,
You are the excel Ninja!
Much appreciated.
Thank you so much.
However, kindly help return the Rev codes(D01, D02,D03,C01) on the activities.
The output don't have them anymore

 

[vletm]

Elovika
Still You've skipped to give answers ...

 

[Elovika]

Hi Vletm,
Many thanks again for your efforts.
Now before my comments let me attempt to give answers to your earlier questions; hope im clear enough on this.
WRT to your earlier questions"2 eg D01 CTR Catalogue <> D01 CTR ... Your data don't have that ( there are some other same kind of differences ) "
My response: D01, D02,D03 Co1 of the activities are meant to be derived from the input data as you have done in the output data.
However, the output is till NOT coming out as desired.
Your preferred solution is much better and automated version of what i thought earlier .
You have made life easier for me ; i don't have to manually enter "calculated" man hours for the revisions (D01, D02, D03, C01) for the resources.
You macro does that for me excellently!
All activities' rows are very correct (depends on # of resources allocated to each activity)
All budgeted units are correct
The only snag left to conclude this macro automation is to figure out proper labeling for the activities.

See illustration below (to the right of the activity Name):

Activity ID	Resource ID	(*)Resource Name	(*)Activity Name Remarks	Budgeted Units
A0001	R001	Senior Planning engineer	D01 CTR Correct	16:48​
A0001	R002	Planning engineer	D02 CTR should be D01 CTR	16:48​
A0002	R001	Senior Planning engineer	D01 CTR should be D02 CTR	4:12​
A0002	R002	Planning engineer	D02 CTR Correct	4:12​
A0003	R001	Senior Planning engineer	D01 CTR should be D03 CTR	4:12​
A0003	R002	Planning engineer	D02 CTR, Should be D03 CTR	4:12​
A0004	R001	Senior Planning engineer	D01 CTR, Should C01 CTR	2:48​
A0004	R002	Planning engineer	D02 CTR, Should be C01 CTR	2:48​

My suggestions:

Configure all allocated resources to revision e.g 4 resources to D01(60%), 4 resources to D02(15%), 4 Resources to D03 (15%) and 4 resources to C01(10%).
If this can be done for all activities rows then we should home and dry.