Excel macro to Generate a new Worksheet with 'Task' wise Report as mentioned below

[lenin]

I have a Table with 10 columns and 21 Rows. Last column has %allocation that is 'I' Cell of each Row divided by 8.75 and multiplied by 100. Example Source table and Target table in attached file.


Target: Need a Excel macro to Generate a new Worksheet with 'Task' wise Report as mentioned below.

Plz view the pic: https://docs.google.com/file/d/0B7BtqzryKzJQbEVueE9VaGZCTWM/edit?usp=sharing

and https://docs.google.com/file/d/0B7BtqzryKzJQa3VVYVloY01obkk/edit?usp=sharing

Example file link: https://docs.google.com/file/d/0B7BtqzryKzJQc1RjSlZTaFdiWHc/edit?usp=sharing

1. Resources cell in Target file should be contains "all Resources ID's", who all are involved in a perticular task and appended with %allocation value. If a Resource involved 'Twice' the 'Avarage of two days %allocation' should be appended for corresponding resource.


Example: For Meeting, "111[20%];222[5.71%];333[94.58%];444[94.28%];555[18.57%]"
Note: "111" appended with (5.71 + 34.28)/2 = 20


2. Planned Start date Should be the "Lowest Date" of the perticular task


3. Planned Finish date Should be the "Highest Date' of the perticular task


4. Actual Start date is same as Planned start


5. Actual Finish date is same as Planned Finish


6. Actual Effort(hrs) is 'SUM' of the 'Actual Hours' (From Sours Table)of all resourses, who all are involved in a perticular task


EX for 'Meeting': 0.5+3+0.5+8.25+8.25+0.5+2.75 = 23.75


These same thing should be followed for all tasks in the source table (These table has 4 task types)
(Rest of all fields has updated automatically by formulas)


Thanks in Advance.

...