how to manipulate text in one column

[cah jogja]

dear all,
sorry for rmy poor english, hope subject title can work . i use this
program for identify flight route, i use text for dget criteria
etc:
A B
C
1 CODE DETAIL
2 JOGCGK JOGJA to JAKARTA / JAKARTA to JOGJA
3 JOGDPS JOGJA to BALI / BALI to JOGJA
4 JOGSUB JOGJA to JAKARTA / JAKARTA to BALI
5
6 =DGET(A1:B4;"DETAIL";A8:A9)
7
8 CODE :
9
10

my problem is come up when the criteria is the return flight route. on
the criteria for A2 is CGKJOG instead JOGCGK. how to make those
criteria flight route also can be used as its return flight or for
example i can use JOGCGK as CGKJOG.
i really need the solution because there are thousand of routes. thanks
very much.

 

[Bob Phillips]

Maybe you could add this formula into A10

=RIGHT(A9,3)&LEFT(A9,3)

and change the DGET to =DGET(A1:B4;"DETAIL";A8:A10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[cah jogja]

dear bob,
it works !! thx very much!

but i get new problem with it. what if i don't have space for formula
you've gave? how to combine those formula into one? like when i use
index in this situation:

SHEET1 (DATA)
A B C

1 CODE CLASS PRICE
2 JOGCGK V 100
3 JOGDPS Z 225
4 JOGSUB S 357


SHEET2
A B C
1 CODE CLASS PRICE
2
3

 

[Bob Phillips]

Use something like

=IF(AND(ISNA(MATCH(I2,$A$2:$A$5,0)),ISNA(MATCH(RIGHT(I2,3)&LEFT(I2,3),$A$2:$
A$5,0))),"",IF(ISNA(MATCH(I2,$A$2:$A$5,0)),VLOOKUP(RIGHT(I2,3)&LEFT(I2,3),A2
:C5,2,FALSE),VLOOKUP(I2,$A$2:$C$5,2,FALSE)))

where I2 holds your code and ,2 referes to the column, C LASS in this case

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[cah jogja]

hi again,
i've been trying to combine those formulas with my multiple criteria
index case, still i can't make it...
here again bob :

A B C D
1 CODE CLASS ROUTE PRICE
2 JT V JOGCGK 215
3 7P S CGKPNK 148
4 KI B SUBJOG 215
5
6
7 CODE CLASS ROUTE PRICE
8
9
10

 

[Bob Phillips]

You're changing the data. Your previous Code values are now route values. It
is hardly surprising the formula doesn't work.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[dede2924]

i'm sorry bob, i''ve been away and just read ur message.

the last situation is i've been facing right now. i know Code values is
changing in to Airlines Code. i'm trying to use ur formula in any
conditions. pls advise

many thx