Finding Exact Difference between Three Numbers

[Rabbit]

Are we looking at permutations or combinations?
Are there repeat numbers?

If combinations and no repeats, another method is to create a dictionary object, populate it, then do a pass on the row and check if the keys x+11 and x+22 exists.

[zmbd]

Exactly Rabbit - something OP has not clearly expressed.
We now have three experts questioning the goal and question!

I do question why checking for key delta-22?
Did I miss something, which is quite possible with how OP has posted - my understanding was that op was looking for delta-11?

[brunoff]

I'm so thankfull to all, twinnyfo, zmbd , Rabbit.
I'm sorry I'm just getting started in VBA, I don´t have any background in programming, forgive me if i didn't illustrate the problem correctly: a code for visual basic/ excel to calculate the exact difference of three numbers (triples) in a extremely large sample, in this example im tryng to find out three numbers (x,y,z) whose difference is 11 , in other words z-y=11 , y-x=11. I'm trying to find out which are, and how many are, triples of numbers with the precise difference of 11 within each row/line in the excel spreedsheet.

That's im tryng to mean firstly "any three cells" (in the same row/line) or any three numbers (x,y,z) whose difference is 11 within each row/line in the excel spreedsheet. Any combination of 3 numbers displayed in the input data that meets the condition difference is 11.
Thank to all for kindness.

[zmbd]

You are still not making any sense here...
Show us the math as I have done in step 5 of my post.

[Rabbit]

@zmbd, the reasoning is that they're trying to find triplets (a,b,c) where if a < b < c then a+11 = b and b+11 = c.

So for any given x, if x+11 and x+22 exists, then that means x < x+11 < x+22, with each one 11 units apart from the next.

The reason I don't check the ordering is that if you load the entire row into the dictionary and then you traverse the row to check the keys in the dictionary, then at some point, x will be the start of the sequence.

[brunoff]

What i'm trying to do is to find "any three cells" (in the same row/line) or any three numbers (x,y,z) whose difference is 11 within each row/line in the excel spreedsheet. Any combination of 3 numbers displayed in the input data that meets the condition difference is 11.

I'm struggling with some math, but i give an example what i'm tryng to explain, in the table bellow we can see at left (first 6 rows) the input data, and at the right side the results (x,y,z) that meet the condition of any numbers with the precise difference of 11 within each row/line in the excel spreedsheet.

1	13	16	21	22	24	none	
8	11	22	33	44	55	(11,22,33);(22,33,44);	(33,44,55)

8	19	21	25	28	30	(8,19,30)
24	26	27	29	35	46	(24,35,46)
8	17	20	28	32	39	(17,28,39)
10	27	38	40	49	55	(27,38,49)
3	14	20	25	40	48	(3,14,25)
7	9	18	26	29	51	(7,18,29)
2	12	16	23	34	65	(12,23,34)
4	15	19	22	26	68	(4,15,26)
5	11	16	27	58	75	(5,16,27)
6	14	17	20	22	28	(6,17,28)
36	47	58	70	85	91	(36,47,58)
26	27	31	37	48	82	(26,37,48)
15	19	21	26	28	37	(15,26,37)
9	51	62	65	69	73	(51,62,73)
14	42	43	45	53	64	(42,53,64)
1	14	45	56	60	67	(45,56,67)
31	32	54	56	65	76	(54,65,76)

we are looking at any combinations that meets the condition difference between any 3 numbers (in the same row/line) is 11.

I'm eager to receive any help. Thanks to all

[zmbd]

@ Rabbit - you are a genius!

@ brunoff
If you had taken this last post and made it your first post you might already have had your answer.

First, Triple is a very specific mathematical and computational terminology
+ http://mathworld.wolfr am.com/PythagoreanTrip le.html
+ https://en.wikipedia.or g/wiki/Tuple#Etymology

So you're not really looking for mathematical triples; however, the output you've chosen mimics the output commonly used to denote Pythagorean triples which initially caused me confusion - when in fact, you are more interested in the pairing.

Now that we have that cleared up lets take just one row of your data...
8 19 21 25 28 30 (8,19,30)
and you're looking at the COMBINATION-without replication (not a permutation that is ((8-19) != (19-8)) ) of all of the values and only taking the groupings with a delta-11 - ignoring the replicate paring (8,8) so we gave CMB(6,2)=15 ordered pairs such that:
abs(8-19); abs(8-21); abs(8-25); abs(8-28); abs(8-30);
abs(19-21); abs(19-25); 1abs(9-28); abs(19-30);
abs(21-25); abs(21-28); abs(21-30);
abs(25-28); abs(25-30);
abs(28-30);
taking the numbers from each paring delta-11, 8,19,19,30, and only reporting each value once so that we have 8,19,30.

