Forum Discussion
how to select lines in an array in excel based on a date criteria
How to do the following selection of lines in an array in Excel:
I have an array of employees, with salary revision dates and salary amounts. So I may have several lines for one employee, corresponding to his salary history:
Employee Number | Last Name | First Name | salary revision date | Salary |
01 | SMITH | John | 1/1/2021 | 1000 |
01 | SMITH | John | 1/2/2022 | 1100 |
01 | SMITH | John | 1/3/2023 | 1200 |
04 | SMITH | Tim | 1/1/2021 | 1500 |
09 | MARTIN | Alex | 1/1/2023 | 2000 |
12 | DOE | Joe | 1/1/2021 | 3000 |
12 | DOE | Joe | 1/2/2022 | 3200 |
I want from to extract from this array the records corresponding only to current (latest) salary, so I want one single line per name, corresponding to the most recent salary revision date for that line:
1 | SMITH | John | 1/3/2023 | 1200 |
4 | SMITH | Tim | 1/1/2021 | 1500 |
9 | MARTIN | Alex | 1/1/2023 | 2000 |
12 | DOE | Joe | 1/2/2022 | 3200 |
