Create a Macro to Calculate Percentages

[Huizar]

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

 

[Veritan]

Hello Huizar, see if this code will work for you. Just insert it into a module and then run it. I'm not certain how the "DEERS" affects the count. If the code below doesn't do what you need, can you give more information on how the "DEER 1", "DEER 3", "DEER LAB" and so on affect the counts?

Sub Percentage()
    Dim r As Range, Dispatched%, Tier%, TierUse#
    
    [COLOR=#008000]'Change the cell references below to be the column and row where your data starts.[/COLOR]
    For Each r In Range("A1:A" & Range("A1").End(xlDown).Row)
        If Left(r, 10) = "Dispatched" Then Dispatched = Dispatched + 1
        [COLOR=#008000]'The second condition below will cause the line to only count cells that DO have
        'the word "Tier" and DON'T have the word "Dispatched". Remove the second condition
        'if you want to include all cells that have the word "Tier".[/COLOR]
        If InStr(r, "Tier") <> 0 And InStr(r, "Dispatched") = 0 Then Tier = Tier + 1
    Next r
    
    With Range("A1").End(xlDown)
        .Offset(2) = "Tier Use %"
        .Offset(2, 1) = Tier / Dispatched
        .Offset(2, 1).NumberFormat = "0.0%"
    End With
End Sub

 

[Huizar]

Hey Veritan,

The Deer 1, Deer 2, will change the count on the amount of Dispatches and Tier counts; I'm basically grouping all the products because they belong in the same family. If my raw data was grouped by product line it would be easier. So I have to find all the products that contain key words "DEER". I hope this makes sense. If i'm reading the code right, and I have not tested it yet, it would provide the Tier % based on on all the raw data.

Thanks

 

[Huizar]

Hey Varittan, Code did not seem to work for me. It gave me a Run-Time error "6': Overflow

 

[Veritan]

I think I'm starting to understand what you're asking, but it's still unclear. You say that you have 6 machines to run this for. Are they all some variation on "DEER XXX"? Or do they start with another common name (i.e., do you have any machines named TURTLE 1, OCTOPUS 3.4, KANGAROO LAB, SQUID PRO, etc.?) In your original post, you stated that your "Dispatched" sum was ~332. How did you arrive at this number? Is it the number of cells that contain the words "Dispatched" and a specific variation on "DEER"? Is it the total cells that have "Dispatched" regardless of anything having to do with "DEER"? Does the variation of "DEER" affect the count? If a cell has the words "Dispatched" and "Tier" in it, is it added to the sum or can the sum (332 in your example) only consist of cells that contain "Dispatched" and not "Tier"? You also gave an example sum of ~34. How was this number arrived at? Is it the count of all cells with the word "Tier" in it? Is it only cells that contain "Tier" and NOT "Dispatched"? Does the number following the word "Tier" affect the count? Finally, how are you wanting the percentage to be calculated? Is it by specific Tier #? By machine name variation? What is the divisor, is it always the total of all cells containing "Dispatched"? Or can it change dynamically, and if so, how is it arrived at?

If you can give some very specific examples, that would help a lot, preferably 2 or 3 different ones. As far as the Overflow error, I think that's just because the variables were initially declared as Integers, I'll declare them as Long to avoid that.

 

[Huizar]

Hey Veritan, thanks for responding.

So when I say i have to run this for 6 machines, i'm actually talking about 6 different model types. For instance a variation of "DEER XXX" would fall into one model type. The second machine would be Turtle 1 and a variation of that ("TURTLE XXX) and so on. I arrived at 332 because that was the count in the column with "*Dispatched*" for variation of "DEER". Then within the DEER variation i got the count of 32 for Tier in the column. So essentially I'm getting the total count of "*Dispatched*" and "*Tier*" within the DEER variation. So They main driver i guess you could say is the Model (DEER Variation). The Final output would be % Tier USE which is derived Total Dispatched count divded by Total Tier Support Count within DEER variation. I'm pulling the data once a week so the Dispatch count and tier count could change based on the new added rows. It is basically a data pull i do from OBI.

Example: DEER variation is just one model regardless of variation. Turtle variation is just one model regardless of variation.

[TABLE="width: 522"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]DEER[/TD]
[TD]Dispatched Service[/TD]
[/TR]
[TR]
[TD]DEER LAB[/TD]
[TD]Dispatched Service[/TD]
[/TR]
[TR]
[TD]DEER Pro[/TD]
[TD]Dispatched -Tier 1[/TD]
[/TR]
[TR]
[TD]DEER 345[/TD]
[TD]Dispatched- Tier 2[/TD]
[/TR]
[TR]
[TD]DEER 789[/TD]
[TD]Dispatched Service - CRT[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 473"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Turtle[/TD]
[TD]Dispatched -Tier 1[/TD]
[/TR]
[TR]
[TD]Turtle[/TD]
[TD]Dispatched -Tier 2[/TD]
[/TR]
[TR]
[TD]Turtle PRO[/TD]
[TD]Dispatched Service[/TD]
[/TR]
[TR]
[TD]Turtle ISE[/TD]
[TD]Dispatched Service - CRTS - CU doesn't[/TD]
[/TR]
[TR]
[TD]Turtle C[/TD]
[TD]Dispatched -Tier 10[/TD]
[/TR]
</tbody>[/TABLE]


The count for DEER with Dispatch is 5. The Tier support count is 2. % Tier USE would be 40.0%
The count for Turtle with Dispatch is 5. The Tier support count is 3. % Tier Use would be 60.0%

Hopes this is clear f you.

Thanks

 

[Veritan]

Thank you for clearing a lot of that up. I believe this code is what you are looking for. A couple things to note. First, I am assuming that your data is contiguous starting in Cell A1 with no blanks between any of the rows. Second, I am assuming that all occurrences of the machine name ("DEER") will occur in column A and that all occurrences of "Dispatched" and "Tier" will occur in column B. Finally, this code will put the data labels and percentages in the first row just under the last row of your data. As these are all location related assumptions, they are fairly easy to change around, so you should be able to edit it without too much trouble if something is in a different location than I am assuming. See if this code will work for you. (I also declared all numeric values as long to try to get around any Overflow errors.)

Sub Percentage()
    Dim r As Range, i&, Dispatched&, Tier&, arrMach() As Variant
    
[COLOR=#008000]    'Make sure to enter the names of the machines in lowercase here in order to be able to match all case types in the data.[/COLOR]
    arrMach = Array("deer", "turtle", "squid", "kangaroo", "octopus", "elephant")
    For i = 0 To UBound(arrMach)
[COLOR=#008000]        'Change the cell references below to be the column and row where your data starts.[/COLOR]
        For Each r In Range("A1:A" & Range("A1").End(xlDown).Row)
            If InStr(LCase(r), arrMach(i)) <> 0 And InStr(LCase(r.Offset(, 1)), "dispatched") <> 0 Then Dispatched = Dispatched + 1
            If InStr(LCase(r), arrMach(i)) <> 0 And InStr(LCase(r.Offset(, 1)), "tier") <> 0 Then Tier = Tier + 1
        Next r
        With Cells(Rows.Count, 1).End(xlUp).Offset(1)
            .Value = "Tier Use %: " & UCase(arrMach(i))
            If Dispatched = 0 Then
                .Offset(, 1) = 0
            Else: .Offset(, 1) = Tier / Dispatched
            End If
            .Offset(, 1).NumberFormat = "0.0%"
        End With
        Tier = 0
        Dispatched = 0
    Next i
End Sub

Good luck with this, hope it works!

 

[Huizar]

ah, what happens if there is blanks? Does it affect the output?

 

[SpillerBD]

If you had the data to specify each Model Group and the Detail of the items you are trying to get your percentage counts on, you could use a Pivot Table's calculation features to very easily get you desired results. Or, you could use various SUMPRODUCT calculations too.
But it all goes back to having a good data table...

 

[Veritan]

Blanks will affect it, but they're easy to get around. Change the line that currently says:

For Each r In Range("A1:A" & Range("A1").End(xlDown).Row)

to

For Each r In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

That should eliminate the need to worry about blanks.