Count open for particular Shipment

[Dilip89]

I am working on a file which has lot of data, I want to know how many days it took for a particular shipment to close ? File looks somewhat like this.

Data looks somewhat like below :

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: #DDEBF7"]Date[/TD]
[TD="width: 64, bgcolor: #DDEBF7"]Shipment[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/2/2019[/TD]
[TD="bgcolor: transparent"]82794104[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/2/2019[/TD]
[TD="bgcolor: transparent"]50080262[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/3/2019[/TD]
[TD="bgcolor: transparent"]82794104[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/3/2019[/TD]
[TD="bgcolor: transparent"]50414072[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/3/2019[/TD]
[TD="bgcolor: transparent"]50417404[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/3/2019[/TD]
[TD="bgcolor: transparent"]50417559[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/4/2019[/TD]
[TD="bgcolor: transparent"]82794104[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/4/2019[/TD]
[TD="bgcolor: transparent"]50414072[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/5/2019[/TD]
[TD="bgcolor: transparent"]82794104[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/5/2019[/TD]
[TD="bgcolor: transparent"]50414072[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/6/2019[/TD]
[TD="bgcolor: transparent"]82794104[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1/6/2019[/TD]
[TD="bgcolor: transparent"]50414072[/TD]
[/TR]
</tbody>[/TABLE]

 

[EFANYoutube]

Your data is incomplete
you need a column to tell you what the instances of the dates are for
for example you have 5

82794104
I don't know when this is closed or opened because there are 5 different occasion
If you can get your data into a state where you have 2 instances with dates(1st one being the open date, 2nd one being the closed date) then the bottom code will work

Sub get_list_of_closed_Shipments()
    Dim data As Worksheet
    Dim listRng, searchRng, outputStartCell, cell As Range
    Dim numberOfDays As Integer
    Set data = ThisWorkbook.Sheets("Data") 'What ever the name of the sheet is that you keep your data
    Set outputStartCell = ThisWorkbook.Sheets("Output").Cells(1, 1) 'where you want the output to start
    Set listRng = data.Range(data.Range("B2"), data.Cells(Rows.Count, 2).End(xlUp))
    
    For Each cell In listRng
        On Error Resume Next
        Set searchRng = Range(cell.Offset(1), data.Cells(Rows.Count, 2).End(xlUp))
        Debug.Print searchRng.Cells.Count
        numberOfDays = CDate(Format(searchRng.Find(cell.Value).Offset(0, -1).Value, "mm/dd/yyyy")) - CDate(Format(cell.Offset(0, -1).Value, "mm/dd/yyyy"))
        If Err = 0 Then
            'write data
            outputStartCell.Value = cell
            outputStartCell.Offset(0, 1).Value = numberOfDays
            Set outputStartCell = outputStartCell.Offset(1)
        End If
        On Error GoTo 0
    Next cell
End Sub

​