How do I.....

adamdalz

New Member
Joined
Apr 17, 2002
Messages
1
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top