PDA

View Full Version : Solved: Too many nested loops in excel



Hova
06-25-2012, 09:48 AM
I have a code that has way too many nested statements

Basically I have 82 counties and need to break up the counties by district (1-7)
The list I am searching through is over 6000. Any suggestions?

Thanks!

Hova

=IF(C2=1,7,IF(C2=2,1,IF(C2=3,7, IF(C2=2,4,2, IF(C2=2,5,2 ,IF(C2=2,6,3, IF(C2=2,7,2,IF(C2=2,8,2,IF(C2=2,9,1,IF(C2=2,10,1,IF(C2=2,11,3,IF(C2=2,12,6, IF(C2=2,13,1,IF(C2=2,14,2,IF(C2=2,15,3,IF(C2=2,16,7,IF(C2=2,17,2,IF(C2=2,18 ,6,IF(C2=2,19,7,IF(C2=2,20,6,IF(C2=2,21,6,IF(C2=2,22,2,IF(C2=2,23,6,IF(C2=2 ,24,6,IF(C2=2,25,5,IF(C2=2,26,3,IF(C2=2,27,3,IF(C2=2,28,3,IF(C2=2,29,1,IF(C 2=2,30,6,IF(C2=2,31,6,IF(C2=2,32,3,IF(C2=2,33,7,IF(C2=2,34,6,IF(C2=2,35,5,I F(C2=2,36,2,IF(C2=2,37,6,IF(C2=2,38,5,IF(C2=2,39,7,IF(C2=2,40,5,IF(C2=2,41, 1,IF(C2=2,42,2,IF(C2=2,43,7,IF(C2=2,44,1,IF(C2=2,45,5,IF(C2=2,46,7,IF(C2=2, 47,2,IF(C2=2,48,1,IF(C2=2,49,2,IF(C2=2,50,5,IF(C2=2,51,5,IF(C2=2,52,5,IF(C2 =2,53,1,IF(C2=2,54,2,IF(C2=2,55,6,IF(C2=2,57,7,IF(C2=2,58,1,IF(C2=2,59,1,IF (C2=2,60,2,IF(C2=2,61,5,IF(C2=2,62,5,IF(C2=2,63,3,IF(C2=2,64,7,IF(C2=2,65,7 ,IF(C2=2,66,6,IF(C2=2,67,3,IF(C2=2,68,2,IF(C2=2,69,2,IF(C2=2,70,1,IF(C2=2,7 1,1,IF(C2=2,72,2,IF(C2=2,73,1,IF(C2=2,74,7,IF(C2=2,75,3,IF(C2=2,75,3,IF(C2= 2,76,3,IF(C2=2,77,6,IF(C2=2,78,1,IF(C2=2,79,7,IF(C2=2,80,1,IF(C2=2,81,2,IF( C2=2,82,3,0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) ))))))))))))))))))

CodeNinja
06-25-2012, 10:20 AM
Hova,
Looks like you have worse problems than making this a smaller nested if/then. You have too many arguments on most of these if statements starting with the fourth interior statement...

IF(C2=2,4,2, IF(C2=2,5,2 .... and so on

so

There should only be 3 arguments:
1- the condition: C2 =2 ?
2- The value if true: 4
3- the value if false: is this 2 or IF(C2=2,5,2... It cannot be both.

fredlo2010
06-25-2012, 10:29 AM
Hova,

Also I see there quite a few repletion for values. Use OR(), AND() to shorten your formula

Instead of IF(C2=1,7,IF(C2=2,1,IF(C2=3,7, IF(C2=2,4,2, IF(C2=2,5,2 ,.....

use

IF(OR(C2=1,C2=3),7,IF........

Do you get the idea. Its hard to get the idea of what you are saying maybe a sample of the workbook will help. But with such big lists I think it would be better to use Match() and LOOKUPS...

CodeNinja
06-25-2012, 10:39 AM
I would do a table of the results you want and do a vlookup against it...

That would look something like

Sheet2 has cells a1-a82 1 to 82 and b1-b82 with all the corresponding answers

=VLOOKUP(C2,sheet2!$A$1:$B$82,2,FALSE)

Hova
06-25-2012, 11:00 AM
I think Lookup is the way to go from what I hear. I first tried to use OR statements, but that was too long also. I will try the VLOOKUP and let y'all know. I have never used that function before.

Hova
06-25-2012, 11:10 AM
Thanks! works like a charm

Hova

PS Can I say this post has been answered? or Closed?

CodeNinja
06-25-2012, 12:20 PM
Glad this worked for you.

At the top, where it says thread tools, drop down and select "solved"

fredlo2010
06-25-2012, 12:28 PM
Make sure you are using Internet explorer and not Chrome to do it. Trust me it took me a while to figure than one out.

And regarding Vlookup, once you learn it you will be using it all the time. :) I learned it about two years ago and I use it in about 85% of my Excel works

Hova
06-25-2012, 12:38 PM
And that was the problem... I was in Chrome. Thanks guys!