Identifying last record in a data set

[djbe17]

Hi all,
i have "brain fog" and cannot work out the logic to build a formula...i expect im missing the very obvious.
I have a large data set, of record against unique sites (col B) , each site will have have had a number of inspections undertaken during its life (unique survey number col B & submit date Col C)

I need to run some compliance checks to confirm if the LAST submitted survey has been accepted within specific time scale - to do this i first need a flag in Col E to identify the last ( most recent ) survey for each has been submitted. From there i can build the various KPI flags - data shown below is a very simplified version of the intended data.

Many thanks in advance.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[djbe17]

Account updated - excel 365 thanks

 

[Zot]

May I ask why for Site ID 36825, the last survey was Survey Number 130527 and not 130528 with same Submit and Accept Date?

 

[djbe17]

May I ask why for Site ID 36825, the last survey was Survey Number 130527 and not 130528 with same Submit and Accept Date?

Hi ,
occasionally surveys may have been split into 2 reports submitted on the same day. When i copied this to the example i should have formatted the date field to dd/mm/yy HH:MM
The logic still remains to identify the most recent upload. - unfortunately the data source does not include a latest / last survey counter.

Thanks

 

[Fluff]

How about

=IF(C2=MAXIFS(C:C,B:B,B2),"Last Survey","")

 

[djbe17]

Many thanks indeed, a beautiful simple solution indeed. I had already started down the wrong road with using help cols etc.

Vary much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.