Most Recent Date

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have list of employees with their goals, each employee has at least 3 goals and each goal has a different update goal date. My question is how to figure out the most recent update date of each employee? What is the right formula?
Below is a sample of employees list:

EE ID​
Last Modified DateMost Recent Update Date Per Employee
1111​
6/10/2020
1111​
6/10/2020
1111​
6/10/2020
1111​
6/10/2020
1111​
7/13/2020
1111​
6/10/2020
2222​
2/28/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
3333​
2/26/2020
3333​
2/26/2020
3333​
2/26/2020
3333​
2/26/2020
3333​
2/26/2020
3333​
2/26/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
5555​
6/22/2020
5555​
6/22/2020
5555​
6/22/2020
5555​
6/22/2020
5555​
6/22/2020
5555​
6/22/2020


Thank you!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,109
with Power Query
EE IDDate
111113/07/2020
222218/07/2020
333326/02/2020
444425/06/2020
555522/06/2020

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"EE ID", type text}, {"Last Modified Date", type date}}),
    Group = Table.Group(Type, {"EE ID"}, {{"Date", each List.Max([Last Modified Date]), type date}})
in
    Group
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
110
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Using Microsoft 365

Book1
ABCDE
1EE IDLast Modified DateUNIQUE IDMost Recent Update Date Per Employee
211116/10/2011117/13/20
311116/10/2022228/8/20
411116/10/2033332/26/20
511116/10/2044446/25/20
611117/13/2055556/24/20
711116/10/20
822222/28/20
922227/18/20
1022227/18/20
1122228/8/20
1222227/18/20
1322227/18/20
1422227/18/20
1522227/18/20
1633332/26/20
1733332/26/20
1833332/26/20
1933332/26/20
2033332/26/20
2133332/26/20
2244446/25/20
2344446/25/20
2444446/25/20
2544446/25/20
2644446/25/20
2744446/25/20
2844446/25/20
2944446/25/20
3044446/25/20
3155556/22/20
3255556/22/20
3355556/24/20
3455556/22/20
3555556/22/20
3655556/22/20
Sheet1
Cell Formulas
RangeFormula
D2:D6D2=SORT(UNIQUE(A2:A36))
E2:E6E2=IF(D2="","",MAXIFS($B$2:$B$36,$A$2:$A$36,D2#))
Dynamic array formulas.
 

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows
with Power Query
EE IDDate
111113/07/2020
222218/07/2020
333326/02/2020
444425/06/2020
555522/06/2020

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"EE ID", type text}, {"Last Modified Date", type date}}),
    Group = Table.Group(Type, {"EE ID"}, {{"Date", each List.Max([Last Modified Date]), type date}})
in
    Group
Thank you so much, I wish I'm that expert with Power Query to use your logic but I'm not YET ;), I'll keep this in future to use it, thank you once again for your input and help!
 

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Using Microsoft 365

Book1
ABCDE
1EE IDLast Modified DateUNIQUE IDMost Recent Update Date Per Employee
211116/10/2011117/13/20
311116/10/2022228/8/20
411116/10/2033332/26/20
511116/10/2044446/25/20
611117/13/2055556/24/20
711116/10/20
822222/28/20
922227/18/20
1022227/18/20
1122228/8/20
1222227/18/20
1322227/18/20
1422227/18/20
1522227/18/20
1633332/26/20
1733332/26/20
1833332/26/20
1933332/26/20
2033332/26/20
2133332/26/20
2244446/25/20
2344446/25/20
2444446/25/20
2544446/25/20
2644446/25/20
2744446/25/20
2844446/25/20
2944446/25/20
3044446/25/20
3155556/22/20
3255556/22/20
3355556/24/20
3455556/22/20
3555556/22/20
3655556/22/20
Sheet1
Cell Formulas
RangeFormula
D2:D6D2=SORT(UNIQUE(A2:A36))
E2:E6E2=IF(D2="","",MAXIFS($B$2:$B$36,$A$2:$A$36,D2#))
Dynamic array formulas.
Thank you for your help, I'm using Office 365, however, I don't have the SORT & UNIQUE formula yet, is there any other alternative formula I can use?
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
110
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Thank you for your help, I'm using Office 365, however, I don't have the SORT & UNIQUE formula yet, is there any other alternative formula I can use?

I'm sorry, I know there are fairly complex formulas to accomplish this. Hopefully someone more advanced than me can help...
 

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows
I'm sorry, I know there are fairly complex formulas to accomplish this. Hopefully someone more advanced than me can help...
No worries, I tried to figure out the unique ID issue and it worked, however, when I'm trying to mimic your below formula I couldn't understand what the symbol "#" stands for?
2:E6E2=IF(D2="","",MAXIFS($B$2:$B$36,$A$2:$A$36,D2#))
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,354
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202009a.xlsm
ABCDE
1EE IDLast Modified DateUNIQUE IDMost Recent Update Date Per Employee
2111110-Jun-20111113-Jul-20
3111110-Jun-20
4111110-Jun-20
5111110-Jun-20
6111113-Jul-20
7111110-Jun-20
4b
Cell Formulas
RangeFormula
E2E2=AGGREGATE(14,6,$B$2:$B$1200/($A$2:$A$1200=D2),1)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,354
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I added the Array formula for the unique ID

T202009a.xlsm
DE
2111113-Jul-20
32222
4b
Cell Formulas
RangeFormula
E2E2=AGGREGATE(14,6,$B$2:$B$1200/($A$2:$A$1200=D2),1)
D2:D3D2=INDEX(A2:A36, MATCH(0, COUNTIF($A$1:A1,A2:A36), 0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,356
Messages
5,547,459
Members
410,793
Latest member
sauravg
Top