P: 10

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 zy=11 , yx=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 Excel2007. How can it be done in Visual Basic in MS Excel2007?
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
 
Share this Question
Expert Mod 2.5K+
P: 3,524

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 yz = 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!
 
P: 10

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 yz = 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 , 2211=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
  Expert Mod 2.5K+
P: 3,524

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 detailsnot 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 firsttimer.
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 , 2211=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 thisbut 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!
 
P: 10

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 , 2211=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 Excel2007.
Your help is so welcoming.
  Expert Mod 2.5K+
P: 3,524

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" anythingthis 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 againremove 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 criteriabut 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!
 
P: 10

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.
  Expert Mod 2.5K+
P: 3,524

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
 
P: 10

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
  Expert Mod 2.5K+
P: 3,524

First, this is why all parties need to be using the same set of datatest 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 codedon't just copy and paste. Unless you put in the effort yourself, this will not benefit you.
 
P: 10

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
  Expert Mod 2.5K+
P: 3,524

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 45 below). Lines 45: 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 variablethis 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 declaredin 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? Nowe 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 1011: Lines 1213: 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 1213 if the values meet that criterion. Notice, then, how lines 1213 look at cells in the same row, but in Columns intCol + 1 and intCol + 2 (the second and third cells in our sequence). Lines 1417: 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 1819: 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 reevaluate 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!
 
P: 10

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
  Expert Mod 2.5K+
P: 3,524

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.
  Expert Mod 5K+
P: 5,397

@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 NassiShneiderman 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(2749) 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(1027); abs(2738); abs(3840); abs(4049); abs(4955)
or using this logic
abs(1027); abs(1038); abs(1040); abs(1049);(...)
abs(2738); abs(2740);(...)
abs(4049);(...) 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...
  Expert Mod 10K+
P: 12,434

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.
  Expert Mod 5K+
P: 5,397

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 delta22?
Did I miss something, which is quite possible with how OP has posted  my understanding was that op was looking for delta11?
 
P: 10

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 zy=11 , yx=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.
  Expert Mod 5K+
P: 5,397

You are still not making any sense here...
Show us the math as I have done in step 5 of my post.
  Expert Mod 10K+
P: 12,434

@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.
 
P: 10

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
  Expert Mod 5K+
P: 5,397

@ 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 COMBINATIONwithout replication (not a permutation that is ((819) != (198)) ) of all of the values and only taking the groupings with a delta11  ignoring the replicate paring (8,8) so we gave CMB(6,2)=15 ordered pairs such that: abs(819); abs(821); abs(825); abs(828); abs(830);
abs(1921); abs(1925); 1abs(928); abs(1930);
abs(2125); abs(2128); abs(2130);
abs(2528); abs(2530);
abs(2830);
taking the numbers from each paring delta11, 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(830) = 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.
 
P: 10

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
  Expert Mod 10K+
P: 12,434

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.
  100+
P: 161

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

    Question stats  viewed: 3995
 replies: 24
 date asked: May 7 '19
