Search code examples
excelvbafiltersubtotal

Excel: Calculate difference between cell and cell above in an auto filtered table

I have a table with column A containing incrementing numerical values, and column B being a bunch of names. I need to filter the table according the names, and have column C update with the difference between the value in column A in the current row and the cell above..

For example, I'd like to have something like this which, when filtered according to the Name column, should update the difference like so

I have tried to use SUBTOTAL function in a few different ways but to no avail. Ideally it'd update once the filter in the table is changed. I tried to do this in VBA but so far I've gotten macro that only filters with the hard-coded filter criteria.

Solutions in either excel formulas/python/vba are all welcomed and greatly appreciated!

I apologise in advance if this question isn't up to standards as Im new here :) Thank you in advance!

@JvdV: This is the outcome of me trying to implement your formula, This is after filtering.

Solution

  • REVISED ANSWER

    So after your explenation I have looked into a formula that will give you the difference of the current row B-value minus the B-value of occurance of the A-value before that.

    =IFERROR(B2-LOOKUP(2,1/($A$1:A1=A2),$B$1:B2),0)

    Taking your sample data, it would look like this:

    enter image description here

    Then when you apply the filter, it would look like this:

    enter image description here

    So with this workaround you dont have the correct value when no filter is applied, but in this case I assumed you are interested in the difference when it IS filtered!

    The formula is entered in cell C2 and dragged down.

    EDIT

    If this is not the answer you'r after and you DO need the values when it is not filtered, make use of a UDF like below:

    Public Function LastVisibleCell(CL As Range) As Long

Dim RW As Long, X As Long
RW = CL.Row - 1
On Error GoTo 1

If RW > 1 Then
    For X = RW To 1 Step -1
        If ActiveSheet.Rows(X).EntireRow.Hidden Then
        Else
            LastVisibleCell = Cells(CL.Row, 2).Value - Cells(X, 2).Value
            Exit For
        End If
    Next X
Else
1:    LastVisibleCell = 0
End If

End Function

    Call it from cell C2 like: =LastVisibleCell(A2) and drag down. When you apply your filter, the cells will update.

    Beware, this will take ages to update on large datasets!