Max date

[Forestq]

Hi I have source data into sheet1 like:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]01/01/2015[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]30/03/2015[/TD]
[/TR]
[TR]
[TD]Bmw[/TD]
[TD]06/06/2015[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]15/07/2015[/TD]
[/TR]
[TR]
[TD]Bmw[/TD]
[TD]02/02/2015[/TD]
[/TR]
</tbody>[/TABLE]

Into sheet2 I have unique list of values from columnA:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]15/07/2015[/TD]
[/TR]
[TR]
[TD]Bmw[/TD]
[TD]06/06/2015[/TD]
[/TR]
</tbody>[/TABLE]

How to write function and put it into columnB (sheet2) that will give me max date (the newest) for value from columnA?

 

[Aladin Akyurek]

In B2 of Sheet2 control+shift+enter, not just enter, and copy down:

=MAX(IF(Sheet1!$A$2:$A$6=A2,Sheet1!$B$2:$B$6))

 

[Useful]

Hello!

create pivot table from value field settings choose max ?

 

[Forestq]

Hi Aladin,

something is wrong, I got error:

=MAX(IF(Portal!$M$2:$M$15918=Sheet2!A1,Portal!$AO$2:$AO$15918))

 

[Forestq]

ok, ctrl+shift+enter and is working

can I also use it into vba?

 

[Aladin Akyurek]

Hi Aladin,

something is wrong, I got error:

=MAX(IF(Portal!$M$2:$M$15918=Sheet2!A1,Portal!$AO$2:$AO$15918))

ok, ctrl+shift+enter and is working

Right...

can I also use it into vba?

Yes, I think you can find at this board examples of code which include such formulas.