Eliminating Nested If statements

J

jamesa580

New Member
Joined
Aug 28, 2013
Messages
10
I'm trying to create an excel tool that will take the amount of product in process for my company at each stage of our process, and compare it to the amount of product we have need to ship. After this comparison is made, i would like the last column of my sheet to output the step in the process where the last piece for a shipment is located.

The following is an example I have put together:

<colgroup><col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:2304;width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> </colgroup><tbody>
[TD="class: xl65, width: 86"]PRODUCE[/TD]
[TD="class: xl65, width: 64"]On tree[/TD]
[TD="class: xl65, width: 70"]Harvested[/TD]
[TD="class: xl65, width: 64"]Cleaning[/TD]
[TD="class: xl65, width: 64"]Packed[/TD]
[TD="class: xl65, width: 63"]Cust. Qty[/TD]
[TD="class: xl65, width: 87"]RSK LOC[/TD]

[TD="class: xl65"]Apples[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]Harvested[/TD]

[TD="class: xl65"]Bananas[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]PLANT MORE[/TD]

[TD="class: xl65"]Oranges[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]36[/TD]
[TD="class: xl66"]PLANT MORE[/TD]

[TD="class: xl65"]Pears[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]On Tree[/TD]

[TD="class: xl65"]grapes[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]28[/TD]
[TD="class: xl66"]PLANT MORE[/TD]

[TD="class: xl65"]Cherries[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]Cleaning[/TD]

[TD="class: xl65"]Strawberries[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]PLANT MORE
[/TD]

[TD="class: xl65"]Nectarines[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]44[/TD]
[TD="class: xl66"]PLANT MORE
[/TD]

[TD="class: xl65"]Melons[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]On Tree
[/TD]

[TD="class: xl65"]Cantalope[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]38[/TD]
[TD="class: xl66"]PLANT MORE
[/TD]

</tbody>


Where the RSK LOC is what I am looking to output. My current equation is

=IF(E11>=F11, "Packed", IF((E11+D11)>=F11, "Cleaning", IF(SUM(C11:E11)>=F11, "Harvested", IF(SUM(B11:E11)>=F11, "On Tree", "PLANT MORE"))))

However the actual process is much longer than this with over 50 steps. Is there a quicker way to complete this function than my current approach?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Are you willing and able to run VBA code? If so, this should work for you, you'll just have to insert a shape and assign it this macro to be your refresh button. You'll have to hit the refresh button every time you update the numbers so it isn't ideal but it will scale to any size table you use, so long as it follows your format.
Code: 
Sub Order_Status()
    'variables
        Dim i As Integer, j As Integer, Enough As Boolean
        Dim lRow As Integer, lColumn As String
        Dim Orders As Integer
        
    'find size of table
        lColumn = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
        lRow = Range("A" & Rows.Count).End(xlUp).Row
        
    'work
        For i = 2 To lRow   'work its way down the rows
            Orders = Cells(i, lColumn - 1).Value
            Enough = False
            For j = lColumn - 2 To 2 Step -1    'work back through the columns
                If Orders > Cells(i, j).Value Then  'more orders left than at this stage
                    Orders = Orders - Cells(i, j).Value
                Else    'this stage has the last one needed
                    Range("A1").Offset(i - 1, lColumn - 1).Value = Cells(1, j).Value 'print stage in last column
                    Enough = True
                    Exit For    'exits loop to move on to next order
                End If
            Next j
            If Enough = False Then Range("A1").Offset(i - 1, lColumn - 1).Value = "PLANT MORE"
        Next i
End Sub
 
Upvote 0
j_harr said:
Are you willing and able to run VBA code? If so, this should work for you, you'll just have to insert a shape and assign it this macro to be your refresh button. You'll have to hit the refresh button every time you update the numbers so it isn't ideal but it will scale to any size table you use, so long as it follows your format.
Code: 
Sub Order_Status()
    'variables
        Dim i As Integer, j As Integer, Enough As Boolean
        Dim lRow As Integer, lColumn As String
        Dim Orders As Integer
        
    'find size of table
        lColumn = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
        lRow = Range("A" & Rows.Count).End(xlUp).Row
        
    'work
        For i = 2 To lRow   'work its way down the rows
            Orders = Cells(i, lColumn - 1).Value
            Enough = False
            For j = lColumn - 2 To 2 Step -1    'work back through the columns
                If Orders > Cells(i, j).Value Then  'more orders left than at this stage
                    Orders = Orders - Cells(i, j).Value
                Else    'this stage has the last one needed
                    Range("A1").Offset(i - 1, lColumn - 1).Value = Cells(1, j).Value 'print stage in last column
                    Enough = True
                    Exit For    'exits loop to move on to next order
                End If
            Next j
            If Enough = False Then Range("A1").Offset(i - 1, lColumn - 1).Value = "PLANT MORE"
        Next i
End Sub
Click to expand...


I'm not at all familiar with VBA, so I'll have to take a stab at this. Does this have a built in way to skip over a particular column or columns if need be?
 
Upvote 0
jamesa580 said:
I'm not at all familiar with VBA, so I'll have to take a stab at this. Does this have a built in way to skip over a particular column or columns if need be?
Click to expand...

