Formula that can determine the pair of values that gives the smallest difference

[mramono]

Hi

I have values in five different cells scattered across my excel sheet and I want to find the pair of those values that gives the smallest difference.

Assuming I have the value in a1, b1, c1, d1 and e1

Then I test the difference as follows

E.g. a1-b1 = 0.50
a1-d1 = 0.53
b1-c1 = 0.49
c1-d1 = 0.31
a1-e1 = 0.48
b1-e1 = 0.45
c1-e1 = 0.32
d1-e1 = 0.53

looking at the answers in column a2, such that a2 equals the formula that gives the smallest answer (In this case the formula in a2 must select c1-d1 because the answer is 0.31 which is the smallest of all the answers. The point is if the values in a1,b1,c1,d1,e1 changes respectively, the formula in a2 will be different as the smallest answer would have changed also for the pairs of values tested.

I will appreciate suggestion how to figure out such an excel function (in a2 for instance)

 

[Gerald Higgins]

OK well my solution clearly doesn't return the address of the cells the produce the minimum result, and I agree that doing that in a single formula would be challenging.

If I had to deal with this problem myself, I would use helper columns to work out the difference for each of the 8 combinations.
That way you could see, not only which combination of tests give the minimum results, but also how close the other combinations are to the minimum.
That might be useful information.

 

[Joe4]

If I had to deal with this problem myself, I would use helper columns to work out the difference for each of the 8 combinations.

If they want a non-VBA approach, I would DEFINITELY recommend that.

However, if they are able to use VBA, you usually don't need helper columns.

 

[mramono]

So, you mean it will always be columns P, AB, AO, BA, and BN? Is that what you are saying?

Is this going to run on just one row at a time, and do you want the macro to run against a bunch of rows at once?
How should we communicate to the macro which rows to run on (we can do the selected rows, input for rows, or hard-code rows)?

Yes, colums will always be P, AB, AO, BA, BN and answer in BZ (but the columns next to them should also do the same). Meaning CA will work on Q, AC, AP, BB, and BO. That means answer in CA, which is to the right of BZ should use respective columns to the right of P, AB, AO, BA, BN respectively (Q, AC, AP, BB, and BO). And that should be the case for answers in BP up to BX respectively.

I'll like it to run a bunch of rows at once, I think hard-code rows will work fine.

 

[Joe4]

Hmmm, your columns do not seem to be evenly spaced out.

Column P = column 16
Column AB = column 28
Column AO = column 41
Column BA = column 53
Column BN = column 66

This would be much easier if they were all evenly spaced out (some as 12 apart, some are 13 apart).

I think this can get really messy, even in VBA.
Please explicitly tell us ALL the different sets of columns that need to be compared, and all the rows you want included in this.

 

[mramono]

Hmmm, your columns do not seem to be evenly spaced out.

Column P = column 16
Column AB = column 28
Column AO = column 41
Column BA = column 53
Column BN = column 66

This would be much easier if they were all evenly spaced out (some as 12 apart, some are 13 apart).

I think this can get really messy, even in VBA.
Please explicitly tell us ALL the different sets of columns that need to be compared, and all the rows you want included in this.

I have put the data more or less as it appears on my spreadsheet, from column B to BW

Column B and AA are repeats, column N and AM are also repeats from a different laboratory, Column AZ is umpire lab. The final results are in column BL. With the data below then:

BL1720 = either (B1720+N1720)/2 or (AA1720+AM1720)/2 or (B1720+AM1720)/2 or (N1720+AA1720)/2 or (B1720+AZ1720)/2 or (N1720+AZ1720)/2 or (AA1720+AZ1720)/2 or (AM1720+AZ1720)/2

and the choice of the answer that appears in column BL is based on which of the following gives the smallest answer or difference {(B1720-N1720)/min(B1720,N1720), (AA1720-AM1720)/min(AA1720,AM1720), (B1720-AM1720)/min(B1720,AM1720), (N1720-AA1720)/min(N1720,AA1720), (B1720-AZ1720)/min(B1720,AZ1720), (N1720-AZ1720)/min(N1720,AZ1720), (AA1720-AZ1720)/min(AA1720,AZ1720), (AM1720-AZ1720)/min(AM1720,AZ1720)}

B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR BS BT BU BV BW 1 Pt Pd Rh Au 3PGM Ru Ir Cu Ni Cr2O3 Co Pt Pd Rh Au 3PGM Ru Ir Cu Ni Cr2O3 S Co Pt Pd Rh Au 3PGM Ru Ir Cu Ni Cr2O3 Co Pt Pd Rh Au 3PGM Ru Ir Cu Ni Cr2O3 S Co Pt Pd Rh Au Ru Ir Cu Ni Cr2O3 S Co Pt Pd Rh Au 3PGM Ru Ir Cu Ni Cr2O3 S Co 2 61.80 26.35 8.06 2.35 98.56 12.99 4.00 0.73 1.57 0.72 48.00 21.50 7.34 1.39 78.23 9.75 2.45 0.77 2.10 0.55 61.80 26.35 8.06 2.35 98.56 12.99 4.00 0.73 1.57 0.72 42.70 21.90 5.98 2.45 74.39 10.50 3.54 0.97 2.53 0.55 47.46 22.34 7.01 1.39 10.45 2.49 0.62 1.54 47.73 21.92 7.18 1.39 78.21 10.10 2.47 0.75 1.56 0.64 3 61.80 26.35 8.06 2.35 98.56 12.99 4.00 0.73 1.57 0.72 48.00 21.50 7.34 1.39 78.23 9.75 2.45 0.77 2.10 0.55 61.80 26.35 8.06 2.35 98.56 12.99 4.00 0.73 1.57 0.72 42.70 21.90 5.98 2.45 74.39 10.50 3.54 0.97 2.53 0.55 47.46 22.34 7.01 1.39 10.45 2.49 0.62 1.54 47.73 21.92 7.18 1.39 78.21 10.10 2.47 0.75 1.56 0.64 4 61.80 26.35 8.06 2.35 98.56 12.99 4.00 0.73 1.57 0.72 48.00 21.50 7.34 1.39 78.23 9.75 2.45 0.77 2.10 0.55 61.80 26.35 8.06 2.35 98.56 12.99 4.00 0.73 1.57 0.72 42.70 21.90 5.98 2.45 74.39 10.50 3.54 0.97 2.53 0.55 47.46 22.34 7.01 1.39 10.45 2.49 0.62 1.54 47.73 21.92 7.18 1.39 78.21 10.10 2.47 0.75 1.56 0.64 5 44.91 20.10 6.27 0.76 72.04 9.10 2.77 0.50 1.23 1.14 47.60 22.00 6.57 0.76 76.93 10.60 2.65 0.62 1.74 0.82 44.91 20.10 6.27 0.76 72.04 9.10 2.77 0.50 1.23 1.14 48.90 22.60 7.19 0.80 79.49 10.60 2.60 1.32 2.25 0.82 22.54 7.15 10.05 0.46 1.18 46.26 22.27 6.42 0.76 75.70 10.33 2.71 0.48 1.20 0.98 <tbody> </tbody>

<tbody>
</tbody>

The answers in BL are the correct answers i'm looking for, but i had to do manual and rigorous checks to get them. The idea is to get the same answers automatically.

 

[Joe4]

No, I am looking for specifics.

When I say different sets of columns, here is what I mean.
- You said to compare columns P, AB, AO, BA, and BN. That is ONE set. What are all the other sets (what exactly are all the other 5 columns sets we want to compare)? Just list out all the other 5 columns sets.

Regarding all the rows we need to compare, how is the macro to know which rows?
In your example, you used row 1720. What are all the other rows?
Will it always be the same?
- If "Yes", what exactly are those rows?
- If "No", how can the code figure out which rows to run against?

 

[mramono]

No, I am looking for specifics.

When I say different sets of columns, here is what I mean.
- You said to compare columns P, AB, AO, BA, and BN. That is ONE set. What are all the other sets (what exactly are all the other 5 columns sets we want to compare)? Just list out all the other 5 columns sets.

Regarding all the rows we need to compare, how is the macro to know which rows?
In your example, you used row 1720. What are all the other rows?
Will it always be the same?
- If "Yes", what exactly are those rows?
- If "No", how can the code figure out which rows to run against?

For simplicity so that you can be able to use the data I provided above, i have shifted the data such that: Column P is now B, AB is now N, AO is now AA, column BA is now AM, column BN is now AZ, and column BZ is now BL.

below is how set of columns are arranged.

I forgot to change the row number, in my original example the row number was 1720 but with the shifting now the row is row 2

B	C	D	E	G	H	I	J
N	O	P	Q	S	T	U	V
AA	AB	AC	AD	AF	AG	AH	AI
AM	AN	AO	AP	AR	AS	AT	AU
AZ	BA	BB	BC	BD	BE	BF	BG
BL	BM	BN	BO	BQ	BR	BS	BT (ANS)

<colgroup><col span="4"><col><col><col span="2"></colgroup><tbody>
</tbody>

The formula in row 2, row 3, row 4 and row 5 are exactly the same. The idea is once i have formulas populated in say in row 2 I can just copy the formula down to row 5.
BL, BM, BN, BO, BR, BS and BT contains the answer for each set respectively.

 

[Joe4]

Try this:

Sub MyMinDiff()

    Dim diff As Double
    Dim minDiff As Double
    Dim frml As String
    Dim cols As Variant
    Dim i As Integer
    Dim j As Integer
    Dim rows As Long
    Dim cloop As Long
    
    Application.ScreenUpdating = False
    
'   Set array of initial columns (numerical equivalents of B, N, AA, AL, and AZ)
    cols = Array(2, 14, 27, 39, 52)
    
'   Loop through rows 2 through 5
    For rows = 2 To 5
'       Run through 8 different sets per row
        For cloop = 0 To 7
            minDiff = 999999999
            For i = LBound(cols) To UBound(cols)
                For j = LBound(cols) To UBound(cols)
                    If (i <> j) And (Cells(rows, cols(i) + cloop) >= Cells(rows, cols(j) + cloop)) Then
                        diff = Cells(rows, cols(i) + cloop) - Cells(rows, cols(j) + cloop)
                        If diff < minDiff Then
                            minDiff = diff
                            frml = "=" & Cells(rows, cols(i) + cloop).Address(0, 0) & "-" & Cells(rows, cols(j) + cloop).Address(0, 0)
                        End If
                    End If
                Next j
            Next i
            Cells(rows, cloop + 64).Formula = frml
        Next cloop
    Next rows

    Application.ScreenUpdating = True
    
    MsgBox "Process complete!"

End Sub

Note that the calculations are being done in the VBA, and the formula returning the smallest values is being returned.
So to expand this to more rows, you CANNOT simply just copy the formulas it creates down. You need to edit the VBA code (change the 5 to the new last row).

 

[mramono]

Try this:

Sub MyMinDiff()

    Dim diff As Double
    Dim minDiff As Double
    Dim frml As String
    Dim cols As Variant
    Dim i As Integer
    Dim j As Integer
    Dim rows As Long
    Dim cloop As Long
    
    Application.ScreenUpdating = False
    
'   Set array of initial columns (numerical equivalents of B, N, AA, AL, and AZ)
    cols = Array(2, 14, 27, 39, 52)
    
'   Loop through rows 2 through 5
    For rows = 2 To 5
'       Run through 8 different sets per row
        For cloop = 0 To 7
            minDiff = 999999999
            For i = LBound(cols) To UBound(cols)
                For j = LBound(cols) To UBound(cols)
                    If (i <> j) And (Cells(rows, cols(i) + cloop) >= Cells(rows, cols(j) + cloop)) Then
                        diff = Cells(rows, cols(i) + cloop) - Cells(rows, cols(j) + cloop)
                        If diff < minDiff Then
                            minDiff = diff
                            frml = "=" & Cells(rows, cols(i) + cloop).Address(0, 0) & "-" & Cells(rows, cols(j) + cloop).Address(0, 0)
                        End If
                    End If
                Next j
            Next i
            Cells(rows, cloop + 64).Formula = frml
        Next cloop
    Next rows

    Application.ScreenUpdating = True
    
    MsgBox "Process complete!"

End Sub

Note that the calculations are being done in the VBA, and the formula returning the smallest values is being returned.
So to expand this to more rows, you CANNOT simply just copy the formulas it creates down. You need to edit the VBA code (change the 5 to the new last row).

Hi Joe4,

Thanks for your help still.

I may be doing something wrong, I just copy and pasted the code in the VB Microsoft Excel Objects>Thisworkbook, saved and ran the macro. I'm getting and error message "Run-time error 13"

Unfortunately with my handicapped understanding and usage of VB, I have no idea how to resolve the error or what is that i should do to avoid it.

 

[Joe4]

No, do not put the code in the "ThisWorkbook" module.

In the VBAProject window on the left-side of the VBA Editor, right-click on your file name, select Insert -> Module, and it should insert a Module named "Module1". Place the code in that module.