Managing Filtered Items

[Dastard]

Hi
how can i apply any formula on items which are resulted because of a filter ( since filtered items are in random order )
also take a look at attachment
i need a lil vb program which could scan worksheet "Results" and column "G" ( with header "Node") and look for same text like .. for example start with "MDLH1194" it should apply the formula ( i have applied that forumula ) in front of this text ( see H2 , H15 and H65 ) to calculate duration wherever it finds "MDLH1194" text in G column .. after that it should sum up those durations as i have did ( see I2 ) and also print output like i have printed in worksheet "Analysis" (calculate minutes aswell in E2) .. ignore H233 in "Results"
and then jump over to next Node and repeat till it is done with all distinct nodes
i hope it will be a piece of cake for u gurus
waiting
Regards

[DKenny]

Why don't you just fill the formula in all the Duration formulas in Col H and then create a pivot table, grouping by Node?

[DKenny]

Or, if you don't like pivot tables, you could use a SumIF formula.

Here's your sample workbook with examples of both.

[Dastard]

its nice effort and i love to rate ur post for that .. but u know all i want is to get rid of this "click" and "select" thingy .. when u have almost 60,000 lines for each block and u have to repeat same procedure for almost 30 blocks .. that takes a lot of energy ... a lil vb program generating total durations for each node is everything i need
besides if u could guide me on this "Pivot table and chart" option .. that how can i generate the output as u have generated from raw data ( step by step) .. it is nice since it also creates a chart for me ( i can impress my boss with that )

[DKenny]

Try this.
Note: this assumes that you have included the calculation of Duration in column H before you generate the report.

Option Explicit
 
Private Type ReportLine
    sBSC As String
    sEvent As String
    sNode As String
    dtTotTime As Date
End Type
 
Sub DK_report()
Dim lLastRow As Long
Dim rngData As Range
Dim vaLineData() As ReportLine
Dim lRowNum As Long
Dim bProcessed As Boolean
Dim lLineId As Long
Dim lRecordCount As Long
Dim wksReport As Worksheet
Dim rngOutput As Range
    
    'Set a reference to the source data
    With ThisWorkbook.Worksheets("Results")
        lLastRow = .Range("A65536").End(xlUp).Row
        Set rngData = .Range(.Cells(2, 1), .Cells(lLastRow, 8))
    End With
    
    ReDim vaLineData(0)
    
    With rngData
        For lRowNum = 1 To .Rows.Count
            
            'Assume we have not yet processed this node
            bProcessed = False
            
            'Loop through the processed nodes..
            For lLineId = LBound(vaLineData) To UBound(vaLineData)
                
                '..Looking for a match to the current row
                If vaLineData(lLineId).sNode = .Cells(lRowNum, 7) Then
                    
                    '..If a match is found
                    'record that and stop looping
                    bProcessed = True
                    Exit For
                    
                End If
                
            Next lLineId
            
            'For New nodes, record the required values
            If Not bProcessed Then
                vaLineData(UBound(vaLineData)).sBSC = .Cells(lRowNum, 1)
                vaLineData(UBound(vaLineData)).sEvent = .Cells(lRowNum, 2)
                vaLineData(UBound(vaLineData)).sNode = .Cells(lRowNum, 7)
                vaLineData(UBound(vaLineData)).dtTotTime = Application.WorksheetFunction.SumIf(.Columns(7), .Cells(lRowNum, 7), .Columns(8))
                
                'And increase the size of the array
                ReDim Preserve vaLineData(UBound(vaLineData) + 1)
            End If
        Next lRowNum
    End With
    
    'Remove the unused record from the array
    ReDim Preserve vaLineData(UBound(vaLineData) - 1)
    
    'How many line in the report?
    lRecordCount = UBound(vaLineData) - LBound(vaLineData) + 1
    
    'Add a new sheet
    Set wksReport = ThisWorkbook.Worksheets.Add
    
    Application.ScreenUpdating = False
    
    With wksReport
        .Move after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        .Name = "Report- " & Format(Now(), "yy-mm-dd hh,mm,ss")
        .Range("A1").Value = "BCS"
        .Range("B1").Value = "Event Type"
        .Range("C1").Value = "Node"
        .Range("D1").Value = "Total"
        .Range("E1").Value = "Total (Minutes)"
        .Range("D:D").NumberFormat = "hh:mm:ss"
        
        'Loop through the array, writing the value to the report
        For lRowNum = 2 To lRecordCount + 1
            .Cells(lRowNum, 1) = vaLineData(lRowNum - 2).sBSC
            .Cells(lRowNum, 2) = vaLineData(lRowNum - 2).sEvent
            .Cells(lRowNum, 3) = vaLineData(lRowNum - 2).sNode
            .Cells(lRowNum, 4) = vaLineData(lRowNum - 2).dtTotTime
            .Cells(lRowNum, 5) = CInt(vaLineData(lRowNum - 2).dtTotTime * 60 * 24)
        Next lRowNum
        
        .Columns("A:E").AutoFit
    End With
    
    Application.ScreenUpdating = True
    Set wksReport = Nothing
End Sub