How do I get the Max date

wheddingsjr · #1 03-14-2022, 12:59 PM

Hi all

I have data that has items with the same codes but different dates. I have sorted the data by item number and date descending). I want to mark the first item with the most recent date as 1 and all of the other like items be marked as 0 (see attached example). I tried a few different formulas but none of them worked. Any suggestions would be appreciated.

Thanks

Alansidman · #2 03-14-2022, 07:53 PM

Attached is a solution using Power Query. You can review the steps taken in the file.

ArviLaanemets · #3 03-14-2022, 11:23 PM

Into field C2 enter the formula
=((COUNTIFS($A$2:$A$58,$A2,$B$2:$B$58,">" & $B2)+1)=1)*(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1)[/CODE]
, and copy down.

And sorting is not essential - the formula returns 1 for 1st record with latest date it finds for given item.

wheddingsjr · #4 03-15-2022, 04:57 AM

Thanks ArviLaanemets, that worked perfecly!!!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Use Original Award Date or Mod Award Date For Date-Based Recurring Tasks	KBCasey27	Project	1	04-30-2021 12:22 AM
Change font color if date in column F is prior to today's date.	kevinbradley57	Excel Programming	1	12-05-2018 07:35 PM
Microsoft Project 2016 print specific date range stuck on one date	martinsalmon	Project	1	10-12-2016 11:36 AM
How can a document have the same date modified/create date, yes have have actual file content?	legaleagle	Word	15	01-07-2015 06:58 PM
Excel pivot table with a DATE value field for some reason stops at a certain date	angie450	Excel Programming	2	08-19-2014 08:50 AM