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:

[TABLE="width: 215"]
<colgroup><col span="2" style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Proj #[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Latest Date[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]4/1/2017[/TD]
[TD="align: center"]6/3/2017[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]5/1/2017[/TD]
[TD="align: center"]7/3/2017[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]6/1/2017[/TD]
[TD="align: center"]8/3/2017[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]5/2/2017[/TD]
[TD="align: center"]6/3/2017[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]6/2/2017[/TD]
[TD="align: center"]7/3/2017[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]7/2/2017[/TD]
[TD="align: center"]8/3/2017[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]6/3/2017[/TD]
[TD="align: center"]6/3/2017[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]7/3/2017[/TD]
[TD="align: center"]7/3/2017[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]8/3/2017[/TD]
[TD="align: center"]8/3/2017[/TD]
[/TR]
</tbody>[/TABLE]

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
Proj #	Date
A
B
C
A
B
C
A
B
C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Latest Date[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4/01/2017[/TD]
[TD="align: right"]6/03/2017[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5/01/2017[/TD]
[TD="align: right"]7/03/2017[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]6/01/2017[/TD]
[TD="align: right"]8/03/2017[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5/02/2017[/TD]
[TD="align: right"]6/03/2017[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6/02/2017[/TD]
[TD="align: right"]7/03/2017[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]7/02/2017[/TD]
[TD="align: right"]8/03/2017[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]6/03/2017[/TD]
[TD="align: right"]6/03/2017[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]7/03/2017[/TD]
[TD="align: right"]7/03/2017[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]8/03/2017[/TD]
[TD="align: right"]8/03/2017[/TD]

</tbody>

Latest Date​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=AGGREGATE(14,6,B$2:B$10/(A$2:A$10=A2),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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)