-1

The problem I am having is I only want to look at Order number(Column A) if they have more than 4 value. When they have 4 values I want to only look at the last 4 values in that order. In column D I wrote 1 and 0 to correspond with what I can filter with.

But I do not know how I can write a formula in Excel to do that from Column B.

enter image description here

0

1 Answer 1

0

On VBA insert a module and on the module this code:

Function OverFour(Target As Range, top_value As Integer) As Integer
    thisrow = Target.Row 'row of the order cell
    thiscolumn = Target.Column 'column of the order cell
    thisvalue = Target.value 'value of the order cell
    thisorder = Cells(thisrow, thiscolumn)
    notSame = True
    newrow = thisrow
    finalvalue = 0
    'the following part checks how many rows are on the block of repeated values
    While notSame = True 
        newrow = newrow + 1
        k = Cells(newrow, thiscolumn)
        j = Cells(newrow, thiscolumn - 1)
        If k = 1 Or k = "" Then
            newrow = newrow - 1
            notSame = False
        End If
    Wend
    'if the number of rows is larger than top_value
    'and this cell is in the range from top_value to the end of the block
    'then output an 1
    If Cells(newrow, thiscolumn) >= top_value Then
        If newrow - thisrow <= top_value - 1 Then
            finalvalue = 1
        End If
    End If
    OverFour = finalvalue
End Function

Now, on cell D2 insert:

=OverFour(B2,4)

And copy (drag down) to the other cells in column D.

The function uses two references:

  • The cell of the order, B2 in this case.
  • The desired top value, 4 in this example.

The good thing about it is that it could work if you decide to mark the top 2, 3, 4, 5 or whatever you want.

4
  • Would you mind adding some comments so I can understand how the program is working?
    – user38498
    Jun 18, 2015 at 14:57
  • @user38498 Added some basic comments to the VBA code
    – jcbermu
    Jun 18, 2015 at 15:09
  • Where does the 'k' and the 'j' come from?
    – user38498
    Jun 18, 2015 at 16:10
  • The algorithm goes down from the row to find the boundaries of the block. It goes to the next row, extracts value from columns B k and Aj. When k=1it means it got on the next block the goes one step back newrow=newrow-1.
    – jcbermu
    Jun 19, 2015 at 8:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .