Pairing Start Date and End Date from List of Dates

[somesoul]

I have list of dates as below:

I need to pair the EntryDate (Earliest START), DateFrom (Latest START) and DateTo (LatestEnd for the month and year). From above, my expected result as below:

1. EntryDate = first assigned date (earliest START).
2. DateFrom = Latest START. If only one START EntryDate = DateTo
3. DateTo = Latest END (only include on specific month and year, for example only include February 2021)

As am not really familiar with excel, I have tried so many ways to pair but not getting the result as expected.
currently I need to pair them manually which cost me hours as there are thousands of row. Your help will be very appreciated.

 

[Zot]

Not really sure I understood 100% the criteria since this is few lines of sample.

Sub PairStartEnd()

Dim m As Long, n As Long, eRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Application.ScreenUpdating = False

'Define sheet name here. Assumption the sheets are named as default Sheet1 and Sheet2
Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

'Prepare Result sheet (Sheet2)
ws2.Range("A1") = "Ticket Number"
ws2.Range("B1") = "Entry Date"
ws2.Range("C1") = "Date From"
ws2.Range("D1") = "Date To"

'Get the end of data row in Sheet1
eRow = ws1.Range("A1").End(xlDown).Row

'Search for Start and End from row 2 to eRow
m = 1
For n = 2 To eRow
    If ws1.Range("D" & n) = "START" And ws1.Range("D" & n + 1) = "END" Then
        m = m + 1
        With ws2
            .Range("A" & m) = ws1.Range("A" & n)
            .Range("B" & m) = ws1.Range("B" & n)
            .Range("C" & m) = ws1.Range("B" & n)
            .Range("D" & m) = ws1.Range("B" & n + 1)
            n = n + 1
        End With
    End If
Next

End Sub

I'm not sure how familiar you are with VBA (or macro). So, here is the guide if you need on how to run macro

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

 

[somesoul]

Not really sure I understood 100% the criteria since this is few lines of sample.

Sub PairStartEnd()

Dim m As Long, n As Long, eRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Application.ScreenUpdating = False

'Define sheet name here. Assumption the sheets are named as default Sheet1 and Sheet2
Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

'Prepare Result sheet (Sheet2)
ws2.Range("A1") = "Ticket Number"
ws2.Range("B1") = "Entry Date"
ws2.Range("C1") = "Date From"
ws2.Range("D1") = "Date To"

'Get the end of data row in Sheet1
eRow = ws1.Range("A1").End(xlDown).Row

'Search for Start and End from row 2 to eRow
m = 1
For n = 2 To eRow
    If ws1.Range("D" & n) = "START" And ws1.Range("D" & n + 1) = "END" Then
        m = m + 1
        With ws2
            .Range("A" & m) = ws1.Range("A" & n)
            .Range("B" & m) = ws1.Range("B" & n)
            .Range("C" & m) = ws1.Range("B" & n)
            .Range("D" & m) = ws1.Range("B" & n + 1)
            n = n + 1
        End With
    End If
Next

End Sub

I'm not sure how familiar you are with VBA (or macro). So, here is the guide if you need on how to run macro

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

Hi Zot,

This is great and am learning something new today. Thank You. I never do VBA till today (at least run the script ...
The script working fine except if there's more than one START, it does not take the earliest START for entry date.

For example:

Data

Expected Result:

Anyway, Thanks again

 

[Zot]

I think I did not differentiate the Entry Date and Date From. My fault. It is more to my simple logic ?

Now I'm just not sure if your cell is date formatted because on my Excel the date format is dd/mm/yyy. SO, the day and month are switch on my result but the logic looks ok. Try this

Sub PairStartEnd()

Dim m As Long, n As Long, eRow As Long
Dim EntryDate, StartDate, EndDate
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Application.ScreenUpdating = False

'Define sheet name here
Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

'Prepare Result sheet (Sheet2)
ws2.Range("A1") = "Ticket Number"
ws2.Range("B1") = "Entry Date"
ws2.Range("C1") = "Date From"
ws2.Range("D1") = "Date To"

'Get the end of data row in Sheet1
eRow = ws1.Range("A1").End(xlDown).Row

'Search for Start and End from row 2 to eRow
m = 1
For n = 2 To eRow
    Select Case ws1.Range("D" & n)
        Case "START"
            If EntryDate = "" Then
                EntryDate = ws1.Range("B" & n)
            Else
                StartDate = ws1.Range("B" & n)
            End If
        Case "Previous Months ""END"""
            EntryDate = ""
            StartDate = ""
        Case "END"
            If Not EntryDate = "" Then
                EndDate = ws1.Range("B" & n)
                m = m + 1
                With ws2
                    .Range("A" & m) = ws1.Range("A" & n)
                    .Range("B" & m) = EntryDate
                    If Not StartDate = "" Then
                        .Range("C" & m) = StartDate
                    Else
                        .Range("C" & m) = EntryDate
                    End If
                    .Range("D" & m) = EndDate
                End With
                EntryDate = ""
                EndDate = ""
            End If
    End Select
Next

End Sub