Finding Exact Difference between Three Numbers

[brunoff]

I already created an account and verified my email adress, but when i tried to post my first doubt on ask a question. it displays a message that my post couldn't be submitted.

I tried to post the following below: Im trying 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.

Can someone give some aide???

I tried the following code:

Sub Listtriples3()
Dim myC As Range
Dim myR As Range
Dim myT As Range
Dim Col As Integer
Dim firstCol As Integer
Dim Diff As Integer
Dim Deff As Integer
 
Diff = 11
Deff = 11
 
Set myT = ActiveCell.CurrentRegion
firstCol = Cells(myT.Cells(1).Row, _
                 Columns.Count).End(xlToLeft)(1, 3).Column
For Each myR In myT.Rows
    Col = firstCol
    For Each myC In myR.Cells
        If myC.Value <> "" Then
            If Not IsError(Application.Match(myC.Value + Diff, _
                                             myR.Cells, False)) Then
                Cells(myC.Row, Col).Value = "(" & myC.Value & ", " & _
                                            myC.Value + Diff & ", " & _
                                            myC.Value + Diff + Deff & ")"
                Col = Col + 1
            End If
        End If
    Next myC
Next myR
End Sub
 
 
 

I tried 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.

But when i run the code above , it gave some more numbers that are not primordially displayed in each line

For example, you can run my example in excel and see what im tryng to explain.

I am using MS Excel-2007. How can it be done in Visual Basic in MS Excel-2007?

Can someone give a hand?

I'm looking forward to receiving good news.

Many Thanks

Please can someone help me with this problem ? many thanks

[twinnyfo]

Brunoff,

Welcome to Bytes!

Could you explain in just a bit more detail exactly what it is you are trying to do, as it is slightly unclear from both your post and your code what you are after.

I understand this: you are looking for three values which all have a difference of 11. Your description implies z > y > x—is this always the case? What if y-z = 11? Will this situation meet your requirement?

Also, you don’t describe explicitly if you are looking at three consecutive cells in Excel or if you are simply looking for “any three values that meet these criteria”?

Finally, is this a finitely sized Excel Spreadsheet? What I am asking is, is this a randomly generated spreadsheet that could have 12 columns and 42 rows one day and 17 columns and 81 rows the next? Or does it always have the same number of rows and columns? This might help add some simplicity to the problem, but either way, it would be good to know how this sheet comes to you for this purpose.

Let us know some more details, and we’ll see what we can do to hepp!

[brunoff]

my reply for the first question: Your description implies z > y > x—is this always the case? my answer is yes. One example for clarifying this point: in the 2nd row/line of first1st.zip there is
8 11 22 33 44 55 in the first1st.zip when you must first SELECT THE AREA OF NUMBERS FOR CALCULATION
and the press CALC it will generate the following:
(11, 22, 33) (22, 33, 44) (33, 44, 55) and (44, 55, 66) . These three results are correct (11, 22, 33) (22, 33, 44) (33, 44, 55) but this last one here (44, 55, 66) it's wrong because the number 66 doesn't belong to the original input data 8 11 22 33 44 55

my reply for the second question (What if y-z = 11? Will this situation meet your requirement?): It will, because for each row/line im tryng to find any 3 numbers display as (x,y,z) that meet the condition that difference between them is 11. The result should be treated as only one count for (11, 22, 33), because in the 2nd row/line of first1st.zip there is one combination that meets the condition difference between 3 any numbers is 11 33 -22 =11 , 22-11=11 so this should be counted only once . For the display , this form form (11, 22, 33) should be prefereable than other forms such as (33,22,11) or (22,11,33).

my reply for the third question (you don’t describe explicitly if you are looking at three consecutive cells in Excel or if you are simply looking for “any three values that meet these criteria”?) :
I'm tryng to find out any three numbers displayed as ( , , ) that meets the condition difference between 3 any of these 3 numbers is 11 , for each row/line.

my reply for the forth question (is this a finitely sized Excel Spreadsheet? What I am asking is, is this a randomly generated spreadsheet that could have 12 columns and 42 rows one day and 17 columns and 81 rows the next? Or does it always have the same number of rows and columns? ) :

the first1st.zip has 6 columns and 95 rows/lines of predefined input data, in order to display my problem to you in a simple way.
It always have the same number of 6 columns, but the number rows/lines of predefined input data will increase to 10000 rows/lines.

Please, can you run the file first1st.zip above by first SELECTING THE AREA OF NUMBERS FOR CALCULATION
and the press CALC , it will show what i have been trying to explain.

I'm so grateful for your help

[twinnyfo]

