Formula to tell which data is the latest in a list

[gheyman]

Down column C I have a list of names. Down Column B I have the date the names were entered. The list will have duplicate names.

I need a formula that shows which duplicate is the latest entry. I want to use this formula in a nested formula. Basically an IF formula that says if this is the "latest Entry" then....

(No I don't want to sort. lol)




Here is the big picture of what I am trying to do:
The list of Names in column C is actually Tab Names that are added to the sheet when a Tab is imported into this workbook. When they are added, I have a Time Stamp placed next the tab name in column B. From time to Time, the user can delete a tab from the workbook and replace it with an updated version (renaming the updated version exactly the same as the one they deleted) or they can just delete a tab and not replace it. (Note: the Tab Name and Time stamp stay listed in my spreadsheet)

So down my list I want to show which tabs stills exist in the workbook, which were replaced and which were deleted.
I can easily identify which were deleted using an Indirect formula using the tab name in column C - if the formula errors I know the tab name doesn't exist in the workbook anymore.
I can tell if a Tab is still there using an Indirect as well - if it doesn't error then the Tab is in the workbook

But my hurdle is ones that been replaced. If they were replaced that means the tab name is listed one or more times in the list. I am thinking if I can identify which duplicate tab name has the latest date, then I can nest that formula into my Indirect formula. (If I am a duplicate and I have the latest Time Stamp, then do the indirect formula - if not "Replaced") which is why I need the formula to identify which of the Dups has the latest Time Stamp.

 

[Peter_SSs]

Down column C I have a list of names. Down Column B I have the date the names were entered. The list will have duplicate names.

I need a formula that shows which duplicate is the latest entry. I want to use this formula in a nested formula. Basically an IF formula that says if this is the "latest Entry" then....

If you have Office 365 then try something like
=IF(B2=MAXIFS(B$2:B$28,C$2:C$28,C2),"Latest","Not")

Otherwise, try
=IF(B2=AGGREGATE(14,6,B$2:B$28/(C$2:C$28=C2),1),"Latest","Not")

 

[wideboydixon]

One possible solution:

Book1
	B	C	D
1	01/01/2018	Sheet1	Replaced
2	01/05/2018	Sheet2	Current
3	01/03/2018	Sheet3	Deleted
4	01/04/2018	Sheet1	Current
5	01/02/2018	Sheet2	Replaced
6	01/06/2018	Sheet3	Deleted
Sheet1

Cell Formulas
Range	Formula
D1	=IF(IFERROR(INDIRECT($C1&"!A1"),"-")="-","Deleted",IF(COUNTIFS($C:$C,$C1,$B:$B,">="&$B1)=1,"Current","Replaced"))

WBD

 

[gheyman]

THANKS FOR THE HELP. VERY MUCH APPRECIATED

=IF(AND(COUNTIF($C:$C,$C2)>1,MAX(IF($C:$C=$C2,$B:$B))>$B2),"Tab was Replaced",IF((ISERROR(INDIRECT($C2&"!$L$3")))=FALSE,"Tab is Present","Tab was Deleted"))