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

Jimmy509

New Member
Joined
Apr 18, 2019
Messages
26
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Maybe something like this (dates as dd/mm/yyyy)


A
B
C
D
E
1
Transaction​
Date​
Transaction​
Last Date​
2
1​
03/01/2019​
1​
03/03/2019​
3
1​
03/03/2019​
2​
10/01/2019​
4
1​
04/02/2019​
3​
10/04/2019​
5
2​
10/01/2019​
6
2​
04/01/2019​
7
3​
11/01/2019​
8
3​
03/03/2019​
9
3​
10/04/2019​
10
3​
02/04/2019​

<tbody>
</tbody>


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.
 
Last edited:

Jimmy509

New Member
Joined
Apr 18, 2019
Messages
26
Maybe something like this (dates as dd/mm/yyyy)


A
B
C
D
E
1
Transaction​
Date​
Transaction​
Last Date​
2
1​
03/01/2019​
1​
03/03/2019​
3
1​
03/03/2019​
2​
10/01/2019​
4
1​
04/02/2019​
3​
10/04/2019​
5
2​
10/01/2019​
6
2​
04/01/2019​
7
3​
11/01/2019​
8
3​
03/03/2019​
9
3​
10/04/2019​
10
3​
02/04/2019​

<tbody>
</tbody>


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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Something using .Find method
Maybe something like this (see data sample in post 6)

Code:
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,048
Messages
5,466,253
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top