Yes, that can be done.
Code: 
Sub Order_Status()
    'vars
        Dim i As Integer, j As Integer, Enough As Boolean
        Dim lRow As Integer, lColumn As String
        Dim Orders As Integer
        
    'find size of table
        lColumn = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
        lRow = Range("A" & Rows.Count).End(xlUp).Row
        
    'work
        For i = 2 To lRow   'work its way down the rows
            If i <> 3 And i <> 5 Then
                Orders = Cells(i, lColumn - 1).Value
                Enough = False
                For j = lColumn - 2 To 2 Step -1    'work back through the columns
                    If j <> 3 Then
                        If Orders > Cells(i, j).Value Then  'more orders left than at this stage
                            Orders = Orders - Cells(i, j).Value
                        Else    'this stage has the last one needed
                            Range("A1").Offset(i - 1, lColumn - 1).Value = Cells(1, j).Value 'print stage in last column
                            Enough = True
                            Exit For    'exits loop to move on to next order
                        End If
                    End If
                Next j
                If Enough = False Then Range("A1").Offset(i - 1, lColumn - 1).Value = "PLANT MORE"
            End If
        Next i
End Sub

In this updated version, you're interested in these added lines:
Code: 
If i <> 3 And i <> 5 Then
If j <> 3 And j <> 4 Then
It's a bit ugly but it'll work for you. These lines mean that the macro will skip rows 3 and 5 as well as columns 3 and 4 - the variable i corresponds to rows and j corresponds to columns. If you want to adjust which rows and columns to skip, just follow the same pattern, adding in "And i <> X" (or "And j <> X") as needed.
Code: 
If i <> 3 Then
If i <> 3 And i <> 5 Then
If i <> 3 And i <> 5 And i <> 7 Then
If i <> 3 And i <> 5 And i <> 7 And I <> 11 Then
Does that help? Is there anything else? I tried to put things clearly but if something is confusing feel free to ask for a clearer explanation.
 
Upvote 0
Welcome to MrExcel!

Could you share a bit more detail about what you're wanting this tool to do?

A 50 stage process seems very complicated. Do all steps absolutely need to be considered?

Matty
 
Upvote 0
Maybe this array formula (use Ctrl+Shift+Enter and not only Enter):

Code: 
=IFERROR(OFFSET($F$1,,
-MATCH(1,--(SUBTOTAL(9,OFFSET($E2,,,1,-(COLUMN(B$2:E$2)-COLUMN(B$2)+1)))>=$F2),0)),
"PLANT MORE")

Markmzz
 
Upvote 0
Matty said:
Welcome to MrExcel!

Could you share a bit more detail about what you're wanting this tool to do?

A 50 stage process seems very complicated. Do all steps absolutely need to be considered?

Matty
Click to expand...

I'm trying to find where the last piece for a shipment is within our process. And no, every step does not and should not be included, because there are locations where product is destined to be thrown out and I do not want to count that in my calculations.
 
Upvote 0
markmzz said:
Maybe this array formula (use Ctrl+Shift+Enter and not only Enter):

Code: 
=IFERROR(OFFSET($F$1,,
-MATCH(1,--(SUBTOTAL(9,OFFSET($E2,,,1,-(COLUMN(B$2:E$2)-COLUMN(B$2)+1)))>=$F2),0)),
"PLANT MORE")

Markmzz
Click to expand...

Hi Jamesa580,

Did you try my formula?

Markmzz
 
Upvote 0
j_harr said:
Yes, that can be done.
Code: 
Sub Order_Status()
    'vars
        Dim i As Integer, j As Integer, Enough As Boolean
        Dim lRow As Integer, lColumn As String
        Dim Orders As Integer
        
    'find size of table
        lColumn = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
        lRow = Range("A" & Rows.Count).End(xlUp).Row
        
    'work
        For i = 2 To lRow   'work its way down the rows
            If i <> 3 And i <> 5 Then
                Orders = Cells(i, lColumn - 1).Value
                Enough = False
                For j = lColumn - 2 To 2 Step -1    'work back through the columns
                    If j <> 3 Then
                        If Orders > Cells(i, j).Value Then  'more orders left than at this stage
                            Orders = Orders - Cells(i, j).Value
                        Else    'this stage has the last one needed
                            Range("A1").Offset(i - 1, lColumn - 1).Value = Cells(1, j).Value 'print stage in last column
                            Enough = True
                            Exit For    'exits loop to move on to next order
                        End If
                    End If
                Next j
                If Enough = False Then Range("A1").Offset(i - 1, lColumn - 1).Value = "PLANT MORE"
            End If
        Next i
End Sub

In this updated version, you're interested in these added lines:
Code: 
If i <> 3 And i <> 5 Then
If j <> 3 And j <> 4 Then
It's a bit ugly but it'll work for you. These lines mean that the macro will skip rows 3 and 5 as well as columns 3 and 4 - the variable i corresponds to rows and j corresponds to columns. If you want to adjust which rows and columns to skip, just follow the same pattern, adding in "And i <> X" (or "And j <> X") as needed.
Code: 
If i <> 3 Then
If i <> 3 And i <> 5 Then
If i <> 3 And i <> 5 And i <> 7 Then
If i <> 3 And i <> 5 And i <> 7 And I <> 11 Then
Does that help? Is there anything else? I tried to put things clearly but if something is confusing feel free to ask for a clearer explanation.
Click to expand...

Thanks for this, it's been very helpful. I'm not entirely sure how to get it to update the proper column, however. I'd like to be able to maneuver/manipulate it to where I want/different files etc.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
L
  • Question Question
Compile Error when Adding Attachments
Replies
5
Views
180
6StringJazzer
6StringJazzer
LearningByDoing
  • Solved
No correct calculation with Nested IF + AND Function
Replies
4
Views
101
Alex Blakenburg
A
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
357
Anthony47
A
S
  • Solved
Vertical Sparklines Bar Graph linked to cells - can you change Max value, using a Cell Reference ?
2
Replies
19
Views
619
StrawberryDreams
S

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top