EXCEL Formula or VBA to determine max date based on multiple rows

[Stephen Spittal]

Afternoon all,

Im not sure of the best way to work this out.

I download a sheet from a database with the CR number as the identifier
the CR number (Column A) can have upto 12 * 8 rows each row has a focus date (Column L) im looking for away to identify the max date of Column L based on the number of rows for each CR number in Column A.

In the example sheet attached CR Number 202199 is identified in 9 rows A2:A10 and CR Number 202354 is identified in 13 rows A11:A23
In the formula [=IF(COUNTIF(A:A,A2)>0,MAX(L2:L10))] MAX(L2:L10) shoud change based on the change in CR Number rows.

 

[NARAYANK991]

Hi ,

Won't the following array formula , entered using CTRL SHIFT ENTER return this :

=MAX(IF($A$2:$A$23 = A2, $L$2:$L$23))

Or are you looking for something else ?

Narayan

 

[bosco_yip]

Maybe……..

In M2, formula copy down :

=AGGREGATE(14,6,L$2:L$23/(A$2:A$23=A2),1)

Regards
Bosco

 

[Stephen Spittal]

Narayan & Bosco,

Thank you very much both of those forumla do the job my only problem is i have over 20000 rows and they both take a long time to work, would there be a quicker way?

Many thanks for your help

Regards
Stephen

 

[vletm]

Stephen Spittal
Something like this way ...?
Answer: Columns O & P

 

[NARAYANK991]

Hi Stephen ,

A pivot table , as suggested by vletm , is the fastest ; if this is not acceptable to you , another approach would be to use a helper column.

See the attached file for this alternative.

Narayan

 

[Stephen Spittal]

Thank you all for uor help it is much appreciated I hadn't thought of a Pivot Table however both of these solutions worked

Many thanks