Need to match site code and pick the furthest date out

R

rentonhighlands

Board Regular
Joined
Jul 31, 2014
Messages
132
Office Version
  1. 365
Platform
  1. Windows
Need to match site code and pick the furthest date out

code date
site code 1 1/27/2025
site code 1 2/15/2025
site code 2 3/1/2025
site code 2 3/25/2025
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
so a MAXIFS()
would get the latest date by code
or maybe a filter()

Book2
ABCDE
1code datedatescodeDate
2site code 11/27/25site code 12/15/25
3site code 12/15/25site code 23/25/25
4site code 23/1/25
5site code 23/25/25
Sheet1
Cell Formulas
RangeFormula
E2:E3E2=MAXIFS($B$2:$B$5,$A$2:$A$5,D2)



A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
 
Last edited:
Upvote 0
I need to match on code because I have the same code listed many times and excel to return the latest date.



codedate
site code 11/27/2025
site code 12/15/2025
site code 13/1/2025
site code 14/1/2025here I want to poplulate date 4/1/2025. I want the formula look down column I and give me the latest date
site code 23/1/2025
site code 23/25/2025
Site code 34/1/2025
Site code 34/25/2025
site code 45/1/2025
site code 45/25/2025
site code 53/1/2025
site code 53/15/2025
site code 65/25/2025
site code 66/1/2025
site code 78/1/2025
site code 78/15/2025
site code 89/1/2025
site code 89/15/2025
site code 910/1/2025
site code 910/14/2025
site code 108/1/2025
site code 108/10/2025
 
Upvote 0
as i'm in the UK , i converted all the dates to MDY - BUT they may not have correctly changed the day and month
But should give you what you need

=IF(COUNTIF($A$3:A3,A3)=COUNTIF($A$3:$A$1000,A3),MAXIFS($B$3:$B$1000,$A$3:$A$1000,A3),"")

you may need to change the range - from row 1000 to match your range

this will put the max date in the last cell which may not be what you want - see example below where i changed the dates


Book3
ABCD
1
2codedate
3site code 11/27/25 
4site code 12/15/25 
5site code 13/1/25 
6site code 14/1/254/1/25here I want to poplulate date 4/1/2025. I want the formula look down column I and give me the latest date
7site code 23/1/25 
8site code 23/25/253/25/25
9Site code 34/1/25 
10Site code 34/25/254/25/25
11site code 45/1/25 
12site code 45/25/255/25/25
13site code 53/1/25 
14site code 53/15/253/15/25
15site code 65/25/25 
16site code 66/1/256/1/25
17site code 78/1/25 
18site code 78/15/258/15/25
19site code 89/1/25 
20site code 89/15/259/15/25
21site code 910/1/25 
22site code 910/14/2510/14/25
23site code 108/1/25 
24site code 108/10/258/10/25
25
Sheet1
Cell Formulas
RangeFormula
C3:C24C3=IF(COUNTIF($A$3:A3,A3)=COUNTIF($A$3:$A$1000,A3),MAXIFS($B$3:$B$1000,$A$3:$A$1000,A3),"")


for example

Book3
ABC
1
2codedate
3site code 11/27/25 
4site code 111/15/25 
5site code 13/1/25 
6site code 14/1/2511/15/25
7site code 23/1/25 
8site code 23/25/253/25/25
9Site code 311/1/25 
10Site code 34/25/2511/1/25
11site code 45/1/25 
12site code 45/25/255/25/25
13site code 53/1/25 
14site code 53/15/253/15/25
Sheet1
Cell Formulas
RangeFormula
C3:C14C3=IF(COUNTIF($A$3:A3,A3)=COUNTIF($A$3:$A$1000,A3),MAXIFS($B$3:$B$1000,$A$3:$A$1000,A3),"")
 
Upvote 0
this works to find the date. After excel finds the latest date I need excel to add 60 business days to the latest date and return the result
 
Upvote 0
ok - just use +60 for days OR do you want business days - Mon-fri

workdays
=IF(COUNTIF($A$3:A3,A3)=COUNTIF($A$3:$A$1000,A3),WORKDAY(MAXIFS($B$3:$B$1000,$A$3:$A$1000,A3),60),"")
 
Upvote 0
thats the formula i provided, in last post


Book2
ABCD
1
2codedate60 business days
3site code 11/27/25 
4site code 111/15/25  
5site code 13/1/25  
6site code 14/1/2511/15/252/6/26
7site code 23/1/25  
8site code 23/25/253/25/256/17/25
9Site code 311/1/25  
10Site code 34/25/2511/1/251/23/26
11site code 45/1/25  
12site code 45/25/255/25/258/15/25
13site code 53/1/25  
14site code 53/15/253/15/256/6/25
Sheet1
Cell Formulas
RangeFormula
C3:C14C3=IF(COUNTIF($A$3:A3,A3)=COUNTIF($A$3:$A$1000,A3),MAXIFS($B$3:$B$1000,$A$3:$A$1000,A3),"")
D4:D14D4=IF(COUNTIF($A$3:A4,A4)=COUNTIF($A$3:$A$1000,A4),WORKDAY(MAXIFS($B$3:$B$1000,$A$3:$A$1000,A4),60),"")
 
Upvote 0
ok this works except I am getting a result of 3/23/1900 so some of my entries where I only have 1 code and not multiple codes.
 
Upvote 0
works for me

need to see an example

Book2
ABCD
1
2codedate60 business days
3site code 11/27/25 
4site code 111/15/25  
5site code 13/1/25  
6site code 14/1/2511/15/252/6/26
7site code 23/1/253/1/255/23/25
8site code 123/25/253/25/256/17/25
9Site code 311/1/25  
10Site code 34/25/2511/1/251/23/26
11site code 45/1/25  
12site code 45/25/255/25/258/15/25
13site code 53/1/253/1/255/23/25
14site code 63/15/253/15/256/6/25
15site code 73/16/253/16/256/6/25
16site code 83/17/253/17/256/9/25
17site code 93/18/253/18/256/10/25
18site code 103/19/253/19/256/11/25
Sheet1
Cell Formulas
RangeFormula
C3:C18C3=IF(COUNTIF($A$3:A3,A3)=COUNTIF($A$3:$A$1000,A3),MAXIFS($B$3:$B$1000,$A$3:$A$1000,A3),"")
D4:D18D4=IF(COUNTIF($A$3:A4,A4)=COUNTIF($A$3:$A$1000,A4),WORKDAY(MAXIFS($B$3:$B$1000,$A$3:$A$1000,A4),60),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
How to count using multi criteria
Replies
1
Views
117
AhoyNC
A
D
  • Question Question
Create Ranges of Dates and Return a Value in VBA Code
Replies
1
Views
48
iggydarsa
iggydarsa
S
  • Question Question
Calculate Weekly movements
2
Replies
13
Views
118
Shp1
S
R
  • Question Question
Help Formula Countif or Countifs
Replies
6
Views
128
Cubist
Cubist
A
  • Question Question
Formula to create a dynamic graph accumulating the amounts
Replies
1
Views
97
coulombevin
coulombevin

Forum statistics

Threads
1,226,013
Messages
6,188,421
Members
453,473
Latest member
bbugs73

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