Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to manipulate text in one column
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to manipulate text in one column
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" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to manipulate text in one column
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to manipulate text in one column
Use something like
=IF(AND(ISNA(MATCH(I2,$A$2:$A$5,0)),ISNA(MATCH(RIG HT(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" wrote in message oups.com... 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 . . . 10 on SHEET2 C2 THROUGH C10 i fill with cse formula: =INDEX(SHEET1!C2:C4;MATCH(1;(SHEET2!A2=SHEET1!A2:A 4)*(SHEET2!B2=SHEET1!B2:B4 );0)) just like first, i have problem when i must fill SHEET 2 on A2 through A10 with return flight route and there's no space between rows. please give me advise... thx |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to manipulate text in one column
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to manipulate text in one column
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) "cah jogja" wrote in message oups.com... 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 . . . 100 i use something like : =INDEX($D$2:$D$4;MATCH(1;(A8=$A$2:$A$4)*(B8=$B$2$: $B$4)*(C8=$C$2:$C$4);0)) i can't combine your formula in to mine, so i can type route in any direction. please help me... many thx |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to manipulate text in one column
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to manipulate an 2D Array into a Column Array? | Excel Discussion (Misc queries) | |||
how to manipulate text in one column | Excel Programming | |||
Find a specific text item and manipulate cells surrounding | Excel Programming | |||
Manipulate an excel column | Excel Worksheet Functions | |||
Can you manipulate text documents using VBA in excel | Excel Programming |