AS a general rule, we don't download zip files unless we specifically ask for them on this forum. This is because many of us can't download these files at work, not to mention for general security reasons.

Thus, especially when describing your issue on a forum like this, you need to be more explicit and provide many more details--not just throw your project up here and ask us to take a look at it. I'm not being rude, just trying to explain site policies to a first-timer.

All that being said, at this point, I am somewhat aiming blindly, but I will ask a few more questions to help me out, but also provide at least a little bit of guidance as to "how I would approach this problem".

First,

8 11 22 33 44 55 in the first1st.zip when you must first SELECT THE AREA OF NUMBERS FOR CALCULATION
and the press CALC it will generate the following:
(11, 22, 33) (22, 33, 44) (33, 44, 55) and (44, 55, 66).

IF you have a set of six numbers, and IF you know that you will always have a set of six numbers, why does your code generate a 66? This is where the vast confusion factor sets in. I thought you were only comparing three numbers. Your result implies you are calculating a value. So, what is it?

Confusion factor 2:

The result should be treated as only one count for (11, 22, 33), because in the 2nd row/line of first1st.zip there is one combination that meets the condition difference between 3 any numbers is 11 33 -22 =11 , 22-11=11 so this should be counted only once . For the display , this form form (11, 22, 33) should be preferable than other forms such as (33,22,11) or (22,11,33).

Say what? How does 11, 33, -22 fit into your scheme? The first pair has a difference of 22, the second pair has a difference of 55. So, I have to ask again, EXACTLY what is it you are trying to do with this code?

Finally, my general approach to this problem:
Again, IF you have a set of six numbers, and IF you know that you will always have a set of six numbers, there should be no need to "select" the area of numbers. Let the code do the heavy lifting:

Option Explicit

Sub Listtriples3()
    Dim lngRow  As Long 'just in case you have a huge spreadsheet
    Dim intCol  As Integer
    Dim myT     As Range
 
    lngRow = 1
    intCol = 1
    Do While Not Cells(lngRow, intCol) = ""
        For intCol = 1 To 4 'for six cells you only need the first four values
            If Abs(Cells(lngRow, intCol) - Cells(lngRow, intCol + 1)) = 11 Then
                If Abs(Cells(lngRow, intCol + 1) - Cells(lngRow, intCol + 2)) = 11 Then
                    MsgBox "Row: " & lngRow & vbCrLf & _
                           "Columns: " & intCol & ", " & intCol + 1 & ", " & intCol + 2 & vbCrLf & _
                           "Values: " & Cells(lngRow, intCol) & ", " & Cells(lngRow, intCol + 1) & ", " & Cells(lngRow, intCol + 2)
                End If
            End If
        Next intCol
        lngRow = lngRow + 1
    Loop
End Sub

In this example, I just have a message box inform the user of any matches. I use the Abs() function for values that are out of order. Here is my set of values that I used:

1   12  23  24  34  35
11  22  33  22  11   7

This should give you enough to move forward with this--but again, I'm still not sure what the purpose of this is and what you are trying to do exactly. However, this approach should be easily modified to suit your needs.

Hope this hepps!

[brunoff]

my reply to the 1st question above: it will generate the following:
(11, 22, 33) (22, 33, 44) (33, 44, 55) and (44, 55, 66) . These three results are correct (11, 22, 33) (22, 33, 44) (33, 44, 55) but this last one here (44, 55, 66) it's wrong because the number 66 doesn't belong to the original input data 8 11 22 33 44 55 i can´t find where 66 came from since it wasn't present in the original input data 8 11 22 33 44 55

my reply to the 2st question above (Confusion factor 2:)that part i forget to put some punction to avert ambiguity. The correct passage should be written as:

The result should be treated as only one count for (11, 22, 33), because in the 2nd row/line of first1st.zip there is one combination that meets the condition difference between 3 any numbers is eleven, so that 33 -22 =11 , 22-11=11 so this should be counted only once . For the display , this form (11, 22, 33) should be prefereable than other forms such as (33,22,11) or (22,11,33).
i tried to use your code above but nothing happens.I don´t know if i did something wrong but it didn"t display any results. I'm using Visual Basic in MS Excel-2007.

Your help is so welcoming.

[twinnyfo]

OK - Let me make it perfectly clear so you can stop referring to it. I am not going to download your zip file. Please describe what you want to happen, what is going on and provide examples in your posts so that everyone can see what it is you are talking about.

Again, I still don't know exactly what you WANT this code to do. I also understand that you may be struggling with the language, but much is still not clear.

Concerning your first response above, you have only confirmed what I already knew: that 66 was not part of the list. But, keep in mind that your code is telling Excel to give you the value 66:

