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)

 

[mramono]

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.

Ok, I have placed the code on Module 1 but the error still comes but this time it gives me the option to debug. when i select debug it highlights this line with yellow:

diff = Cells(rows, cols(i) + cloop) - Cells(rows, cols(j) + cloop)

 

[mramono]

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.

Hi Joe4

I think it was my mistake, I realized my data were not in proper rows as defined earlier. I then corrected the mistake to make sure data entry starts at row 2. and the the error did not appear. sorry about this. let me check again

 

[mramono]

Hi Joe4

I think it was my mistake, I realized my data were not in proper rows as defined earlier. I then corrected the mistake to make sure data entry starts at row 2. and the the error did not appear. sorry about this. let me check again

Hi Joe4

Ok, I see how it works now.

We cannot compare B & AA because those two results came from the same laboratory (just the same lab being asked to repeat)

We also cannot compare N & AM because its also the same laboratory (just the same lab being asked to repeat)

I guess what i hope you can assist is to do the same comparison as the code you provide is doing but excluding the comparison of B & AA as well as N & AM

 

[Joe4]

We cannot compare B & AA because those two results came from the same laboratory (just the same lab being asked to repeat)

We also cannot compare N & AM because its also the same laboratory (just the same lab being asked to repeat)

With each reply, it seems this issue is getting more and more complex!

Are these values always exactly the same?
If so, is it enough to say exclude zero differences, or might there actually be zero differences that you want to display?

 

[mramono]

With each reply, it seems this issue is getting more and more complex!

Are these values always exactly the same?
If so, is it enough to say exclude zero differences, or might there actually be zero differences that you want to display?

I also beginning to appreciate the complexity, but also my learning curve is exponential.

To answer your question, its actually coincidental that the repeats were the same, it only means the repeat were not actually done hence the first results were used as repeats. What actually happens though in most cases is that if the laboratory make a repeat analysis, the repeat is usually not far from their original figure. This is because if the repeat is significantly different then the lab has some fundamental problem in their process as opposed to normal random error. Yes the those figures are hardly the same but they are usually very close to one another. But because they come from the same lab we don't want to use them in the comparison.

I was getting so warm to the solution, I'm crossing my fingers that you can still come up with an ingenuous solution like as it is, you have already pass way beyond my wildest dreams.

 

[Joe4]

I am on my way out in a few minutes, so I probably won't be able to take a crack at it until later tonight.
I have an idea, just need to try to work it in.

 

[mramono]

I am on my way out in a few minutes, so I probably won't be able to take a crack at it until later tonight.
I have an idea, just need to try to work it in.

Thank you,

i'm also going to sleep, it's mid night here in South Africa.

i'll check out what you have first thing in the morning.

thank you so much.

 

[Joe4]

OK, see if this modification works:

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)) [COLOR=#ff0000]_
                        And (cols(i) + cols(j) <> 29) And (cols(i) + cols(j) <> 53)[/COLOR] 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

 

[mramono]

OK, see if this modification works:

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)) [COLOR=#ff0000]_
                        And (cols(i) + cols(j) <> 29) And (cols(i) + cols(j) <> 53)[/COLOR] 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

Hi Joe4


Perfect, it looks like the code is pulling the right pair of cells. You made my day and i can see the light. The extra line of code that you added to enhance the comparison seem to have done the trick. I sort of see and understand the integers used in the code, but the 29 and 53 eludes me, I hope when i apply this code on the bigger spreadsheet these two numbers won't give me problems. E.g I can see that 64 means the answers are written 64th columns relative to the respective element.


I hope you still have patience for me,

1) Is it possible to instead of returning the answers as say (N2-AZ2), we can return the (N2+AZ2)/2 (and similarly for all respective answers) - I tried to look for myself where the code was actually returning the difference and hoped to modify that part but i couldn't figure out.
2) Also from column BY (col 77) I would like to return the following answers still based on smallest difference ABS(N2-AZ2)/min(N2,AZ2) -- (i'm not sure if is a question of repeating the same code but on module 2 of the VBA also in such a way that we return ABS(N2-AZ2)/min(N2,AZ2) instead of (N2-AZ2).

 

[Joe4]

but the 29 and 53 eludes me

Columns "B" & "AA" - column B is column number 2, column "AA" is column number 27.
2 + 27 = 29
So I am saying is the two column numbers we are comparing add up to 29, do not include it.
If you add up your other two column numbers in the same way, you will see those add up to 53.

1) Is it possible to instead of returning the answers as say (N2-AZ2), we can return the (N2+AZ2)/2 (and similarly for all respective answers) - I tried to look for myself where the code was actually returning the difference and hoped to modify that part but i couldn't figure out.

Try changing this line of code:

frml = "=" & Cells(rows, cols(i) + cloop).Address(0, 0) & "-" & Cells(rows, cols(j) + cloop).Address(0, 0)

to

frml = "=(" & Cells(rows, cols(i) + cloop).Address(0, 0) & "-" & Cells(rows, cols(j) + cloop).Address(0, 0) & ")/2"

2) Also from column BY (col 77) I would like to return the following answers still based on smallest difference ABS(N2-AZ2)/min(N2,AZ2) -- (i'm not sure if is a question of repeating the same code but on module 2 of the VBA also in such a way that we return ABS(N2-AZ2)/min(N2,AZ2) instead of (N2-AZ2).

That's looks like a whole another ball of wax...