find the max date

[coolcarnee]

I have a dataset that looks something like this


1 13/05/2012

1 14/05/2012

1 15/05/2012

2 11/03/2012

2 12/03/2012

2 13/03/2012

3 01/01/2012

3 02/01/2012

3 03/01/2012


In the column C, I want a formula that will return the highest(max date) in column B for every value in column A. So the highest date for Id 1 is 15/05/2012. Highest date for Id 2 is 13/03/2012 and so on.. I tried to use the INDEX MATCH function, but I am not getting there.


Any help will be appreciated.

 

[VaraK]

Hi coolcarnee,


Say your data is from A1:B9, your formula in C1 would be :

=LOOKUP(A1,$A$1:$A$9,$B$1:$B$9) or simply =LOOKUP(A1,$A$1:$B$9)

Filldown the formula and you would see your result.


To acheive this you need to sort your Dates in ascending order. Similarly if you want to get min date you can sort your dates in descending order using the same formula. So, the trick in sorting


Hope this helps.


VaraK

 

[kaushik03]

Hi coolcarnee,


I try to solve your query in three different methods using sumproduct/max-index/dmax.Use whatever you want..


The sample file(solution) is uploaded here..

https://hotfile.com/dl/164309496/1b5f2ff/Max_Date.xlsx.html


However, later, you can think of uploading your sample file here to help others to have a better visualization of your data.

http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Kaushik

 

[sgmpatnaik]

Hi Coolcarnee


if my guess is wright then your data is insert the Id No. in ColumnA, Date in ColumnB and the result you want in ColumnC,ColumnD,ColumnE - C - id 1, D - id 2, E - id 3


the formula is used like this


=MAX($B$1:$B$9,$A$1:$A$9,$C$1)


AND THE SAME FORMULA U CAN USE IN REST COLUMNS


IF IT IS NOT USE PLEASE UPLOAD A SAMPLE FILE WE CAN ASSIST THROUGH


WITH REGARDS


PATNAIK

 

[Hui]

In C2 put

=IFERROR(IF(A2<>A3,MAX(IF(A:A=A2,B:B,)),""),"")

Ctrl+Shift+Enter

Copy C2 down