Cells(myC.Row, Col).Value = "(" & myC.Value & ", " & _
                            myC.Value + Diff & ", " & _
                            myC.Value + Diff + Deff & ")"

Your code is simply displaying the first value; adding 11, displaying that value; adding 22 and displaying that value. You are never displaying the actual numbers in your cells (aside from the first).

Your second response still makes as little sense to me as the first time I've read it. Again, this can be language. You refer to "counting", but you are not "counting" anything--this simply can be how you are referring to things, but it adds more confusion. You mention one display is "preferable" to another. What does that mean?

Please try using my code again--remove the word "Private" from the beginning of the sub (I'm an Access guy by trade, and we always declare things as either Public or Private, and some habits are hard to break). Then use the sample values I've provided and tell me what happens. It should give you several pop up messages that display certain cells that meet your criteria--but I am still terribly uncertain as to what your exact criteria are.

Option Explicit
 
Sub Listtriples3()
    Dim lngRow  As Long 'just in case you have a huge spreadsheet
    Dim intCol  As Integer
 
    lngRow = 1
    intCol = 1
    Do While Not Cells(lngRow, intCol) = ""
        For intCol = 1 To 4 'for six cells you only need the first four values
            If Abs(Cells(lngRow, intCol) - _
                Cells(lngRow, intCol + 1)) = 11 Then
                If Abs(Cells(lngRow, intCol + 1) - _
                    Cells(lngRow, intCol + 2)) = 11 Then
                    MsgBox "Row: " & lngRow & vbCrLf & _
                           "Columns: " & intCol & ", " & _
                                        intCol + 1 & ", " & _
                                        intCol + 2 & vbCrLf & _
                           "Values: " & Cells(lngRow, intCol) & ", " & _
                                        Cells(lngRow, intCol + 1) & ", " & _
                                        Cells(lngRow, intCol + 2)
                End If
            End If
        Next intCol
        lngRow = lngRow + 1
    Loop
End Sub

I'm trying to hepp, but you will need to me much more clear in your descriptions for us to make much progress.

Thanks!

[brunoff]

Im so grateful for your help . I used the input data below to run your code:

6    11    14    16    21    22
8    11    22    33    44    55
8    10    11    17    20    22
5    12    14    25    30    35
8    17    20    28    32    39

I modified the THEN PART of your code by putting [ Cells(lngRow, intCol).Value = "(" & Cells(lngRow, intCol) & ", " & Cells(lngRow, intCol + 1) & ", " & Cells(lngRow, intCol + 2))" ] above and i got the following result:

6    11    14    16    21    22
8    (11, 22, 33    (22, 33, 44    (33, 44, 55    44    55
8    10    11    17    20    22
5    12    14    25    30    35
8    17    20    28    32    39

by doing this it gave the result for 2nd row/ line (11, 22, 33 (22, 33, 44 (33, 44, 55 , but it was posted below the 2nd row/line. How fix the code above in order the results to be written at the right side end of input data (such as from cell J1 rightwards and downwards)?


Another problem the code did the calculation only for 2 rows of input data. It didn't do the calculation until the end of input data.

Forgive me due to my ignorance, i dont know how to fix the troubles above. because I only have very small limited knowledge of VB in Excel.

Please, Would you fix the code up?

Thank you TWINNYFO for your efforts on this matter.

[twinnyfo]

You will have to post the entire procedure before I try to trouble shoot it. But - it is clear that you have not used the code that I posted in the previous post. If you had, you would never have gotten the results of

(11, 22, 33    (22, 33, 44    (33, 44, 55

[brunoff]

I used the input data below to run your code:

6    11    14    16    21    22
8    11    22    33    44    55
8    10    11    17    20    22
5    12    14    25    30    35
8    17    20    28    32    39

the i used the modified the THEN PART of your code (i write the ehole code that i used):

Sub Listtriples4()
    Dim lngRow  As Long 'just in case you have a huge spreadsheet
    Dim intCol  As Integer
 
    lngRow = 1
    intCol = 1
    Do While Not Cells(lngRow, intCol) = ""
        For intCol = 1 To 4 'for six cells you only need the first four values
            If Abs(Cells(lngRow, intCol) - _
                Cells(lngRow, intCol + 1)) = 11 Then
                If Abs(Cells(lngRow, intCol + 1) - _
                    Cells(lngRow, intCol + 2)) = 11 Then
                    Cells(lngRow, intCol).Value = _
                        "(" & Cells(lngRow, intCol) & ", " & _
                        Cells(lngRow, intCol + 1) & ", " & _
                        Cells(lngRow, intCol + 2)
                End If
            End If
        Next intCol
        lngRow = lngRow + 1
    Loop
End Sub
 

I've made this procedure and it gave the result that i mentioned in the previous post.

Many thanks in advance

[twinnyfo]

First, this is why all parties need to be using the same set of data--test data, so that you're not looking at one set of numbers and I am looking at another.

Second, you may think this cruel, but I want to teach you a bit about troubleshooting first. Before I just give you the answer to what is left of this issue, I want you to look at the code you have and see that it is doing exactly what you are telling it to do. I have given enough in the code above to help you understand what you need to get the string placed into the proper cell.

Think about your code--don't just copy and paste. Unless you put in the effort yourself, this will not benefit you.

[brunoff]

Please, excuse my ignorance,I don´t have any background in programming, and I'm just getting started in VBA, i think that the code that i posted and your code that's extremely better than mine, share the same idea: to find adjacent values/cells whose difference (absolute value) is 11.

Forgive my ignorance,due to lack of knowledge in vba, i dont know how to fix that problems and I don't exactly know how to make the code do the calculation until the end of input data and to make it writting the results at the right side of input data.


I would appreciate to hear from you

Thank you for your help

[twinnyfo]

Brunoff,

First, ignorance due to lack of experience is something you should never be ashamed of nor apologize for. Just don't use that inexperience as a justification for inaction or as an excuse not to learn. Since it appears you fall into the category of wanting to learn, I will guide you through the solution so that you understand it (hopefully) and then you will be able to include these same principles into your future projects.

Here is the finalized code (I've used the same name for the sub as you listed above):

Option Explicit
 
Sub Listtriples4()
    Dim lngRow  As Long 'just in case you have a huge spreadsheet
    Dim intCol  As Integer
 
    lngRow = 1
    Do While Not Cells(lngRow, 1) = ""
        For intCol = 1 To 4 'for six cells you only need the first four values
            If Abs(Cells(lngRow, intCol) - _
                Cells(lngRow, intCol + 1)) = 11 Then
                If Abs(Cells(lngRow, intCol + 1) - _
                    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
        Next intCol
        lngRow = lngRow + 1
    Loop
End Sub

Line 1: this is a line you should have in all of your code (always the first line). It tells the VBA engine that you can only use variables after they have been explicitly declared somewhere in the code (see lines 4-5 below).

Lines 4-5: these two lines explicitly declare your variables. By declaring your variables, you do several things: 1) you assign a name to your variable that you can now freely use throughout this procedure; 2) you assign a variable type to that variable--this forces you to assign certain types of data to this variable, so that if you are working with numbers, trying to assign a text value to that variable will incur an error (this hepps with trouble shooting); 3) the computer also allocates resources by setting aside a segment of memory equivalent to the size of the variable you have declared--in general, with systems with many GB of memory this is not so important. But let's say you were a major system developer using thousands of variables. This would add up over time and using the smallest type of variable required can save system resources.

Lines 7: The lngRow variable is now set to a "default" value based upon the assumptions of your spreadsheet. The assumption is that there will be data in Row 1, Column 1 (i.e., that's where your data "starts"). You will see how these variables are implemented below.

Line 8: You do not want this code to run eternally, do we? No--we only want it to run for as long as we have data. Thus, if we encounter a cell in the first column of any row that is blank (Cells(lngRow, 1) = "") then we stop doing whatever it is that we are doing.

Line 9: Our assumption is that there are always 6 columns of data (never more and never less). If we are examining three sequential cells, then we only need to begin in the first four columns. When we begin in column 1, we will want to look at columns 1, 2 and 3. Beginning in column 4 looks at columns 4, 5 and 6. So, no need to begin in column 5. By using a For ... Next statement, this variable intCol will only ever be between 1 and 4.

Lines 10-11: Lines 12-13: Look at these lines very carefully to see what we are doing here. What is Cells(lngRow, intCol)? Well, the first time through, when lngRow = 1 and intCol = 1, we are referring to Cell(1, 1). Then, we are subtracting from the value of that cell Cells(lngRow, intCol + 1)--that statement is nearly identical, except that the Column value is intCol + 1. This means that we are looking at the cell in the same row, but in the cell adjacent to the previous one! Make sense? Now, we take the Abs() of the difference in value of these two cells and see if it is equal to 11. Because this is all part of an If ... Then statement, we only move on to Lines 12-13 if the values meet that criterion. Notice, then, how lines 12-13 look at cells in the same row, but in Columns intCol + 1 and intCol + 2 (the second and third cells in our sequence).

Lines 14-17: Assuming we have met these criteria, then we want to display these values in subsequent cells. Again, our assumptions are that there are six columns of values. Instead of placing our display in the first available cell (column 7), let's place it after an empty cell (the 8th column). Assuming that there is a matching set of values in Cell (lngRow, intCol), where intCol = 1, how would we place a value into the 8th column? We would add 7 to the intCol value. Hence, also, if intCol = 4, by adding 7, we place our displayed result in column 11.

Lines 18-19: These close out our If ... Then Statements.

Line 20: This is the closing part of the For ... Next Statement. This means that the variable intCol will increment my one, starting at 1 and when the value is greater than 4, it will exit this section of the code and move to the next statement.

Line 21: Because you have evaluated the first four cells in your SpreadSheet, it is now time to move to the next Row. This line is important, otherwise, your code will simply evaluate and re-evaluate the first Row infinitely.

Line 22: This line simply tells to code to go back to the Do While statement, evaluate the cell to see if it is empty. If not, repeat. If it is empty, then stop.

I hope all this makes sense. Please fee free to include any additional questions or comments about this code. Hopefully this will be something you can add to your toolkit as you grow in your VBA knowledge and skills.

Keep plugging away at it and never stop learning!

[brunoff]

Thank you twinnyfo. I´ve benn running some tests and i've just discovered that the code is omitting some results. For example, for the input data:

10    27    38    40    49    55
 3    14    20    25    40    48
7    9    18    26    29    51
 
 
 

It should show the following results: (27, 38, 49) ; (3, 14, 25) ; (7, 18, 29) , because the difference of any of these is 11. Looking at these results i guess that is the case when Abs(Cells(lngRow, intCol) - _
Cells(lngRow, intCol + 2)) = 11
that is not inside the code, i try to add it to the code (i don't know if i did right), but it continues omitting results.

Sub Listtriples04()
        Dim lngRow  As Long 'just in case you have a huge spreadsheet
        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

I'm hoping you will return favourably. Many thanks

[twinnyfo]

Your example does not meet the rules that we have been working with. We have been dealing with three consecutive cells. Your description above describes "any three cells".

This would have been helpful to know in the first place.

You do realize that this creates a total of 20 permutations if we simply look at those cells consecutively, and, as I can only imagine that the "real" desire is to find out if the first value is different from the first OR third value by 11 and as long as the second value is off by 11 from either of the other two values, that this gets into very complicated permutations.

My question then become where do you annotate these matching results? If you have the numbers (1, 12, 23, 12, 1, 12) you would have very many combinations of "matches"--you would have to account for that number of columns in your spreadsheet.

Perhaps I really need to ask this question first: What is this for? Is this just an exercise in mathematical permutations or is this actually being "used" for something?

The complexities you have just introduced are moving well beyond the "this is a fun little exercise" level of enjoyment.

[zmbd]

@brunoff
1) Have you taken the time to write down the actual goal of your project (If you want to achieve your goals, doesn't matter if it's matching your socks or solving a complex problem - state the problem and the goal).

2) Have you taken the time to take one set of your typical input data and by hand worked thru the steps, one step at a time, to reach the desired result(s)?

3) Do you have your pseudo logic handy?
It really appears that you've simply jumped into the drink without first sitting down and writing out the goal and then in simple language the general steps

4) If you have the pseudo logic, have you taken the time to actually flowchart your pseudo logic?
I realize, that's not how a lot of people are trained now; however, the old school ways are still, IMHO, the best way to get a project off the ground. I highly recommend using the Nassi-Shneiderman Charts style flow chart. Easy to understand, only a few core symbols to learn, easily applied to object oriented coding

5)

It should show the following results: (27, 38, 49) ; (3, 14, 25) ; (7, 18, 29) , because the difference of any of these is 11.

Here you've introduced an ambiguity in that abs(27-49) is not 11.
So lets look at the first row
10 27 38 40 49 55
are you wanting to compare along this logic:
abs(10-27); abs(27-38); abs(38-40); abs(40-49); abs(49-55)
or using this logic
abs(10-27); abs(10-38); abs(10-40); abs(10-49);(...)
abs(27-38); abs(27-40);(...)
abs(40-49);(...)

this is why step one above is so important
Before you sit down to start the project, you MUST have a clear goal in mind and clearly stated - preferably handwritten, experts have shown, handwriting the goal helps solidify the thoughts needed to reach the goal.

You should, whenever practical, have at least one clear example using typical data walking thru the steps from start to finish.

Please take a moment and sit down and read thru the NS charting technique, write out your logic/pseudo code, chart the code, then come back and restate your question...

[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.wolfram.com/PythagoreanTriple.html
+ https://en.wikipedia.org/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+22); 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