hi I have a spreadsheet where i have a very large nested if statement displaying a code
depending on what is in another cell.
the problem i have is i have reached the maximum number i can have. Is there a way of going to another cell from within the function checking for the extra entrys and then returning back to the same point in the original function
Last edited by JEM UK; 11-10-2011 at 05:17 PM.
You will need to show us the formula so we can suggest a workaround.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
=IF(LEFT(B502,4)="ALDI","CT",IF(LEFT(B502,6)="FALCON","C.O.S",IF(LEFT(B502,6)="binder","ST",IF(LEFT(B502,3)="BP ","F",IF(LEFT(B502,7)="B & Q","C.O.S",IF(LEFT(B502,6)="REFUND","RF",IF(RIGHT(B502,5)="CHRIS","TU",IF(RIGHT(B502,13)="COUNCIL858715","CTX",IF(LEFT(B502,7)="CLIFF'S","C.O.S",IF(LEFT(B502,5)="CO-OP","PL",IF(LEFT(B502,4)="CASH","PL",IF(LEFT(B502,15)="DULUX DECORATOR","C.O.S",IF(LEFT(B502,4)="ESSO","F",IF(LEFT(B502,4)="FOBI","C.O.S",IF(LEFT(B502,5)="FOCUS","C.O.S",IF(MID(B502,22,5)="FRNDS","PN",IF(LEFT(B502,6)="GRAHAM","C.O.S",IF(LEFT(B502,9)="HANDYHIRE","C.O.S",IF(LEFT(B502,8)="SCREWFIX","T",IF(LEFT(B502,8)="HOMEBASE","C.O.S",IF(RIGHT(B502,16)="INSURANCE 624740","AI",IF(LEFT(B502,8)="INTEREST","IN",IF(LEFT(B502,14)="PREMIUM CREDIT","CI",IF(LEFT(B502,5)="JACKS","C.O.S",IF(LEFT(B502,14)="J & L MARSHALL","C.O.S",IF(LEFT(B502,15)="JANICE /SYMONDS","CV",IF(LEFT(B502,10)="JOHNSTONES","C.O.S",IF(LEFT(B502,18)="COMMUNITY PUBLICAT","ADV",IF(LEFT(B502,12)="MILFORDS LTD","C.O.S",IF(LEFT(B502,15)="MORRISON PETROL","F",IF(LEFT(B502,11)="PAUL DUFTON","C.O.S",IF(RIGHT(B502,17)="NI DD 991133","NI",IF(MID(B502,13,6)="PETROL","F",IF(LEFT(B502,14)="POUNDSTRETCHER","C.O.S",IF(LEFT(B502,8)="P S AUTO","CM",IF(LEFT(B502,8)="PLUMBASE","C.O.S",IF(LEFT(B502,12)="PLUMB CENTER","C.O.S",IF(LEFT(B502,5)="RIVER","F",IF(LEFT(B502,6)="SPEEDY","C.O.S",IF(LEFT(B502,5)="SHELL","F",IF(LEFT(B502,12)="TESCO PETROL","F",IF(LEFT(B502,24)="DIRECT DEBIT ONE","MO",IF(LEFT(B502,27)="DIRECT DEBIT SE GAS","UT",IF(LEFT(B502,24)="DIRECT DEBIT SOU","UT",IF(LEFT(B502,24)="DIRECT DEBIT CAN","LA",IF(LEFT(B502,25)="DIRECT DEBIT RIAS","CIN",IF(LEFT(B502,24)="DIRECT DEBIT ACE","AI",IF(LEFT(B502,3)="TAX","TX",IF(LEFT(B502,26)="DIRECT DEBIT ABBEY","BI",IF(LEFT(B502,28)="DIRECT DEBIT DENPLAN","PL",IF(LEFT(B502,28)="DIRECT DEBIT NORWICH","AI",IF(LEFT(B502,10)="TOPPS TIES","C.O.S",IF(LEFT(B502,28)="DIRECT DEBIT PHOENIX","LA",IF(LEFT(B502,13)="000000 CREDIT","I",IF(LEFT(B502,17)="WILSONS/QUOTELINE","PLI",IF(LEFT(B502,23)="DIRECT DEBIT BT","P+B",IF(LEFT(B502,28)="DIRECT DEBIT SE TALK","PH",IF(RIGHT(B502,16)="VODAFONE LIMITED","MP",IF(LEFT(B502,8)="VODAFONE","MP",IF(LEFT(B502,6)="WICKES","C.O.S",IF(LEFT(B502,6)="WILKIN","C.O.S",IF(LEFT(B502,9)="WOODLANDS","C.O.S",IF(RIGHT(B502,24)="YORKSHIRE WATER 956664","UT",IF(B502="","","PL"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Here is your formula is a more reader-friendly format:
The easiest option for me would be to give you vba solution since you don't have Excel 2010 which can handle this massive number of IF statements. If that suffices, let me know and I will work on it during lunch. If you want a formula then I need to know if more than one can be valid at the same time and therefore you need this heirarchy to be intact.
Please Login or Register to view this content.
abousetta
Another important question. Where is this formula going (what is the cell reference) and are the columns next to it free to be used as helper columns?
abousetta
hi thanks for the offer of writing the vb code but I would not want to take up your time like that. I have a rudimentary knowledge of vba having biult a database in access some years ago. if you could tell me how (in laymans terms) I will have a go at it myself.
in answer to your second Question there is no free column next to it but there is one two across
example
DATE,DESCRIPTION,IN, OUT, CATEGORY CODE,CATEGORY, EMPTY,TOTALS OF CATEGORY'S ETC
It is a spreadsheet of my bank statements org by month each entry is categorised and then each category is totalled for that month hope this info is useful Jem
You state in your details that you use XL2003. AFAIK, no more than 7 IF's can be nested????
In its simplest form, a vba solution would like something like:
But then you have set the range, etc.Please Login or Register to view this content.
I may also have a formula solution for you but need to know where you want the finla answer. Also I need a few free columns to use as helper cells. Therefore if you could provide a mock workbook, I might be able to put together a working example.
abousetta
Sorry my mistake 2007 updated my details now
Last edited by JEM UK; 11-09-2011 at 01:20 PM.
Sorry... so what's the problem again. 2007 allows 64 levels of nesting. I just put the formula into an xlsx file and no problems have come up. Are you having problems from your end?
abousetta
If you create a table of all the lookup items and the corresponding return items, then perhaps a simple formula like this...
=LOOKUP(9.999999e+307,SEARCH(" "&$X$1:$X$100&" "," "&B502&" "),$Y$1:$Y$100)
where X1:Y100 contains your table of values...(adjust to match your table range)
might work?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I think i will have to give it up as a bad idea I was hoping to insert a call to a function in a spare cell where it could go through a second if(xxx,xxx,If(xxx,xxx)) and the result be sent back. I think it will be easyer to just manually alter the few entrys that are mis categorised. thanks for your help and time it is good of you.
Jem.
as you say 64 levels i have used all 64 so cannot add any more does the 2010 version allow more?
I think 2010 has the same limitation. I have it at home but I believe that 64 is the limit. Usually there are work-arounds because even 64 is a huge number of If statements.
If you are satisfied with the responses and don't want to investigate this further then please mark the thread as Solved.
abousetta
Just so I don't leave you without an answer, here is what the formula would like if it was created in vba:
It assumes that you want the answer in "C502".Please Login or Register to view this content.
Hope this helps.
abousetta
thanks that is very helpful
i found the solution at the end of my nested if i had a catch all bit which returned "PL" I replaced this with a cell reference and in that cell I have placed another nested if() which gives me another 64 levels
example old =IF(LEFT(B2,4)="ALDI","CT",IF(LEFT(B2,7)="ANDREWS","C.O.S",IF(B2="","","PL")))
new =IF(LEFT(B2,4)="ALDI","CT",IF(LEFT(B2,7)="ANDREWS","C.O.S",IF(B2="","",m2)))
anyway thanks again for all your help everyone I would mark this post as solved but can not see the button
Glad that it all worked out. To mark the thread as solved you have to go the first post, click on edit then advanced adn change the dropdown next to the title.
Good luck.
abousetta
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks