Group values and get max date from the grouped value

Hello,

I need help on my report, I have the following data;
Name Date
A,Trial 12/17/2012 9:00
A,Trial 12/16/2012 7:00
A,Trial 12/15/2012 7:00
B,Trial 12/16/2012 10:00
B,Trial 12/16/2012 8:00
C,Trial 12/16/2012 7:00
C,Trial 12/16/2012 9:00
C,Trial 12/19/2012 7:00

I need some macro that will get all the maximum date grouped by name. So the result should be;

A,Trial 12/17/2012 9:00
B,Trial 12/16/2012 10:00
C,Trial 12/19/2012 7:00

Thank you all in advance.

Originally Posted by arlu1201

So should the other rows be deleted which are not maximum?

Hi arlu, thanks for seeing my thread, if possible I want it in another sheet. My brain's almost going to explode, hehehe.

Is the data in 2 columns ?
you could use this to fond the unique trials
=INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($E$1:E1,$A$2:$A$20),0,0),0))

and then use this to get the max
=MAX(IF(A2:A100=E2,B2:B100)) as an array formula - so you need to use - control + shift + enter

I will add onto a separate sheet and attach here -

put into sheet2 A2
=INDEX(sheet1!$A$2:$A$20,MATCH(0,INDEX(COUNTIF($A$1:A1,sheet1!$A$2:$A$20),0,0),0))
and copy down
then in B2 sheet2
=MAX(IF(sheet1!A2:A20=a2,sheet1!B2:B20)) so you need to use - control + shift + enter and { } brackets should appear around the formula
copy down

Originally Posted by etaf

Is the data in 2 columns ?
you could use this to fond the unique trials
=INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($E$1:E1,$A$2:$A$20),0,0),0))

and then use this to get the max
=MAX(IF(A2:A100=E2,B2:B100)) as an array formula - so you need to use - control + shift + enter

I will add onto a separate sheet and attach here -

put into sheet2 A2
=INDEX(sheet1!$A$2:$A$20,MATCH(0,INDEX(COUNTIF($A$1:A1,sheet1!$A$2:$A$20),0,0),0))
and copy down
then in B2 sheet2
=MAX(IF(sheet1!A2:A20=a2,sheet1!B2:B20)) so you need to use - control + shift + enter and { } brackets should appear around the formula
copy down

hi etaf, yes it is in two columns... it's really great! i appreciate the help, I'll try to translate your formula into macro code.

Thanks a lot!!! Cheers!

Originally Posted by mike7952

This should do it

Please Login or Register  to view this content.

Hi Mike! Cool! Thank you very much for the code, I'll check on this once I'm no longer busy. Thank you all, this site is amazing.

Originally Posted by popoyjin

Hi Mike! Cool! Thank you very much for the code, I'll check on this once I'm no longer busy. Thank you all, this site is amazing.

Wow! Macro is working, really, really great! Thank you all!!! Problem solved, you all saved my day!