rank highest date together with its adjacent rows

[stoey]

hi,

I hope I could get more help this time, can someone help me out how to display the highest date together with the adjacent data on its row? I have attached a sample file.

regards,

stoey

[StephenR]

Highest start time, right? You don't need VBA.

J37: =INDEX(J12:J35,MATCH($K$37,$K$12:$K$35,0))
K37: =MAX(K12:K35)
L37 (copy from J37): =INDEX(L12:L35,MATCH($K$37,$K$12:$K$35,0))

[stoey]

hi,

Highest start time, right? You don't need VBA.

actually its the date with the highest start time...is it possible to just simply use one formula which will eventually display the date which has the highest start time together with its start and end time

regards,

stoey

[StephenR]

You can put the results into one cell by concatenating those formulae.

[stoey]

i'm not that familiar on how to do that though...can you give me an idea?

[martindwilson]

to return all in one cell
=TEXT(INDEX($J$12:$J$35,MATCH($K$37,$K$12:$K$35,0)),"d-mmm-yy")&" " &TEXT(MAX($K$12:$K$35),"hh:mm")&" "&TEXT(INDEX($L$12:$L$35,MATCH($K$37,$K$12:$K$35,0)),"hh:mm")

BIG EDIT.............
thats not quite right however!!!!
it needs not to ref K37 but have the formula from k37 instead so
=TEXT(INDEX($J$12:$J$35,MATCH(MAX(K$12:$K$35),$K$12:$K$35,0)),"d-mmm-yy")&" " &TEXT(MAX($K$12:$K$35),"hh:mm")&" "&TEXT(INDEX($L$12:$L$35,MATCH(MAX($K$12:$K$35),$K$12:$K$35,0)),"hh:mm")

[stoey]

wow! that works perfectly martindwilson...thanks a lot! hey stephen thanks a lot as well..your formula works perfectly as well... thanks a lot for all your quick responses...You guys Rock


Regards,

stoey

[martindwilson]

STOEY
before you say ah ha there is a problem !!!! re read my previous post lol