Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Nesting
Hello. I have a spreadsheet with numerous (hundereds) of naming conventions
in a field that I need to search. One common denominator, though, is that they will always contain text including either "BK", "SL", "OF", or "N". By using the formula =IF(ISNUMBER(A1),LOOKUP("BK",A1)),"BULK", "SIDELOAD"), this almost accomplishes what I need, except that there are the 4 conditions, not just two. I need to have a formula test for the text BK, and if that exists, return the word BULK, if BK is not contained in the cell, the formula should test for the text SL; if SL is contained in the cell, the return should be SIDELOAD, thirdly, if the text SL is not found in the cell, the formula should test for the text N; if N is found, the formula should return NIGHT SIDELOAD; and finally, if none of the prior 3 conditions is met, the formula should return OFS. I should be able to use the above formula, but I can't seem to get the syntax correct to nest it all together. Any help is much appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Nesting
This works but not very neat =IF(A1="BK","Bulk","")&IF(A1="SL","Sideload","")&I F(A1="N","Night","")&IF(A1="N","Night","")&IF(AND( A1<"BK",A1<"SL",A1<"N",A1<""),"OFS","") -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=560866 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Nesting
Hello there,
Thanks much for the advice. Will that work for my instance, where the whole cell contents will contain more than just BK, SL, OF, and N? The cells actually contain text including those letters, but the whole contents are similar to ABK01, ABK02 up through potentially ABK99 daily, PBK01, PBK02 potentially up to PBK99 daily, and the same for the Sideload, OFS and Nights, being ASL01 through ASL99, AOF01 through AOF99, ANS01 through ANS99, and other text combos for 44 sales centers up to 99 routes daily. Each route is named using a combination of the abbreviation for the location, the abbreviation for the route type, and a two digit route number from 01 to 99. That's why I was thinking I had to do a search just for the common text, since there are potentially 396 combinations for any given sales center, and 44 sales centers, adding up to 17,424 possible combinations, but the common denominator is that they will all contain the text BK, SL, OF, or N in the names. "VBA Noob" wrote: This works but not very neat =IF(A1="BK","Bulk","")&IF(A1="SL","Sideload","")&I F(A1="N","Night","")&IF(A1="N","Night","")&IF(AND( A1<"BK",A1<"SL",A1<"N",A1<""),"OFS","") -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=560866 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Nesting
Hi!
Try this: Make a little table somewhere like this: (I'll use the range G1:H4 for this example) .........G...............H 1.....BK............bulk 2.....SL.............sideload 3.....N..............night sideload 4.....OF............OFS Then use this array formula entered using the key combination of CTRL,SHIFT,ENTER: =CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH("*"&G$1:G$4&"*" ,A1)),0),H$1,H$2,H$3,H$4) Biff "logstx095" wrote in message ... Hello. I have a spreadsheet with numerous (hundereds) of naming conventions in a field that I need to search. One common denominator, though, is that they will always contain text including either "BK", "SL", "OF", or "N". By using the formula =IF(ISNUMBER(A1),LOOKUP("BK",A1)),"BULK", "SIDELOAD"), this almost accomplishes what I need, except that there are the 4 conditions, not just two. I need to have a formula test for the text BK, and if that exists, return the word BULK, if BK is not contained in the cell, the formula should test for the text SL; if SL is contained in the cell, the return should be SIDELOAD, thirdly, if the text SL is not found in the cell, the formula should test for the text N; if N is found, the formula should return NIGHT SIDELOAD; and finally, if none of the prior 3 conditions is met, the formula should return OFS. I should be able to use the above formula, but I can't seem to get the syntax correct to nest it all together. Any help is much appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Nesting
Or:
=INDEX(H$1:H$4,MATCH(TRUE,ISNUMBER(SEARCH("*"&G$1: G$4&"*",A1)),0)) Also array entered. Biff "Biff" wrote in message ... Hi! Try this: Make a little table somewhere like this: (I'll use the range G1:H4 for this example) ........G...............H 1.....BK............bulk 2.....SL.............sideload 3.....N..............night sideload 4.....OF............OFS Then use this array formula entered using the key combination of CTRL,SHIFT,ENTER: =CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH("*"&G$1:G$4&"*" ,A1)),0),H$1,H$2,H$3,H$4) Biff "logstx095" wrote in message ... Hello. I have a spreadsheet with numerous (hundereds) of naming conventions in a field that I need to search. One common denominator, though, is that they will always contain text including either "BK", "SL", "OF", or "N". By using the formula =IF(ISNUMBER(A1),LOOKUP("BK",A1)),"BULK", "SIDELOAD"), this almost accomplishes what I need, except that there are the 4 conditions, not just two. I need to have a formula test for the text BK, and if that exists, return the word BULK, if BK is not contained in the cell, the formula should test for the text SL; if SL is contained in the cell, the return should be SIDELOAD, thirdly, if the text SL is not found in the cell, the formula should test for the text N; if N is found, the formula should return NIGHT SIDELOAD; and finally, if none of the prior 3 conditions is met, the formula should return OFS. I should be able to use the above formula, but I can't seem to get the syntax correct to nest it all together. Any help is much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex calculations | Excel Discussion (Misc queries) | |||
How to compute the inverse of a matrix with some complex elements | Excel Worksheet Functions | |||
2 Nesting questions | Excel Worksheet Functions | |||
Importing XML containing Complex Elements | Excel Discussion (Misc queries) | |||
Matrix operations with complex numbers | Excel Discussion (Misc queries) |