published fare

[hanasamo]

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

 

[Fergus]

Try this, it's a bit cumbersome but it works:

Book1
	A	B	C	D
1	Route	Fare
2	pek-sha	1130
3	pek-sha-pek	2260
4	sha-pek	1130
5	can-sha	900
Sheet1

Formula in B2 is :

=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

 

[hanasamo]

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".

 

[Fergus]

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.

 

[hanasamo]

Thank you so much. I'll expect a VBA expert who can help me.

 

[hanasamo]

I'm waiting.......

 

[tactps]

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.

 

[hanasamo]

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.

 

[tactps]

=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.

 

[Fergus]

That's clever tactps, 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.