How can you find multiple match in a cell and select the one with the latest date attribute to it

[Jimmy509]

Hello my fellow excel-ers,
I am trying to find search through a range of cells in column A from my excel spreadsheet. I know that I can use .Find to find the first match in that range or use .FindNext to find all of them. But my issue is I would like to find the match for my latest transaction. In Column B I have a range of dates for each transaction.
The transaction ID repeat multiple times for different date. I would to select the date that is the most recent.
I am new to excel VBA. Can anyone help me please.

 

[Marcelo Branco]

Are the dates in column B sorted (ascending / descending)?

M.

 

[Jimmy509]

Are the dates in column B sorted (ascending / descending)?

M.

Thanks for your reply. No They are not sorted by date. They are sorted by TransactionID

 

[Marcelo Branco]

Thanks for your reply. No They are not sorted by date. They are sorted by TransactionID

Ok, but for each transaction are the dates sorted?

M.

 

[Jimmy509]

Ok, but for each transaction are the dates sorted?

M.

No unfortunately.

 

[Marcelo Branco]

Maybe something like this (dates as dd/mm/yyyy)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Transaction​

[/TD]
[TD]

Date​

[/TD]
[TD][/TD]
[TD]

Transaction​

[/TD]
[TD]

Last Date​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

03/01/2019​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

03/03/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

03/03/2019​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

10/01/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

04/02/2019​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

10/04/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

10/01/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

2​

[/TD]
[TD]

04/01/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

3​

[/TD]
[TD]

11/01/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

3​

[/TD]
[TD]

03/03/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

3​

[/TD]
[TD]

10/04/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

3​

[/TD]
[TD]

02/04/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in E2 copied down
=MAX(IF(A$2:A$10=D2,B$2:B$10))
confirmed with Ctrl+Shift+Enter, not just Enter

Format column E as Date

Hope this helps

M.

 

[Jimmy509]

Maybe something like this (dates as dd/mm/yyyy)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Transaction​

[/TD]
[TD]

Date​

[/TD]
[TD][/TD]
[TD]

Transaction​

[/TD]
[TD]

Last Date​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

03/01/2019​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

03/03/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

03/03/2019​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

10/01/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

04/02/2019​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

10/04/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

10/01/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

2​

[/TD]
[TD]

04/01/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

3​

[/TD]
[TD]

11/01/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

3​

[/TD]
[TD]

03/03/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

3​

[/TD]
[TD]

10/04/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

3​

[/TD]
[TD]

02/04/2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in E2 copied down
=MAX(IF(A$2:A$10=D2,B$2:B$10))
confirmed with Ctrl+Shift+Enter, not just Enter

Format column E as Date

Hope this helps

M.

Thanks but I was looking for something more like in VBA format

 

[Jimmy509]

Thanks but I was looking for something more like in VBA format

Something using .Find method

 

[Marcelo Branco]

Something using .Find method

Maybe something like this (see data sample in post 6)

Sub FindMaxDate()
    'From Chip Pearson
    'http://www.cpearson.com/excel/findall.aspx
    Dim DataRange As Range, rCell As Range
    Dim FoundCell As Range, LastCell As Range, FirstAddr As String
    Dim dtMax As Long
    
    Set DataRange = Range("A2:A10")
    With DataRange
        Set LastCell = .Cells(.Cells.Count)
    End With
    
    For Each rCell In Range("D2:D4")
        Set FoundCell = DataRange.Find(what:=rCell, after:=LastCell)
        If Not FoundCell Is Nothing Then
            FirstAddr = FoundCell.Address
            dtMax = FoundCell.Offset(, 1)
        End If
        
        Do Until FoundCell Is Nothing
            Set FoundCell = DataRange.FindNext(after:=FoundCell)
            If FoundCell.Address = FirstAddr Then Exit Do
            If FoundCell.Offset(, 1) > dtMax Then dtMax = FoundCell.Offset(, 1)
        Loop
        With rCell.Offset(, 1)
            .Value = dtMax
            .NumberFormat = "dd/mm/yyyy"
        End With
    Next rCell
End Sub

Hope this helps

M.

 

[Marcelo Branco]

Some adjustments:
Change the datatype of dtMax to Variant

Immediately after the For each rCell in Range("D2:D4") insert this line
dtMax = "Not Found"

In the first Find insert this parameter
LookAt:=xlWhole

M.