Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Macro or Formula Question
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 "SailDirectedRoutedOrderRejectionAndQuoteResub mit 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Macro or Formula Question
hi Carl,
in this example i named the sheets "sh1" and "sh2" Sub Macro1() mt1 = """SailDirectedRoutedOrderRejectionAndQuoteResubmi t""" mt2 = """SailDirectedOrderAcceptation""" mt3 = """SailDirectedOrderNotice""" mt4 = """SailErrorNotice""" For i = 2 To 4 r1 = Evaluate("=SUMPRODUCT(--(sh1!C2:C8=sh2!A" & i & ")*(sh1!D2:D8=" & mt1 & "))") r2 = Evaluate("=SUMPRODUCT(--(sh1!C2:C8=sh2!A" & i & ")*(sh1!D2:D8=" & mt2 & "))") r3 = Evaluate("=SUMPRODUCT(--(sh1!C2:C8=sh2!A" & i & ")*(sh1!D2:D8=" & 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!D2:D8=" & mt4 & "))") Next End Sub -- isabelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
Macro Question formula | Excel Discussion (Misc queries) | |||
Formula Macro Question | Excel Discussion (Misc queries) | |||
Excel Macro Question? | Excel Discussion (Misc queries) | |||
Excel Formula/Worksheet maybe Macro Question | Excel Worksheet Functions |