Data searching

[Guest]

I have run into a bit of a problem. I have a list of data in 2 columns.
Column A are truck codes which are repetative and column B are time to which
they receive the delivery docket. My question/problem is this... what
formula would be the one to give me the last timestamp a particular truck
received his last docket so I may have the time he received his last load.

Regards,

 

[KC Rippstein]

If you put in C1 the word "Truck #:" and then use D1 for what truck # you
are looking for, this will be your query area. In C2, type the words "Last
Time Stamp:" and in D2 type the following formula:
=LOOKUP(2,1/($A:$A=$D$1),$B:$B)
This will give you the last time stamp for the truck # you specified in cell
D1. I recommend you shade cell D1 in a turquoise and put a light border
around it (like a solid dark gray line) so people are drawn to it and know
that is the only place they have to type in their query.

You could also write a macro that brings up a message box asking you what
truck # you want to search for and gives you the result in another message
box, but my solution above is the simplest and avoids security issues with
macros.
Cheers!
KC Rippstein

 

[Don Guillett]

try this array formula which must be entered using ctrl+shift+enter
=MAX(IF(A2:A22="mytruckcode",B2:B22))
or
=MAX(IF(A2:A22=c2,B2:B22))

 

[KC Rippstein]

After reading Don's post, I realized that I forgot to mention one thing
about my solution below. It assumes that times stamps are added
chronologically to the end of the list as they occur and are (as a result)
sorted from oldest at the top to most recent as you go down the page
indefinitely.

 

[KC Rippstein]

If column B truly is sorted the way you have shown here, my solution works.
The only problem is I put the query area in C12. Just move that to D1:E2
instead.