Select most recent occurrences using cells.Find

Jimmy509

New Member
Joined
Apr 18, 2019
Messages
19
Hi, I have this data:
UserID Date
1 03.22.2019
1 03.22.2018
2 03.22.2019
3 05.22.2018
3 05.22.2019

I would like to go through it using and select the user with the most recent occurrence.
I am using 2 worksheets. sheets(1) has the name of the user and I try to find the name in sheet(2) using "For each cell in sheet(1) in a range in sheet(2)". My problem is when the match happens. I want it to look at column B where the date is and pick the userID with the latest date.
Assume user ID is in sheet(1) column A. User ID is in sheet(2) Column A and Date is in Sheet(2) column B.
Please help as I am not sure how to do this in the most efficient way.
Here's my starter code:

For Each C in Rng
with Sheet(2).Cells
Set B = .FInd(C, LookIn:= xlValues, Lookat:= xlWhole)
If Not B Is Nothing Then
'Find the User ID. If there are multiple occurrences for userID. Find the one with the most recent date'
End IF
End With
Next
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Jimmy509

New Member
Joined
Apr 18, 2019
Messages
19
Can anyone help please?
The format for sheet1 is:
UserID
1
1
2
3
3
The one for sheet2 is( as a correction from the post).
UserID Date
1 03.22.2019
1 03.22.2018
2 03.22.2019
3 05.22.2018
3 05.22.2019
 

Jimmy509

New Member
Joined
Apr 18, 2019
Messages
19
Sheet2:
Column A
UserID
1
1
2
3
3

Column B
Date
03.22.2019
03.22.2018
03.22.2019
05.22.2018
05.22.2019
 

Watch MrExcel Video

Forum statistics

Threads
1,089,861
Messages
5,410,843
Members
403,331
Latest member
dignityy

This Week's Hot Topics

Top