Most Recent Date

[zinah]

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!

 

[sandy666]

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

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]

Using Microsoft 365

Book1
	A	B	C	D	E
1	EE ID	Last Modified Date		UNIQUE ID	Most Recent Update Date Per Employee
2	1111	6/10/20		1111	7/13/20
3	1111	6/10/20		2222	8/8/20
4	1111	6/10/20		3333	2/26/20
5	1111	6/10/20		4444	6/25/20
6	1111	7/13/20		5555	6/24/20
7	1111	6/10/20
8	2222	2/28/20
9	2222	7/18/20
10	2222	7/18/20
11	2222	8/8/20
12	2222	7/18/20
13	2222	7/18/20
14	2222	7/18/20
15	2222	7/18/20
16	3333	2/26/20
17	3333	2/26/20
18	3333	2/26/20
19	3333	2/26/20
20	3333	2/26/20
21	3333	2/26/20
22	4444	6/25/20
23	4444	6/25/20
24	4444	6/25/20
25	4444	6/25/20
26	4444	6/25/20
27	4444	6/25/20
28	4444	6/25/20
29	4444	6/25/20
30	4444	6/25/20
31	5555	6/22/20
32	5555	6/22/20
33	5555	6/24/20
34	5555	6/22/20
35	5555	6/22/20
36	5555	6/22/20
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=SORT(UNIQUE(A2:A36))
E2:E6	E2	=IF(D2="","",MAXIFS($B$2:$B$36,$A$2:$A$36,D2#))
Dynamic array formulas.

 

[zinah]

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

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]

Using Microsoft 365

Book1
	A	B	C	D	E
1	EE ID	Last Modified Date		UNIQUE ID	Most Recent Update Date Per Employee
2	1111	6/10/20		1111	7/13/20
3	1111	6/10/20		2222	8/8/20
4	1111	6/10/20		3333	2/26/20
5	1111	6/10/20		4444	6/25/20
6	1111	7/13/20		5555	6/24/20
7	1111	6/10/20
8	2222	2/28/20
9	2222	7/18/20
10	2222	7/18/20
11	2222	8/8/20
12	2222	7/18/20
13	2222	7/18/20
14	2222	7/18/20
15	2222	7/18/20
16	3333	2/26/20
17	3333	2/26/20
18	3333	2/26/20
19	3333	2/26/20
20	3333	2/26/20
21	3333	2/26/20
22	4444	6/25/20
23	4444	6/25/20
24	4444	6/25/20
25	4444	6/25/20
26	4444	6/25/20
27	4444	6/25/20
28	4444	6/25/20
29	4444	6/25/20
30	4444	6/25/20
31	5555	6/22/20
32	5555	6/22/20
33	5555	6/24/20
34	5555	6/22/20
35	5555	6/22/20
36	5555	6/22/20
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=SORT(UNIQUE(A2:A36))
E2:E6	E2	=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]

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

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

 

[KevCarter]

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]

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]

T202009a.xlsm
	A	B	C	D	E
1	EE ID	Last Modified Date		UNIQUE ID	Most Recent Update Date Per Employee
2	1111	10-Jun-20		1111	13-Jul-20
3	1111	10-Jun-20
4	1111	10-Jun-20
5	1111	10-Jun-20
6	1111	13-Jul-20
7	1111	10-Jun-20
4b

Cell Formulas
Range	Formula
E2	E2	=AGGREGATE(14,6,$B$2:$B$1200/($A$2:$A$1200=D2),1)

 

[Dave Patton]

I added the Array formula for the unique ID

T202009a.xlsm
	D	E
2	1111	13-Jul-20
3	2222
4b

Cell Formulas
Range	Formula
E2	E2	=AGGREGATE(14,6,$B$2:$B$1200/($A$2:$A$1200=D2),1)
D2:D3	D2	=INDEX(A2:A36, MATCH(0, COUNTIF($A$1:A1,A2:A36), 0))
Press CTRL+SHIFT+ENTER to enter array formulas.