Latest data based on Received date and Issue date

S

shansakhi

Active Member
Joined
Apr 5, 2008
Messages
303
Office Version
  1. 365
Platform
  1. Windows
Hello All,
Need your epertise to know how I can consider the latest data based on two columns.
We are receiving daily one file with last 5 days sales, we want to consider the latest Tkt issue date based on the file receive date and tkt issue date.
In below example only two columns are reflecting but in actual there are more than 25 columns, but we want to consider all the data based only these two columns.
As shown in below example, we want to consider Ticket Issue dates highlighted in Yellow and its corrosponding data.

Book2
IJKLMN
1Input DataOutput data
2Received DateTKT Issue DateTKT Issue Date
324-Feb-2523-Feb-2523-Feb-25
424-Feb-2522-Feb-2522-Feb-25
524-Feb-2521-Feb-2521-Feb-25
624-Feb-2520-Feb-2520-Feb-25
724-Feb-2519-Feb-2519-Feb-25
823-Feb-2522-Feb-2518-Feb-25
923-Feb-2521-Feb-2517-Feb-25
1023-Feb-2520-Feb-2516-Feb-25
1123-Feb-2519-Feb-2515-Feb-25
1223-Feb-2518-Feb-2514-Feb-25
1322-Feb-2521-Feb-25
1423-Feb-2520-Feb-25
1523-Feb-2519-Feb-25
1623-Feb-2518-Feb-25
1723-Feb-2517-Feb-25
1821-Feb-2520-Feb-25
1923-Feb-2519-Feb-25
2023-Feb-2518-Feb-25
2123-Feb-2517-Feb-25
2223-Feb-2516-Feb-25
2320-Feb-2519-Feb-25
2423-Feb-2518-Feb-25
2523-Feb-2517-Feb-25
2623-Feb-2516-Feb-25
2723-Feb-2515-Feb-25
2819-Feb-2518-Feb-25
2923-Feb-2517-Feb-25
3023-Feb-2516-Feb-25
3123-Feb-2515-Feb-25
3223-Feb-2514-Feb-25
33
Sheet1
 
Sort by date Sort by votes
Not sure exactly what you're trying to do, so the following may not be what you're looking for. The following query goes row by row down column [TKT Issue Date] and if it is earlier than the earliest date above it then that row is kept, if not then that row is filtered out.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Received Date", type date}, {"TKT Issue Date", type date}}),
    AddIndex = Table.AddIndexColumn(ChangeType, "Index", 0, 1, Int64.Type),
    lst = List.Buffer(AddIndex[TKT Issue Date]),
    tbl = Table.AddColumn(AddIndex, "Keep", each try if [TKT Issue Date] < List.Min(List.FirstN(lst, [Index])) then true else false otherwise true),
    FilterRows = Table.SelectRows(tbl, each [Keep]),
    Result = Table.RemoveColumns(FilterRows,{"Index", "Keep"})
in
    Result

Book1
BCDEFGHIJKLM
1tblDataQuery Output
2Received DateTKT Issue DateCol1Col2Col3Received DateTKT Issue DateCol1Col2Col3
32/24/20252/23/2025LVY2/24/20252/23/2025LVY
42/24/20252/22/2025ZIN2/24/20252/22/2025ZIN
52/24/20252/21/2025BLO2/24/20252/21/2025BLO
62/24/20252/20/2025FDG2/24/20252/20/2025FDG
72/24/20252/19/2025PIC2/24/20252/19/2025PIC
82/23/20252/22/2025UME2/23/20252/18/2025OQR
92/23/20252/21/2025QHK2/23/20252/17/2025AAA
102/23/20252/20/2025ABN2/23/20252/16/2025HKU
112/23/20252/19/2025ETD2/23/20252/15/2025QGJ
122/23/20252/18/2025OQR2/23/20252/14/2025KPD
132/22/20252/21/2025XWI
142/23/20252/20/2025EZM
152/23/20252/19/2025LDC
162/23/20252/18/2025BFG
172/23/20252/17/2025AAA
182/21/20252/20/2025WSC
192/23/20252/19/2025LFP
202/23/20252/18/2025OEJ
212/23/20252/17/2025RMJ
222/23/20252/16/2025HKU
232/20/20252/19/2025TBV
242/23/20252/18/2025IJF
252/23/20252/17/2025RNK
262/23/20252/16/2025HSX
272/23/20252/15/2025QGJ
282/19/20252/18/2025WTY
292/23/20252/17/2025YVX
302/23/20252/16/2025SHG
312/23/20252/15/2025UCZ
322/23/20252/14/2025KPD
33
Sheet6
 
