Get max status based on date

Wishmaster89

Board Regular
Joined
Jan 10, 2022
Messages
77
Office Version
  1. 2021
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
Hi All

I'd like to get the max status from column B based on the latest start date from column C. So the max status in this example should be as 12/01/2021 is the max start date. I thought i had the answer when i used max ifs in column t but then realised its obviously not taking the start date into account.

Book9
ABCDEFGHIJKLMTUVWX
1ULNCompletion StatusStart DateStart YearLearning_Actual_End_Date convertedWithdrawal time frame(days)MonthsStandardhybrid end yearYearRestart indicatormax HEYhybrid year with maxMax statusMIN Start dateStart yearMax status if 17/18Max status if 18/19
21118/07/20192018not in a categoryMaths17/01/202120/21130/11/202121/22618/07/201918/196
31312/01/2021202019/07/2021188not in a categoryMaths30/11/202121/22130/11/202121/22618/07/201918/196
41618/07/2019201814/09/202042413-18Maths17/01/202120/21130/11/202121/22618/07/201918/196
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2Cell ValueduplicatestextNO
A1:A1043475Cell ValueduplicatestextNO
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
How about
Excel Formula:
=INDEX(SORT(B2:C4,2,-1),1,1)

Thanks, that does work but i should of been clearer(sorry!), theres about 1000 rows in my actual data so the formula needs to take into account the different ID's(column A). Also, it needs to check that the standard(H) is the same. This is the formula i have in column T so far.
Your sort function may work but i can't think how to integrate it at the moment.

=MAXIFS(B:B,A:A,A2145,H:H,H2145)
 
Upvote 0
Ok, how about
Excel Formula:
=INDEX(SORT(FILTER($B$2:$C$5000,($A$2:$A$5000=A2)*($H$2:$H$5000=H2)),2,-1),1,1)
 
Upvote 0
Ok, how about
Excel Formula:
=INDEX(SORT(FILTER($B$2:$C$5000,($A$2:$A$5000=A2)*($H$2:$H$5000=H2)),2,-1),1,1)
Works mostly but theres one thing. Sometimes in the data the start dates will be the same and in that case i need to return the max status. 6 in this example.

Book2
ABCDEFGH
1ULNCompletion StatusStart DateStart YearLearning_Actual_End_Date convertedWithdrawal time frame(days)MonthsStandard
21130/01/2018201700History
31630/01/2018201724/04/201944913-18History
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3Cell ValueduplicatestextNO
A2Cell ValueduplicatestextNO
A1:A3Cell ValueduplicatestextNO
 
Upvote 0
Anything else you haven't mentioned?
 
Upvote 0
Anything else you haven't mentioned?
I probably wasn't clear enough. Maybe this sample data is clearer?

so for ULN 1 the max status(b) should be 6 as the start dates and standard are the same.
For ULN 2 it should also be 6 as its similar to the above

ULN 3 has done two different courses ( English/sports) so it will need to return the status with the greater start date

ULN 3 should return 1 for the first two rows(same standard)
Then the bottom 2 of ULN 3 should return 6 as the greater date there is 15/02/202

Hope that makes sense?



Book2
ABCDEFGH
1ULNCompletion StatusStart DateStart YearLearning_Actual_End_Date convertedWithdrawal time frame(days)MonthsStandard
21130/01/2018201700History
31630/01/2018201724/04/201944913-18History
42130/01/2018201700English
52630/01/2018201724/04/201944913-18English
63130/01/2019201700English
73630/01/2020201724/04/201944913-18English
83115/02/2020201724/04/201944913-18Sports
93615/02/2021201724/04/201944913-18Sports
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3Cell ValueduplicatestextNO
A2Cell ValueduplicatestextNO
A1:A3Cell ValueduplicatestextNO
 
Upvote 0
Why should ULN 3 & English return 1? The dates are the same & 6 is greater than 1
 
Upvote 0
Ok, how about
Excel Formula:
=INDEX(SORT(FILTER($B$2:$C$5000,($A$2:$A$5000=A2)*($H$2:$H$5000=H2)),{2,1},-1),1,1)
 
Upvote 0
Solution

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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