Gets minimum subtract of one number of numbers of column

Question

I have 5 numbers in column "A" and 20 numbers in "B". column "C" is the minimum of absolute value subtract each of the numbers of column "A" from column "B". Which is obtained with the following code?

Sub Macro2()
For n = 2 To 6
Range(Cells(n, 3), Cells(n, 3)).FormulaArray = "=MIN(ABS(RC[-2]-R2C2:R21C2))"
Next
End
End Sub

Now how can I calculate the value of column "B" in column"D".

Answer 1

Note that there might not only one solution for b. Mathematically there are 2 solutions possible for b that both result in the same x:

• either =RC[-3]-RC[-1]
• or =RC[-3]+RC[-1]
• (or both of them!)

To illustrate this I changed 2 values in the list of b see the orange ones:

For the last one a = 15 the result for x is x = 1 and here are 2 possible solutions for b: 14 and 16. That means that …

• x = abs(15 - 14) = 1
• x = abs(15 - 16) = 1

so for the result x = 1 there are 2 b possible b = 14 and b = 16. That means you must have a plan what to do if there is not only one result for b.

The formulas I used are

• for b1: =IFNA(VLOOKUP(RC[-3]-RC[-1],R2C2:R21C2,1,FALSE),RC[1])
• for b2: =IFNA(VLOOKUP(RC[-4]+RC[-2],R2C2:R21C2,1,FALSE),RC[-1])
• for b: =IF(RC[-2]=RC[-1],RC[-2],RC[-2] & " or " &RC[-1])

Answer 2

update:

Your formula in C uses an array formula combined with min(). Therefore it's difficult or even impossible to get the b value directly which leads to the result displayed in column c. The macro below is a "quick and dirty" recording of the formulas necessary to get the details to answer your question.

In the columns G2 to K21 you find the array formula

{=ABS(Ax-$B$2:$B$21)}

The result value (which is also in column C) is colored in the first row (G1 to K1) and additionally colored at the location where it occurs.

In column D is the index of the value in column B which leads to the result in C.
In column E you find the value in column B which leads to the result in column C.

vba code

This is the vba code to create the data and formulas which you see in the screenshot attached. (This is code from the Macro Recorder, which has been cleaned up a bit and improved with some comments.)

Option Explicit
Sub Write_Formulas_with_vba()

    'create Array Formulas
    Range("F2:F21").FormulaArray = "=ABS(RC[-5]-R2C2:R21C2)"
    Range("G2:G21").FormulaArray = "=ABS(R[1]C[-6]-R2C2:R21C2)"
    Range("H2:H21").FormulaArray = "=ABS(R[2]C[-7]-R2C2:R21C2)"
    Range("I2:I21").FormulaArray = "=ABS(R[3]C[-8]-R2C2:R21C2)"
    Range("J2:J21").FormulaArray = "=ABS(R[4]C[-9]-R2C2:R21C2)"

    'create MIN formulas
    Range("F1").FormulaR1C1 = "=MIN(R[1]C:R[20]C)"
    Range("G1").FormulaR1C1 = "=MIN(R[1]C:R[20]C)"
    Range("H1").FormulaR1C1 = "=MIN(R[1]C:R[20]C)"
    Range("I1").FormulaR1C1 = "=MIN(R[1]C:R[20]C)"
    Range("J1").FormulaR1C1 = "=MIN(R[1]C:R[20]C)"

    'color relevant cells for this example
    With Union(Range("F1"), Range("F6"), Range("F11")).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    With Union(Range("G1"), Range("G18"), Range("G20")).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    With Union(Range("H1"), Range("H19")).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    With Union(Range("I1"), Range("I21")).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    With Union(Range("J1"), Range("J21")).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

    'add one column
    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    'formulas for 'idx of b' and 'value of b'
    Columns("D:E").ColumnWidth = 16
    Columns("D:K").HorizontalAlignment = xlCenter
    Range("D10").Select
    Range("D2").FormulaR1C1 = "=MATCH(R1C[3],R2C[3]:R21C[3],0)"
    Range("D2").Copy Range("E2")
    Range("E2").Cut Range("D3")
    Range("D3").Copy Range("E3")
    Range("E3").Cut Range("D4")
    Range("D4").Copy Range("E4")
    Range("E4").Cut Range("D5")
    Range("D5").Copy Range("E5")
    Range("E5").Cut Range("D6")

    Range("E2:E6").FormulaR1C1 = "=INDEX(R2C2:R21C2,RC[-1])"

    Range("D1").FormulaR1C1 = "idx_of_b"
    Range("E1").FormulaR1C1 = "value_of_b"

End Sub

'Add sample data of OP in column a, b and c
Sub SampleData()
Dim a_data As Variant
Dim b_data As Variant

    a_data = Array(1, 6, 8, 14, 15)
    b_data = Array(2, 3, 4, 5, 1, 2, 3, 5, 4, 1, 2, 3, 5, 4, 2, 3, 6, 9, 6, 12)

    Range("A:K").HorizontalAlignment = xlCenter
    Range("D1").Select
    Range("A1").Value = "a"
    Range("A2:A6").Value = WorksheetFunction.Transpose(a_data)
    Range("B1").Value = "b"
    Range("B2:B21").Value = WorksheetFunction.Transpose(b_data)
    Range("C1").Value = "c"
End Sub

'macro of the OP
Sub Macro2()
Dim n As Integer
For n = 2 To 6
Range(Cells(n, 3), Cells(n, 3)).FormulaArray = "=MIN(ABS(RC[-2]-R2C2:R21C2))"
Next
End
End Sub

'create array description of sample data column B
Sub read_b_data_to_immediate_window()
Dim xCt As Integer
    Debug.Print "b_data = Array("; Range("B1").Offset(1, 0).Value; ", ";
    For xCt = 2 To 19
        Debug.Print Range("B1").Offset(xCt, 0).Value; ", ";
    Next xCt
    Debug.Print Range("B1").Offset(20, 0).Value; ")"
End Sub