Upvote 0
Thank you.
Yes.. This is workring abolutly as desired as the code is considering latest data.
But this code is taking very long time to process the data. I have more than 200 files and hence it is taking very long time as it is checking row by row.
Is there any other workaround which will quikly process the data.
 
Upvote 0
May be you can try a different approach,
Please check this code on a sample workbook. Sheet name is "Sheet1", Ticket received dates is in Column B, Ticket isssued dates is in Column C, Output range F:G
Sample input format below
Book1
BC
1Received DateTKT Issue Date
22/24/202502/23/2025
32/24/202502/22/2025
42/24/202502/21/2025
52/24/202502/20/2025
62/24/202502/19/2025
72/23/202502/22/2025
82/23/202502/21/2025
92/23/202502/20/2025
102/23/202502/19/2025
Sheet1

VBA Code: 
Sub SortTktIssuedDates()
    Dim ws As Worksheet
    Dim lastRow As Long, outputRow As Long
    Dim uniqueTktDates As Object
    Dim receivedDate As Variant, tktIssueDate As Variant
    Dim tktArray() As Variant
    Dim i As Integer, n As Integer
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set uniqueTktDates = CreateObject("Scripting.Dictionary")
    
    lastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
    
    ws.Range("F1:G1000").Clear
    
    ws.Range("F1").Value = "Received Date"
    ws.Range("G1").Value = "Issued Date"
    
    For i = 2 To lastRow
        tktIssueDate = ws.Cells(i, 3).Value
        receivedDate = ws.Cells(i, 2).Value
        
        If IsDate(tktIssueDate) And IsDate(receivedDate) Then
            tktIssueDate = CDate(tktIssueDate)
            receivedDate = CDate(receivedDate)
            
            If Not uniqueTktDates.exists(tktIssueDate) Then
                uniqueTktDates.Add tktIssueDate, receivedDate
            End If
        End If
    Next i
    
    n = uniqueTktDates.Count
    If n > 0 Then
        ReDim tktArray(1 To n)
        
        i = 1
        For Each tktIssueDate In uniqueTktDates.keys
            tktArray(i) = tktIssueDate
            i = i + 1
        Next tktIssueDate
        
        Call SortDescending(tktArray, n)
        
        outputRow = 2
        For i = 1 To n
            ws.Cells(outputRow, 7).Value = tktArray(i)
            ws.Cells(outputRow, 6).Value = uniqueTktDates(tktArray(i))
            outputRow = outputRow + 1
        Next i
    End If
    
    MsgBox "Process Completed"
     
     
End Sub

Sub SortDescending(ByRef arr As Variant, ByVal n As Integer)
    Dim i As Integer, j As Integer
    Dim temp As Variant
    
    For i = 1 To n - 1
        For j = i + 1 To n
            If arr(i) < arr(j) Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub
 
Upvote 0
Is this any faster?

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Received Date", type date}, {"TKT Issue Date", type date}}),
    GroupRows = Table.Group(ChangeType, {"TKT Issue Date"}, {{"All", each _{0}}}),
    Result = Table.FromRecords(GroupRows[All])
in
    Result
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
T
  • Question Question
cycle through multiple sheets
Replies
6
Views
789
ttowncorp
T

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top