Searching for value(s) in cells and return the result

[ThanhTC]

i'm very new with using VBA to solve the excel problems, but i have to work in rather complicated task :

If the cells in route collums contain an exact strings of text(i.e: DMEVNSGN, DMEX/SGN, DMEVNSGN, DMEX/SGN or SGNVNDME,SGNX/DME, HANVNDME,HANX/DME...) it would return the value in the exact format like DME-SGN,DME-HAN or SGN-DME,DME-HAN in vice versa.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Route[/TD]
[TD]Inbound[/TD]
[TD]Outbound[/TD]
[/TR]
[TR]
[TD][TABLE="width: 244"]
<tbody>[TR]
[TD="width: 244"]SGNVNHKGVNSGN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]XXX-XXX[/TD]
[TD]XXX-XXX[/TD]
[/TR]
[TR]
[TD][TABLE="width: 244"]
<tbody>[TR]
[TD="width: 244"]SGNVNDMEVNSGN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DME-SGN[/TD]
[TD]SGN-DME[/TD]
[/TR]
[TR]
[TD][TABLE="width: 244"]
<tbody>[TR]
[TD="width: 244"]SGNVNHANVNDME[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DME-HAN[/TD]
[TD]HAN-DME[/TD]
[/TR]
[TR]
[TD][TABLE="width: 244"]
<tbody>[TR]
[TD="width: 244"]DMEVNSGNVNPQCVNSGNVNDME[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DME-SGN[/TD]
[TD]SGN-DME[/TD]
[/TR]
[TR]
[TD][TABLE="width: 244"]
<tbody>[TR]
[TD="width: 244"]LEDX/DMEVNSGNVNREPVNSGNVNNHAVNHANVNDMEFVLED[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DME-SGN[/TD]
[TD]HAN-DME[/TD]
[/TR]
[TR]
[TD][TABLE="width: 244"]
<tbody>[TR]
[TD="width: 244"]LEDX/DMEVNHANVNNHAVNHANVNDMEFVLED[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DME-HAN[/TD]
[TD]HAN-DME[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
i do have a formula to do it automatically, but it rather long and would take long time to process if the data need to processed is big.
here is the code:
ver.1
(the longer version)
inbound

=IF(AND(D2="PAX",X2="PS"),IF(ISNUMBER(SEARCH("LHRVNHAN",C2)),"LHR-HAN",
IF(ISNUMBER(SEARCH("LGWX/HAN",C2)),"LHR-HAN",
IF(ISNUMBER(SEARCH("LHRVNSGN",C2)),"LHR-HAN",
IF(ISNUMBER(SEARCH("LHRX/SGN",C2)),"LHR-HAN",
IF(ISNUMBER(SEARCH("LGWVNHAN",C2)),"LGW-HAN",
IF(ISNUMBER(SEARCH("LGWX/HAN",C2)),"LGW-HAN",
IF(ISNUMBER(SEARCH("LGWVNSGN",C2)),"LGW-SGN",
IF(ISNUMBER(SEARCH("LGWX/SGN",C2)),"LGW-SGN",
IF(ISNUMBER(SEARCH("DMEVNHAN",C2)),"DME-HAN",
IF(ISNUMBER(SEARCH("DMEX/HAN",C2)),"DME-HAN",
IF(ISNUMBER(SEARCH("DMEVNSGN",C2)),"DME-SGN",
IF(ISNUMBER(SEARCH("DMEX/SGN",C2)),"DME-SGN",
IF(ISNUMBER(SEARCH("DMEVNCXR",C2)),"DME-CXR",
IF(ISNUMBER(SEARCH("DMEX/CXR",C2)),"DME-CXR",
IF(ISNUMBER(SEARCH("CDGVNHAN",C2)),"CDG-HAN",
IF(ISNUMBER(SEARCH("CDGX/HAN",C2)),"CDG-HAN",
IF(ISNUMBER(SEARCH("CDGVNSGN",C2)),"CDG-SGN",
IF(ISNUMBER(SEARCH("CDGX/SGN",C2)),"CDG-SGN",
IF(ISNUMBER(SEARCH("FRAVNHAN",C2)),"FRA-HAN",
IF(ISNUMBER(SEARCH("FRAX/HAN",C2)),"FRA-HAN",
IF(ISNUMBER(SEARCH("FRAVNSGN",C2)),"FRA-SGN",
IF(ISNUMBER(SEARCH("FRAX/SGN",C2)),"FRA-SGN","XXX")))))))))))))))))))))),"XXX")

outbound

=IF(AND(D2="PAX",X2="PS"),IF(ISNUMBER(SEARCH(“HANVNLHR”,C2)),“HAN-LHR”,
IF(ISNUMBER(SEARCH(“HAN/XLHR”,C2)),”HAN-LHR”,
IF(ISNUMBER(SEARCH(“SGNVNLHR”,C2)),”HAN-LHR”,
IF(ISNUMBER(SEARCH(“SGN/XLHR”,C2)),”HAN-LHR”,
IF(ISNUMBER(SEARCH("HANVNLGW",C2)),"HAN-LGW",
IF(ISNUMBER(SEARCH("HANX/LGW",C2)),"HAN-LGW",
IF(ISNUMBER(SEARCH("SGNVNLGW",C2)),"SGN-LGW",
IF(ISNUMBER(SEARCH("SGNX/LGW",C2)),"SGN-LGW",
IF(ISNUMBER(SEARCH("HANVNDME",C2)),"HAN-DME",
IF(ISNUMBER(SEARCH("HANX/DME",C2)),"HAN-DME",
IF(ISNUMBER(SEARCH("SGNVNDME",C2)),"SGN-DME",
IF(ISNUMBER(SEARCH("SGNX/DME",C2)),"SGN-DME",
IF(ISNUMBER(SEARCH("CXRVNDME",C2)),"CXR-DME",
IF(ISNUMBER(SEARCH("CXRX/DME",C2)),"CXR-DME",
IF(ISNUMBER(SEARCH("HANVNCDG",C2)),"HAN-CDG",
IF(ISNUMBER(SEARCH("HANX/CDG",C2)),"HAN-CDG",
IF(ISNUMBER(SEARCH("SGNVNCDG",C2)),"SGN-CDG",
IF(ISNUMBER(SEARCH("SGNX/CDG",C2)),"SGN-CDG",
IF(ISNUMBER(SEARCH("HANVNFRA",C2)),"HAN-FRA",
IF(ISNUMBER(SEARCH("HANX/FRA",C2)),"HAN-FRA",
IF(ISNUMBER(SEARCH("SGNVNFRA",C2)),"SGN-FRA",
IF(ISNUMBER(SEARCH("SGNX/FRA",C2)),"SGN-FRA","XXX")))))))))))))))))))))),"XXX")

ver.2(the shorter version, but having problems in output display)
inbound:

=IF(AND(D2="PAX",Z2="PS"),
IF(OR(ISNUMBER(SEARCH("DMEVNHAN",C2)),ISNUMBER(SEARCH("DMEX/HAN",C2))),"DME-HAN",
IF(OR(ISNUMBER(SEARCH("DMEVNSGN",C2)),ISNUMBER(SEARCH("DMEX/SGN",C2))),"DME-SGN",
IF(OR(ISNUMBER(SEARCH("DMEVNCXR",C2)),ISNUMBER(SEARCH("DMEX/CXR",C2))),"DME-CXR",
IF(OR(ISNUMBER(SEARCH("LHRVNHAN",C2)),ISNUMBER(SEARCH("LHRX/HAN",C2))),"LHR-HAN",
IF(OR(ISNUMBER(SEARCH("LHRVNSGN",C2)),ISNUMBER(SEARCH("LHRX/SGN",C2))),"LHR-SGN",
IF(OR(ISNUMBER(SEARCH("LGWVNHAN",C2)),ISNUMBER(SEARCH("LGWX/HAN",C2))),"LGW-HAN",
IF(OR(ISNUMBER(SEARCH("LGWVNSGN",C2)),ISNUMBER(SEARCH("LGWX/SGN",C2))),"LGW-SGN",
IF(OR(ISNUMBER(SEARCH("CDGVNHAN",C2)),ISNUMBER(SEARCH("CDGX/HAN",C2))),"CDG-HAN",
IF(OR(ISNUMBER(SEARCH("CDGVNSGN",C2)),ISNUMBER(SEARCH("CDGX/SGN",C2))),"CDG-SGN",
IF(OR(ISNUMBER(SEARCH("FRAVNHAN",C2)),ISNUMBER(SEARCH("FRAX/HAN",C2))),"FRA-HAN",
 IF(OR(ISNUMBER(SEARCH("FRAVNSGN",C2)),ISNUMBER(SEARCH("FRAX/SGN",C2))),"FRA-SGN",“XXX”))))))))))),"XXX")

outbound:

=IF(AND(D2="PAX",Z2="PS"),
IF(OR(ISNUMBER(SEARCH("HANVNDME",C2)),ISNUMBER(SEARCH("HANX/DME",C2))),"HAN-DME",
IF(OR(ISNUMBER(SEARCH("SGNVNDME",C2)),ISNUMBER(SEARCH("SGNX/DME",C2))),"SGN-DME",
IF(OR(ISNUMBER(SEARCH("CXRVNDME",C2)),ISNUMBER(SEARCH("CXRX/DME",C2))),"CXR-DME",
IF(OR(ISNUMBER(SEARCH("HANVNLHR",C2)),ISNUMBER(SEARCH("HANX/LHR",C2))),"HAN-LHR",
IF(OR(ISNUMBER(SEARCH("SGNVNLHR",C2)),ISNUMBER(SEARCH("SGNX/LHR",C2))),"SGN-LHR",
IF(OR(ISNUMBER(SEARCH("HANVNLGW",C2)),ISNUMBER(SEARCH("HANX/LGW",C2))),"HAN-LGW",
IF(OR(ISNUMBER(SEARCH("SGNVNLGW",C2)),ISNUMBER(SEARCH("SGNX/LGW",C2))),"SGN-LGW",
IF(OR(ISNUMBER(SEARCH("HANVNCDG",C2)),ISNUMBER(SEARCH("HANX/CDG",C2))),"HAN-CDG",
IF(OR(ISNUMBER(SEARCH("SGNVNCDG",C2)),ISNUMBER(SEARCH("SGNX/CDG",C2))),"SGN-CDG",
IF(OR(ISNUMBER(SEARCH("HANVNFRA",C2)),ISNUMBER(SEARCH("HANX/FRA",C2))),"HAN-FRA",
 IF(OR(ISNUMBER(SEARCH("SGNVNFRA",C2)),ISNUMBER(SEARCH("SGNX/FRA",C2))),"SGN-FRA",“XXX”))))))))))),"XXX")

here is the question: is there anyway to reduce the length of the formula, or someway to make VBA work for the required task like this?
if you need sampling data, please leave a message.
many thanks

 

[gaz_chops]

So you want to populate the Inbound/Outbound columns based on the 1st 8 digits of the route?

 

[gaz_chops]

If so then create a table like
Route Inbound Outbound
SGNVNHKG XXX-XXX XXX-XXX
SGNVNDME DME-SGN SGN-DME
SGNVNHAN DME-HAN HAN-DME
DMEVNSGN DME-SGN SGN-DME
LEDX/DME DME-SGN HAN-DME
LEDX/DME DME-HAN HAN-DME

Then use an index/match like (adjust the ranges to suit your data)
=INDEX($K$1:$M$7,MATCH(LEFT($C2,8),$K$1:$K$7,0),MATCH(D$1,$K$1:$M$1,0))

 

[XOR LX]

Hi.

Your inbound formula appear to repeat one of the search strings twice, with different returns:

IF(ISNUMBER(SEARCH("LGWX/HAN",F2)),"LHR-HAN"
IF(ISNUMBER(SEARCH("LGWX/HAN",F2)),"LGW-HAN"

Is this an oversight?

Also, rather than having all those conditions within a single formula, it would be much better to construct a simple table, e.g. in A1:B22, with your search strings (e.g. "LHRVNHAN", "LGWX/HAN", etc.) in A1:A22 and your corresponding returns (e.g. "LHR-HAN", "LHR-HAN", etc.) in B1:B22, after which your formula is simply:

=LOOKUP(2^15,SEARCH(A1:A22,F2),B1:B22)

though it should be pointed out that, should more than one of the search strings be found, this formula will be biased towards that which appears later in that list.

Regards

 

[gaz_chops]

Can I ask, what does the 2^15 mean?

 

[XOR LX]

Can I ask, what does the 2^15 mean?

The largest possible return from the SEARCH function is 32,767.

In this particular use of LOOKUP, we simply require that the lookup_value be greater than or equal to the return from the SEARCH function. Since 2^15 = 32,768, this is guaranteed to always be the case.

Of course, you could use any value which is >=32,767, though I personally happen to think there's a sense of efficiency (and perhaps also elegance) in using the least possible value, as opposed to the "blanket" approach of using, e.g. "BigNum" in all cases.

That said, my choice does mean that you have to first perform some sort of research as to what that "least possible value" is, though perhaps that's worth doing, at least in my opinion.

Regards

 

[gaz_chops]

As we are looking for text, why does the value matter?

 

[XOR LX]

As we are looking for text, why does the value matter?

Do you understand how LOOKUP operates in this construction? Do you know that SEARCH returns a numerical value?

Regards

 

[gaz_chops]

Thanks for the explanation!

 

[ThanhTC]

this is the explanation for the route
in example:
LEDX/DMEVNHANX/SGNVNDMESULED
the fist 3 character is the city code of original destination, the next 2 is the airline code, the next 3 is the first destination city code and so on.
Breaking down the above route, remove the airline we have 6 routing:
1st: LED-DME
2nd DME-HAN
3rd: HAN-SGN
4th: SGN-DME
5th:DME-LED
so, in order to recognize the desired routingin each route, i have to filter out the exact combination of the route(DMEVNHAN and SGNVNDME) to do it, no matter where it positioned in the "route"

Hi.

Your inbound formula appear to repeat one of the search strings twice, with different returns:

IF(ISNUMBER(SEARCH("LGWX/HAN",F2)),"LHR-HAN"
IF(ISNUMBER(SEARCH("LGWX/HAN",F2)),"LGW-HAN"

Is this an oversight?

Also, rather than having all those conditions within a single formula, it would be much better to construct a simple table, e.g. in A1:B22, with your search strings (e.g. "LHRVNHAN", "LGWX/HAN", etc.) in A1:A22 and your corresponding returns (e.g. "LHR-HAN", "LHR-HAN", etc.) in B1:B22, after which your formula is simply:

=LOOKUP(2^15,SEARCH(A1:A22,F2),B1:B22)

though it should be pointed out that, should more than one of the search strings be found, this formula will be biased towards that which appears later in that list.

Regards

well,thankyou for pointed out. that's a mistake of formula drafting
and your methods worked wonderfully, but i can still improvised it a little bit more to fully remove the #N/A result
thanks a lot