Column Subtraction

**yarks16** · 06-12-2019, 09:33 AM

Good morning,

I have a column that populates from a formula, some are blank and some have values.. The column has no values from A1 thru A45. A46, has a value. and from A49 thru A75 the cells are empty. While cell A76 has a value. I need to tell my worksheet to subtract the two values, A46-A76. However, the column has about 22,000 values at different cells in the column. The formula needs to be able to "skip" the blank cells and subtract the two closest cells with value. The next subtraction could look like, A109-A127, A143-A166 the cells with values are not proportionally incremented. The column is too large to go through and put a subtraction formula for each instance. Any help would be appreciates. Thanks.

**avk** · 06-12-2019, 10:31 AM

Your data "A1" to "A166"
In "B1"

Formula:

=IFERROR(INDEX($A$1:$A$166,SMALL(INDEX(NOT(ISBLANK($A$1:$A$166))*ROW($A$1:$A$166),0),COUNTBLANK($A$1:$A$166)+ROW(A1))),"")

ensure pressing shift+ctrl+enter
copy paste down till blank.

**modytrane** · 06-12-2019, 11:11 AM

See attached file.
Click the "Run" button and column B and C will list cell references for two consecutive non-zero values in column A.
Column d will have the difference.

here's the simple macro:

Sub subtraction()
Dim lr, x, y As Long
Dim value1, value2 As Long

With Worksheets("Sheet1")
lr = .Range("A" & Rows.Count).End(xlUp).Row
End With
y = 1
With ActiveSheet
For x = 1 To lr
    If .Range("A" & x).Value > 0 Then
        If value1 > 0 Then
        .Range("B" & y).Value = "A" & x
        value2 = .Range("A" & x).Value
        Else: value1 = .Range("A" & x).Value
        .Range("C" & y).Value = "A" & x
        End If
        If value2 > 0 Then
        .Range("D" & y).Value = value2 - value1
        y = y + 1
        value1 = 0
        value2 = 0
        End If
    End If
Next x
End With
    
    




End Sub

**yarks16** · 06-12-2019, 11:39 AM

I implemented this into my worksheet with all of the data and it threw me an error. Of the entirety of the worksheet the data that needs subtracting is in column N, and there are 22500 rows.