Excel Macro or Formula Question

[carl]

My data looks like this (Sheet1 Cell A1)

Date Time Code MessageType
20111018 93032000 GK SailDirectedOrderNotice
20111018 93032000 GK SailDirectedOrderNotice
20111018 93032000 GK SailDirectedRoutedOrderRejectionAndQuoteResubmit
20111018 93032000 GK SailDirectedOrderAcceptation
20111018 93046000 GK SailErrorNotice
20111018 93046000 AB SailDirectedRoutedOrderRejectionAndQuoteResubmit
20111018 93100000 AB SailErrorNotice


I was hoping to find a macro or formula that would create this table
(Sheet2 Cell A1)

Code Ratio ErrorCount
GK 100% 0
AB 0% 1

Where the value in ColB is for each Code in ColA, (Countof
"SailDirectedRoutedOrderRejectionAndQuoteResubmit and
SailDirectedOrderAcceptation" divied by the Countof
"SailDirectedOrderNotice" expressed as a percentage.

And the value in ColC is for each Code in ColA, (Countof
"SailErrorNotice")

Thank you in advance.

 

[isabelle]

hi Carl,

in this example i named the sheets "sh1" and "sh2"

Sub Macro1()

mt1 = """SailDirectedRoutedOrderRejectionAndQuoteResubmit"""
mt2 = """SailDirectedOrderAcceptation"""
mt3 = """SailDirectedOrderNotice"""
mt4 = """SailErrorNotice"""

For i = 2 To 4
r1 = Evaluate("=SUMPRODUCT(--(sh1!C2:C8=sh2!A" & i & ")*(sh1!D28=" & mt1 & "))")
r2 = Evaluate("=SUMPRODUCT(--(sh1!C2:C8=sh2!A" & i & ")*(sh1!D28=" & mt2 & "))")
r3 = Evaluate("=SUMPRODUCT(--(sh1!C2:C8=sh2!A" & i & ")*(sh1!D28=" & mt3 & "))")

If r3 = 0 Then
Sheets("sh2").Range("B" & i) = Format(0, "0%")
Else
Sheets("sh2").Range("B" & i) = Format((r1 + r2) / r3, "0%")
End If
Sheets("sh2").Range("C" & i) = Evaluate("=SUMPRODUCT(--(sh1!C2:C8=sh2!A" & i & ")*(sh1!D28=" & mt4 & "))")
Next
End Sub