Identify most current date for records sharing common value??

[Chrisfav89]

Hello All,

I am wondering if there is a formula that will provide me which record contains the most current date out of all other records that share a common value.

See the table below. Can a formula tell me which line has the most current date out of all other lines that have the same Position number? Ideally it would give me a yes or no like below. Can conditional formatting be applied using this extent of logic?


Position

Position	Center	Distribution	Effective Date	Forumla?
10005	2500	50%	1/1/17	No
10005	1588	50%	1/1/17	No
10005	3500	100%	3/4/17	No
10005	1588	75%	5/1/17	YES
10005	3500	25%	5/1/17	YES
25500	2500	100%	1/1/17	No
25500	3500	100%	5/5/17	YES

<tbody>
</tbody>

Thanks for your help!

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=IF(D2=MAX(IF($A$2:$A$8=A2,$D$2:$D$8)),"Yes","No")

For CF rule, try the following:

=D2=MAX(IF($A$2:$A$8=A2,$D$2:$D$8))