Max date value based on project

[tdn2937]

I'm trying to find a formula that would return the latest date for each project. For example, here is what I would like to get:

Proj #	Date	Latest Date
A	4/1/2017	6/3/2017
B	5/1/2017	7/3/2017
C	6/1/2017	8/3/2017
A	5/2/2017	6/3/2017
B	6/2/2017	7/3/2017
C	7/2/2017	8/3/2017
A	6/3/2017	6/3/2017
B	7/3/2017	7/3/2017
C	8/3/2017	8/3/2017

<colgroup><col span="2" style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

Thanks in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this, copied down.

Book1
	A	B	C
1	Proj #	Date	Latest Date
2	A	4/01/2017	6/03/2017
3	B	5/01/2017	7/03/2017
4	C	6/01/2017	8/03/2017
5	A	5/02/2017	6/03/2017
6	B	6/02/2017	7/03/2017
7	C	7/02/2017	8/03/2017
8	A	6/03/2017	6/03/2017
9	B	7/03/2017	7/03/2017
10	C	8/03/2017	8/03/2017
Latest Date

Cell Formulas
Range	Formula
C2	=AGGREGATE(14,6,B$2:B$10/(A$2:A$10=A2),1)

 

[AndrewHoffenberg]

You can also use the following array formula in cell C2:
=MAX(IF(A:A=A2,B:B,0))
ctrl+shft+enter

Welcome to the MrExcel board!

Try this, copied down.

	A	B	C
1	Proj #	Date	Latest Date
2	A	4/01/2017	6/03/2017
3	B	5/01/2017	7/03/2017
4	C	6/01/2017	8/03/2017
5	A	5/02/2017	6/03/2017
6	B	6/02/2017	7/03/2017
7	C	7/02/2017	8/03/2017
8	A	6/03/2017	6/03/2017
9	B	7/03/2017	7/03/2017
10	C	8/03/2017	8/03/2017

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Latest Date​

Worksheet Formulas Cell Formula C2 =AGGREGATE(14,6,B$2:B$10/(A$2:A$10=A2),1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Peter_SSs]

You can also use the following array formula in cell C2:
=MAX(IF(A:A=A2,B:B,0))
ctrl+shft+enter

You certainly could, but I would highly recommend still restricting the row range. For example
=MAX(IF(A$2:A$10=A2,B$2:B$10,0))

Without that, even with this small sample of 9 rows of data on my machine this formula and the one from post #2 takes about 0.003 seconds to re-calculate but using the whole column references takes over 1 second - approx 300x slower.

 

[Aladin Akyurek]

Also, if your system includes the MAXIFS function, in C2 enter and copy down:

=MAXIFS($B$2:$B$10,$A$2:$A$10,$A2)