I'm new to the macro world but needs some help to create a macro that will populate multiple percentages. My raw data looks like the following:
Column 1 Column 2
[TABLE="width: 599"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Dispatched Service DEER 1[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Mode 1 DEER LAB[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Mode A - CU doesn't want to T/S DEER 3[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Mode B - CU not knowledgable to T/S DEER 5.2 [/TD]
[/TR]
[TR]
[TD]Dispatched Service - GYM 20 - No Parts to T/S DEER PRO[/TD]
[/TR]
[TR]
[TD]Dispatched Service - CRY 21 - No Time to T/S ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Tech69 - Ongoing/Repeat Issue ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service - With Help ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service with Tier 1 Support ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service with Tier 2 Support ETC[/TD]
[/TR]
[TR]
[TD]Dispatched ServiceSent im to Lin[/TD]
[/TR]
[TR]
[TD]Field Assistance[/TD]
[/TR]
[TR]
[TD]Phone Fix[/TD]
[/TR]
[TR]
[TD]Phone Fix - With Help[/TD]
[/TR]
[TR]
[TD]Phone Fix with Tier 1 Support[/TD]
[/TR]
[TR]
[TD]Phone Fix with Tier 2 Support[/TD]
[/TR]
[TR]
[TD]Referred[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
I need to calculate the % Tier Use for specific machinery. For example, My total "Dispatched" sum is about 332. Key word is "Dispatched" regardless of words that follow. Then I need to combine the Dispatched Service w/ Tier 1, Tier 2, etc with Phone fix w tier 1, tier 2, which roughly gives me sum of lets say 34. Keep in mind my list may grow every week. So the key word is "Tier" regardless of the words that follow. My % Tier Use would be 334/32= 10.2%. And I'm only evaluating the tier use for specific machinery. For example, DEER 1, DEER 3, DEER LAB, etc. All the DEERS would fall under the Tier Use of 10.2%. I have to run this calculation for 6 different specific machinery. The key things is I need to get the Tier USE %. And my list may grow so the rows may extend since i am pulling data every week.
Any Ideas? The help is truly appreciated.
Thanks
Column 1 Column 2
[TABLE="width: 599"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Dispatched Service DEER 1[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Mode 1 DEER LAB[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Mode A - CU doesn't want to T/S DEER 3[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Mode B - CU not knowledgable to T/S DEER 5.2 [/TD]
[/TR]
[TR]
[TD]Dispatched Service - GYM 20 - No Parts to T/S DEER PRO[/TD]
[/TR]
[TR]
[TD]Dispatched Service - CRY 21 - No Time to T/S ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Tech69 - Ongoing/Repeat Issue ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service - With Help ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service with Tier 1 Support ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service with Tier 2 Support ETC[/TD]
[/TR]
[TR]
[TD]Dispatched ServiceSent im to Lin[/TD]
[/TR]
[TR]
[TD]Field Assistance[/TD]
[/TR]
[TR]
[TD]Phone Fix[/TD]
[/TR]
[TR]
[TD]Phone Fix - With Help[/TD]
[/TR]
[TR]
[TD]Phone Fix with Tier 1 Support[/TD]
[/TR]
[TR]
[TD]Phone Fix with Tier 2 Support[/TD]
[/TR]
[TR]
[TD]Referred[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
I need to calculate the % Tier Use for specific machinery. For example, My total "Dispatched" sum is about 332. Key word is "Dispatched" regardless of words that follow. Then I need to combine the Dispatched Service w/ Tier 1, Tier 2, etc with Phone fix w tier 1, tier 2, which roughly gives me sum of lets say 34. Keep in mind my list may grow every week. So the key word is "Tier" regardless of the words that follow. My % Tier Use would be 334/32= 10.2%. And I'm only evaluating the tier use for specific machinery. For example, DEER 1, DEER 3, DEER LAB, etc. All the DEERS would fall under the Tier Use of 10.2%. I have to run this calculation for 6 different specific machinery. The key things is I need to get the Tier USE %. And my list may grow so the rows may extend since i am pulling data every week.
Any Ideas? The help is truly appreciated.
Thanks
