Finding Start date and End date for value using vba

[AV114]

I have an Excel sheet looks like below. I want the start date and end date for a task

Data

Summarized to

Can you please help me

[WideBoyDixon]

Are you sure you want to use VBA? You can achieve these values with formulas:

B18 and C18 (Enter with Ctrl+Shift+Enter):

=MIN(IF($A$2:$A$13=$A18,$B$2:$B$13))
=MAX(IF($A$2:$A$13=$A18,$B$2:$B$13))

D18:

=SUMIF($A$2:$A$13,$A18,$C$2:$C$13)

WBD

[ikboy]

Try this:

Sub zz()
Dim d As Object, k, t, f$
Set d = CreateObject("scripting.dictionary")
f = [b2].NumberFormat
ar = [a1].CurrentRegion.Value
For i = 2 To UBound(ar)
    k = ar(i, 1)
    If Not d.exists(k) Then
        d(k) = Array(ar(i, 2), ar(i, 2), ar(i, 3))
    Else
        t = d(k)
        t(2) = t(2) + ar(i, 3)
        t(1) = IIf(t(1) > ar(i, 2), t(1), ar(i, 2))
        d(k) = t
    End If
Next
[a18].Resize(d.Count, 4).Borders.Value = 1
[a18].Resize(d.Count, 1) = Application.Transpose(d.keys)
[b18].Resize(d.Count, 2).NumberFormat = f
[b18].Resize(d.Count, 3) = Application.Transpose(Application.Transpose(d.items))
End Sub

[YasserKhalil]

@ikboy
Great code. To adjust the output for dates it is better to use CLng

d(k) = Array(CLng(ar(i, 2)), CLng(ar(i, 2)), ar(i, 3))

t(1) = IIf(t(1) > CLng(ar(i, 2)), t(1), CLng(ar(i, 2)))

[ikboy]

YasserKhalil ,
You right, Clng is better.
Thx
ikboy

[AlphaFrog]

You can do it with a Pivot Table

Sub Summary_PT()
    
    Dim rng As Range
    
    Set rng = Range("A1").CurrentRegion
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                                   SourceData:=rng).CreatePivotTable _
                                   TableDestination:=Range("E2")
    With ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count)
        .ColumnGrand = False
        .PageFieldOrder = xlOverThenDown
        .RowGrand = False
        .AddFields RowFields:=Array("StockCode", "Data")
        With .PivotFields("TrnDate")
            .Orientation = xlDataField
            .Caption = "Start Date"
            .Position = 1
            .Function = xlMin
            .NumberFormat = "m/d/yyyy"
        End With
        With .PivotFields("TrnDate")
            .Orientation = xlDataField
            .Caption = "End Date"
            .Position = 2
            .Function = xlMax
            .NumberFormat = "m/d/yyyy"
        End With
        With .PivotFields("TrnQuantity")
            .Orientation = xlDataField
            .Caption = " Total TrnQuantity"
            .Position = 3
        End With
        With .DataPivotField
            .Orientation = xlColumnField
            .Position = 1
        End With
    End With
    Application.CommandBars("PivotTable").Visible = False
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub