Finding the first appearance of the latest item
Hello All,
I am trying to figure out how to mark the first time the latest Job Code appeared for all of my employees.
The data looks somewhat like this:
Employee ID Job code Effective Date
123456 100003 4/30/2019
123456 100003 6/30/2018
123456 100003 1/2/2018
123456 256328 10/31/2017
123456 256328 9/1/2015
123456 450378 7/8/2015
I am trying to mark the value of 01/02/2018 as this is the first time the latest job code appears.
Can anyone assist?
Thank you!!!!
Based on your data, try
Formula:
Please Login or Register to view this content.
If you're going to be inserting rows, maybe
Formula:Please Login or Register to view this content.
Assumption: Data is in reverse chronological order with newest orders appearing in row 2
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thank you ChemistB!
This works if I only have one employee ID but not if I have many (which is my case). Is there a way to incorporate that?
Thank you so much!
Hello gvillanueva1009 and Welcome to Excel Forum.
"This works if I only have one employee ID but not if I have many (which is my case). Is there a way to incorporate that?"
It might help if you could upload a spreadsheet that demonstrates the issue. To upload a sample workbook/spreadsheet (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you JeteMc.
I am attaching a sample spreadsheet. Thank you all for your help.
This proposal employs two helper columns (F:G) which may be moved and or hidden for aesthetic purposes.
Column F is populated using: =IF(OR(A2=A1,A2=""),"",AGGREGATE(15,6,C$2:C$50/(A$2:A$50=A2)/(B$2:B$50=B2),1))
Column G is populated using: =IF(F2="","",AGGREGATE(15,6,ROW(A$2:A$50)/(A$2:A$50=A2)/(B$2:B$50=B2)/(C$2:C$50=F2),1))
The conditional formatting rule is: =COUNTIFS($G$2:$G$50,ROW())>0
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks