May 13 2020 01:07 AM - edited May 13 2020 03:29 AM
I'm not sure how to articulate this, or what formula I need to use but i'm thinking lookup?
I need to find the effective date for the employee's current position, so, in this example, for ID 1, I need to find the most recent position code, which is 1211, and the first date that this occurred, which is 1/8/2016. Those in red are the dates I need to find for each ID and place them in another tab.
EDIT: for each ID number, I'm trying to find the most current position code, and the first effective date for this code.
A number of employees can/could have held a position code so it will appear for more than one ID number currently, and can also appear as a previous position code.
eg position code 1022
May 13 2020 01:40 AM - edited May 13 2020 01:41 AM
If your Excel version supports the FILTER function, you may want to have a look at the attached workbook. Note that this function is currently available to Microsoft 365 subscribers in the Monthly channel. It will be available to Microsoft 365 subscribers in the Semi-Annual channel starting in July 2020.
May 13 2020 01:56 AM
Please find the attached workbook with a solution.
I used the following formulas in a table to get the results for all the IDs
=INDEX(C:C,MATCH(G3,B:B,0)+COUNTIF(B:B,G3)-1)
Alan
May 13 2020 02:06 AM
Instead of lookup, you can place your all unique Ids in separate tab.
Then in the original tab, sort the 2 columns of Effective Date (Newest to Oldest) and Position Code (Largest to Smallest). By this way the combination of these 2 for a particular Id will be at top. After this you can do VLOOKUP on ID present in second tab to fetch Effective Date and Position Code.
Thanks.
May 13 2020 03:20 AM
Hi @Riny_van_Eekelen. Thank you for your suggestion, however, it doesn't seem to work with my data.
May 13 2020 03:21 AM
Hi @Alan Murray . Thank you for your help. The query works great, but we can have several staff with the same Position Code so it isn't working for my data.
May 13 2020 03:24 AM
Hi @ShishirKumar . Thank you for your help. However, a number of employees can have the same position code, so this method isn't working for my data.
May 13 2020 03:31 AM
@ShishirKumar @Alan Murray @Riny_van_Eekelen I've added more info to the question to hopefully provide a bit of clarity. Thanks again.
May 13 2020 03:50 AM
@Joannerw in case u have latest version of excel you can use 'MAXIF' and 'MINIF' function for your task.
May 13 2020 04:04 AM
SolutionHere is an updated solution which I believes answers the question.
It is an array formula so you need to press Ctrl + Shift + Enter to run it on your Excel version.
May 13 2020 08:50 PM
Though same position code is there for employees but date is different for a combination of Id and position code. I guess sorting and then VLOOKUP can solve your problem.
May 13 2020 10:45 PM
Thanks for your suggestion. I've had a response which worked.
May 13 2020 11:29 PM
@Joannerw Was about to post another solution when I noticed that you had already accepted another one. I would like to offer now anyway. Feel free to use or discard it.
It does not require the list to be sorted in any way, so that you can just keep on adding ID's, Job codes and Effective dates at the bottom of a (structured) table. Ranges used in the formulae expand automatically with the expanding table.
One extra step that determines the latest effective date for the employee, finds the current job code and then finds the first date for the employee with that code. The attached revised workbook contains two solutions. One using FILTER (which may not yet work for you) and another with more traditional functions.
May 13 2020 04:04 AM
SolutionHere is an updated solution which I believes answers the question.
It is an array formula so you need to press Ctrl + Shift + Enter to run it on your Excel version.