YOU CONFUSE THE SITUATION when you make the statement

the condition of any numbers with the precise difference of 11 within each row/line in the excel spreedsheet.

and then show the result as (8,19,30) because taking any three of those numbers will provide results where the difference does not equal 11 (for example abs(8-30) = 22).
However, I do see (with Rabbit's help!) that what you have is (a,b,c) such that (a,b=a+11,c=a+2 2); thus, you should consider marking Rabbit's answer as the solution (Post#16) the dictionary attack is brilliant.

[brunoff]

ok. Im so grateful to all, zmbd , twinnyfo, Rabbit. for your help. Forgive me , if i didn't represent , at first, the problem properly, because most of time, i've struggled with mathematics.
Please, Would you fix the code bellow in order to work out correctly for a extremelly huge data such as 5000 rows of data? Because, I'm beginner in VBA, and I've got almost nothing in VBA , only the basics of the basic simple concepts.

    Sub Listtriples04()
            Dim lngRow  As Long 
            Dim intCol  As Integer
     
            lngRow = 1
            Do While Not Cells(lngRow, 1) = ""
                For intCol = 1 To 4 
                    If Abs(Cells(lngRow, intCol) - _
                        Cells(lngRow, intCol + 1)) = 11 Then
                        If Abs(Cells(lngRow, intCol + 1) - _
                            Cells(lngRow, intCol + 2)) = 11 Then
                            If Abs(Cells(lngRow, intCol) - _
                        Cells(lngRow, intCol + 2)) = 11 Then
     
                                                    Cells(lngRow, intCol + 7) = _
                                "(" & Cells(lngRow, intCol) & ", " & _
                                Cells(lngRow, intCol + 1) & ", " & _
                                Cells(lngRow, intCol + 2) & ")"
                        End If
                    End If
                End If
     
     
                Next intCol
                lngRow = lngRow + 1
            Loop
        End Sub

So that it calculates the results accurately by using the input data :

 
        input data                         the desired results
1    13    16    21    22    24    none    
8    11    22    33    44    55    (11,22,33);(22,33,44);    (33,44,55)
 
8    19    21    25    28    30    (8,19,30)
24    26    27    29    35    46    (24,35,46)
8    17    20    28    32    39    (17,28,39)
10    27    38    40    49    55    (27,38,49)
3    14    20    25    40    48    (3,14,25)
7    9    18    26    29    51    (7,18,29)
2    12    16    23    34    65    (12,23,34)
4    15    19    22    26    68    (4,15,26)
5    11    16    27    58    75    (5,16,27)
6    14    17    20    22    28    (6,17,28)
36    47    58    70    85    91    (36,47,58)
26    27    31    37    48    82    (26,37,48)
15    19    21    26    28    37    (15,26,37)
9    51    62    65    69    73    (51,62,73)
14    42    43    45    53    64    (42,53,64)
1    14    45    56    60    67    (45,56,67)
31    32    54    56    65    76    (54,65,76)

I hope to hear from you

[Rabbit]

Thanks @zmbd, while the dictionary method won't necessarily perform more efficiently (I'm not sure how the dictionary searches its keys for example), it does make the code easier to grasp.

@brunoff, the first step you should take if you want to use the dictionary approach is to create a dictionary object and then populate in a loop through the rows. Currently, your code has no reference to a dictionary object so I don't know if you decided not to use a dictionary or if you're trying a different method.

[SioSio]

One less loop if compared by string

Sub Listtriples04()
    Dim lngRow  As Long
    Dim intCol  As Integer
    Dim N(3) As Integer
    Dim SN(3) As String
    Dim Str As String
    Dim i As Integer
    For lngRow = 1 To Range("A1").End(xlDown).Row
        Str = ""
        For intCol = 1 To Cells(lngRow, 1).End(xlToRight).Column
            Str = Str & " " & Trim(Cells(lngRow, intCol).Value)
        Next
        i = Cells(lngRow, 1).End(xlToRight).Column
        For intCol = 1 To 6 'Cells(lngRow, 1).End(xlToRight).Column
            N(0) = CInt(Cells(lngRow, intCol).Value)
            N(1) = N(0) + 11
            N(2) = N(1) + 11
            SN(0) = " " & CStr(N(0))
            SN(1) = " " & CStr(N(1))
            SN(2) = " " & CStr(N(2))
            If InStr(Str, N(1)) > 0 And InStr(Str, N(2)) > 0 Then
                i = i + 1
                Cells(lngRow, i).Value = "(" & SN(0) & "," & SN(1) & "," & SN(2) & ")"
            End If
        Next
    Next
End Sub