How do I.....

[adamdalz]

I have a logbook in excel. Included is a column with airplane names in it, and a column with dates when i flew those airplanes. What i'm looking for is a function that will tell me the date that i last flew a specific airplane.

For example, given

column 1 = piper, piper, zlin, piper, zlin
column 2 = may 10, may 12, may 13, may 15, may 16

I want a function that will look through the list and tell me I last flew a piper on may 15.

Thanks!

- Adam

[Yogi Anand]

On 2002-04-18 17:44, adamdalz wrote:
I have a logbook in excel. Included is a column with airplane names in it, and a column with dates when i flew those airplanes. What i'm looking for is a function that will tell me the date that i last flew a specific airplane.

For example, given

column 1 = piper, piper, zlin, piper, zlin
column 2 = may 10, may 12, may 13, may 15, may 16

I want a function that will look through the list and tell me I last flew a piper on may 15.

Thanks!

- Adam

Hi Adam:
Welcome to the board. One of the ways you can this is by using the DMAX function. Let us say your data is set up as follows
cell A1 ... Plane
cell A2 ... piper
cell A3 ... piper
cell A4 ... zin
cell A5 ... piper
cell a6 ... zin

cell B1 ... fly_date
cell B2 ... 5/10
cell B3 ... 5/12
cell B4 ... 5/13
cell B5 ... 5/15
cell B6 ... 5/16

then you want to put your criteria in cells D1:D2
in cell D1 ... Plane
in cell D2 ... piper

then the formula for the last date you flew piper:

=DMAX(A1:B6,2,D1:D2) ... giving you 5/15

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!


_________________
Yogi Anand
Edit: Deleted reference to inactive web site from signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 18:30 ]

[robfo0]

heres a quick macro, but it looks like yogi's solution is probably better, and easier So many functions, so little time to learn them all

Sub Fly_search()

planename = InputBox("Please input the name of the plane you are looking for:")
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

For Each i In Range("A1", Range("A65536").End(xlUp))
If i.Value = planename Then
If i.Offset(1, 0) <> planename Then flydate = i.Offset(0, 1)
End If
Next i
If flydate = "" Then
MsgBox ("plane not found")
Else
MsgBox ("The last date " & planename & " was flown was " & flydate)
End If
End Sub


[ This Message was edited by: robfo0 on 2002-04-18 19:52 ]

[Ice5]

I'm "adamdalz" friend...

this past weekend I was trying to get the DMAX function to work but I just couldn't get the fields in the right order or something.

the DMAX function is a bit shifty.

Good to know I was on the right track though.

Now I undersand that in DMAX the first is the range to check, and the third is the criteria... what is the second field, (The '2' in this case)?

[ This Message was edited by: Ice5 on 2002-04-18 21:10 ]

[robfo0]

the 2 is the column where the values are which you want to return, in this case you would have the database defined as A1:B6, B or the 2nd column contains the dates which you are looking for. if it were A1:C6, and c contained other data, and you were looking for something in that column, you would have a 3 instead of 2

[Yogi Anand]

Hi Ice5:
To elaborate what robfo0 stated, 2 in the formula

=DMAX(A1:B6,2,D1:D2) is the number of column in the field in which the value being sought lies. I could also have substituted the field name in the formula instead of the associated column number, so the formula would have been...

=DMAX(A1:B6,"fly_date",D1:D2) and the result would have been the same 5/15

Regards!

Yogi anand