Any suggestion on how shld i do this?

[edlim85]

Hi there,

Im doing a macro to calculate growth in terms of current and previous year profits. it is perfectly easy if the list of 'agents' in the both years are the same numbers. But in reality some agents might quit say in the end previous year, hence they do not show up in current year data. vice versa for agents who join this(current) year.

Here is to illustrate my problem.
Say in "CurrentYear" ws
<table style="width: 256px; height: 89px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt;width:48pt" height="17" width="64">From</td> <td class="xl69" style="border-left:none;width:48pt" width="64"> To</td> <td class="xl69" style="border-left:none;width:48pt" width="64">Agent</td> <td class="xl70" style="border-left:none;width:48pt" width="64">Profit</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" height="17" align="right">1-Jul-10</td> <td class="xl67" style="border-top:none;border-left:none" align="right"> 30-Jun-11
</td> <td class="xl66" style="border-top:none;border-left:none">Bestway</td> <td class="xl72" style="border-top:none;border-left:none" align="right">1100</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" height="17" align="right">
</td> <td class="xl67" style="border-top:none;border-left:none" align="right">
</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl72" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" height="17" align="right">1-Jul-10</td> <td class="xl67" style="border-top:none;border-left:none" align="right">30-Jun-11
</td> <td class="xl66" style="border-top:none;border-left:none"> Alltheway</td> <td class="xl72" style="border-top:none;border-left:none" align="right">2000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl73" style="height:12.75pt;border-top:none" height="17" align="right">1-Jul-10</td> <td class="xl74" style="border-top:none;border-left:none" align="right">30-Jun-11</td> <td class="xl75" style="border-top:none;border-left:none"> AlwaysAway</td> <td class="xl76" style="border-top:none;border-left:none" align="right">3450
</td> </tr> </tbody></table>
"PreviousYear" ws
<table width="256" border="0" cellpadding="0" cellspacing="0"><tbody><tr height="17"></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt;width:48pt" height="17" width="64">From</td><td class="xl69" style="border-left:none;width:48pt" width="64"> To</td><td class="xl69" style="border-left:none;width:48pt" width="64">Agent</td><td class="xl70" style="border-left:none;width:48pt" width="64">Profit</td></tr><tr style="height:12.75pt" height="17"><td class="xl71" style="height:12.75pt;border-top:none" height="17" align="right">1-Jul-09</td><td class="xl67" style="border-top:none;border-left:none" align="right"> 30-Jun-10</td><td class="xl66" style="border-top:none;border-left:none"> Bestway</td><td class="xl72" style="border-top:none;border-left:none" align="right">800</td></tr><tr style="height:12.75pt" height="17"><td class="xl71" style="height:12.75pt;border-top:none" height="17" align="right">1-Jul-09</td><td class="xl67" style="border-top:none;border-left:none" align="right">30-Jun-10</td><td class="xl66" style="border-top:none;border-left:none"> Noway</td><td class="xl72" style="border-top:none;border-left:none" align="right">1000</td></tr><tr style="height:12.75pt" height="17"><td class="xl71" style="height:12.75pt;border-top:none" height="17" align="right">1-Jul-09</td><td class="xl67" style="border-top:none;border-left:none" align="right">30-Jun-10</td><td class="xl66" style="border-top:none;border-left:none"> Alltheway</td><td class="xl72" style="border-top:none;border-left:none" align="right">3000</td></tr><tr style="height:12.75pt" height="17"><td class="xl73" style="height:12.75pt;border-top:none" height="17" align="right">1-Jul-09</td><td class="xl74" style="border-top:none;border-left:none" align="right">30-Jun-10</td><td class="xl75" style="border-top:none;border-left:none"> AlwaysAway</td><td class="xl76" style="border-top:none;border-left:none" align="right">1450
</td></tr></tbody></table>
the 'result' ws
<table width="273" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="width:48pt" span="3" width="64"> </colgroup><tbody><tr style="height:25.5pt" height="34"> <td class="xl67" style="height:25.5pt;width:61pt" height="34" width="81">Agents</td> <td class="xl68" style="border-left:none;width:48pt" width="64">Current
Year</td> <td class="xl68" style="border-left:none;width:48pt" width="64">Previous
Year</td> <td class="xl69" style="border-left:none;width:48pt" width="64">Growth</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl70" style="height:12.75pt;border-top:none" height="17">Bestway</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1100</td> <td class="xl66" style="border-top:none;border-left:none" align="right">800</td> <td class="xl71" style="border-top:none;border-left:none" align="right">300</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl70" style="height:12.75pt;border-top:none" height="17">Alltheway</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2000</td> <td class="xl66" style="border-top:none;border-left:none" align="right">3000</td> <td class="xl71" style="border-top:none;border-left:none" align="right">-1000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl70" style="height:12.75pt;border-top:none" height="17">AlwaysAway</td> <td class="xl66" style="border-top:none;border-left:none" align="right">3450</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1450</td> <td class="xl71" style="border-top:none;border-left:none" align="right">2000</td> </tr> <tr style="height:13.5pt" height="18"> <td class="xl72" style="height:13.5pt;border-top:none" height="18">Noway</td> <td class="xl73" style="border-top:none;border-left:none" align="right">0</td> <td class="xl73" style="border-top:none;border-left:none" align="right">1000</td> <td class="xl74" style="border-top:none;border-left:none" align="right">0</td> </tr> </tbody></table>
All Agents in current year will appear in the 'result' worksheet, agents' profits in the previous will be match to the current year. and the remaining data(agents which has quit) will be compiled below the LR Result ws.
I could use Vlookup to do the matching.

how shld i go about compiling the 'remaining data' to my result ws?


Really need your valuable advise here.

Regards
Edmund

 

[venkat1926]

not clear. If "noway" was not there in previous year and only this year. his growth in result sheet must be left blank.

 

[edlim85]

hihi venkat1926,

Sorry for the trouble, I have figured it out. I use VBA to compare and find values, and embed this function into if..is nothing then.

I just started on vba barely a month and have so much questions.

Thanks for your reply!!