published fare

hanasamo

Board Regular
Joined
May 31, 2005
Messages
83
I have a air passenger tickets price list in a xls file called "published fare", it's like:
pek-sha 1130
pek-can 1500
sha-can 900
.
.
.
.
.
.
And there is a column in another xls file contains city pair like that:
pek-sha
pek-sha-pek
sha-pek
can-sha
.
.
.
.
.
.
I want to use a macro or a function that can list the price for each route in the column next to the city pair column, as listed in the "published fare",pek-sha & sha-pek are both 1130, pek-sha-pek=1130+1130=2260,etc. how can I do this?

Thanks in advance
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try this, it's a bit cumbersome but it works:
Book1
ABCD
1RouteFare
2pek-sha1130
3pek-sha-pek2260
4sha-pek1130
5can-sha900
Sheet1


Formula in B2 is :
Code:
=IF(LEN(A2)=7,IF(ISNA(VLOOKUP(A2,Fares,2,0)),VLOOKUP(RIGHT(A2,3)&"-"&LEFT(A2,3),Fares,2,0),VLOOKUP(A2,Fares,2,0)),2*(IF(ISNA(VLOOKUP(LEFT(A2,7),Fares,2,0)),VLOOKUP(RIGHT(A2,7),Fares,2,0),VLOOKUP(LEFT(A2,7),Fares,2,0))))
This assumes that :
(1) your Published Fares are in a named range "Fares" in this workbook
(2) city codes are always 3 digits
(3) routes with more than 2 city codes are RETURN flights not multiple destinations.

HTH
 
Upvote 0
Thanks Fergus.

Your advice is not quite understandable to me, I don't know if your function can work when there is a route like"pek-sha-can-sha-pek".
 
Upvote 0
No, the formula would not work with routes like "pek-sha-can-sha-pek". If you wanted a formula to do that, especially for an unlimited number of sectors it would be very long and cumbersome.

For what you want I reckon you need a macro, which is not my field, lets hope one of the code-gurus sees this and comes up with a solution for you.
 
Upvote 0
Assuming that you have each leg listed (e.g. pek-sha and sha-pek):

I have placed your legs in column G and the prices in H.

In A1, I put "pek-sha-van"
In cell B1:
=SUM(IF(ISNUMBER(SEARCH($G$1:$G$4,A2)),$H$1:$H$4,0))
Enter with shift-ctrl-enter.
 
Upvote 0
tactps said:
Assuming that you have each leg listed (e.g. pek-sha and sha-pek):

I have placed your legs in column G and the prices in H.

In A1, I put "pek-sha-van"
In cell B1:
=SUM(IF(ISNUMBER(SEARCH($G$1:$G$4,A2)),$H$1:$H$4,0))
Enter with shift-ctrl-enter.

Thank you, your function worked partially, for those multiple legs routes, it seems that it doesn't sum all the legs but part of them.
 
Upvote 0
=SUM(IF(ISNUMBER(SEARCH($G$1:$G$4,A2)),$H$1:$H$4,0))

First the search is the key:

It looks for the first leg (e.g. "pek-sha") as a string in cell A2. If it exists it then looks at the price (H1:H4), otherwise it ignores it (0).
Then it goes ont o the next one and add them up at the end.
 
Upvote 0
That's clever tactps, :-D but note that for return legs, i.e. sha-pek as opposed to pek-sha the route and fare have to be separately entered in columns G and H.
 
Upvote 0

Forum statistics

Threads
1,222,196
Messages
6,164,519
Members
451,900
Latest member
lamski

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