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

Question

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.

Answer 1

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:

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

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!

Answer 2

After 3 days of intense (albeit ineffective) Google-ing, I finally came across this answer also on stack overflow.

However, as I'm working on a large set of data (>150,000 rows), the method in the question uses too much memory. Using VBA to paste the formulas into visible cells only does not seem to alleviate the problem.

Sub CopyPasteFormula()
    Dim Ws As Worksheet
    Dim LRow As Long
    Dim PasteRng As Range

    Set Ws = Worksheets("Translated Data")
    Ws.Range("$D$2:$D$200000").AutoFilter Field:=4, Criteria1:="<>-", Operator:=xlFilterValues
    LRow = Ws.Range("D" & Rows.Count).End(xlUp).Row
    Set PasteRng = Ws.Range("A3:A" & LRow).SpecialCells(xlCellTypeVisible)
    Ws.Range("A3").Copy
    PasteRng.PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
End Sub

Above is my macro code to attempt reduce the memory use... Appreciate any feedback!