# Most Recent Date

#### zinah

##### Board Regular
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 Date Most 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!

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### sandy666

##### Well-known Member
with Power Query
 EE ID Date 1111 13/07/2020 2222 18/07/2020 3333 26/02/2020 4444 25/06/2020 5555 22/06/2020

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

#### KevCarter

##### Board Regular
Using Microsoft 365

Book1
ABCDE
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
with Power Query
 EE ID Date 1111 13/07/2020 2222 18/07/2020 3333 26/02/2020 4444 25/06/2020 5555 22/06/2020

Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
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

Using Microsoft 365

Book1
ABCDE
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?

#### sandy666

##### Well-known Member
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!
You are welcome

for future

#### KevCarter

##### Board Regular

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
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:E6 E2 =IF(D2="","",MAXIFS(\$B\$2:\$B\$36,\$A\$2:\$A\$36,D2#))

#### Dave Patton

##### Well-known Member
T202009a.xlsm
ABCDE
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
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.

Replies
2
Views
135
Replies
10
Views
234
Replies
0
Views
80
Replies
2
Views
43
Replies
8
Views
135