472,961 Members | 2,151 Online

# Finding Exact Difference between Three Numbers 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 z-y=11 , y-x=11.

Can someone give some aide???

I tried the following code:
Expand|Select|Wrap|Line Numbers
1. Sub Listtriples3()
2. Dim myC As Range
3. Dim myR As Range
4. Dim myT As Range
5. Dim Col As Integer
6. Dim firstCol As Integer
7. Dim Diff As Integer
8. Dim Deff As Integer
9.
10. Diff = 11
11. Deff = 11
12.
13. Set myT = ActiveCell.CurrentRegion
14. firstCol = Cells(myT.Cells(1).Row, _
15.                  Columns.Count).End(xlToLeft)(1, 3).Column
16. For Each myR In myT.Rows
17.     Col = firstCol
18.     For Each myC In myR.Cells
19.         If myC.Value <> "" Then
20.             If Not IsError(Application.Match(myC.Value + Diff, _
21.                                              myR.Cells, False)) Then
22.                 Cells(myC.Row, Col).Value = "(" & myC.Value & ", " & _
23.                                             myC.Value + Diff & ", " & _
24.                                             myC.Value + Diff + Deff & ")"
25.                 Col = Col + 1
26.             End If
27.         End If
28.     Next myC
29. Next myR
30. End Sub
31.
32.
33.
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
Attached Files first1st.zip (39.8 KB, 81 views)
May 7 '19 #1
24 7493 twinnyfo
3,653 Expert Mod 2GB
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!
May 8 '19 #2
brunoff
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 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
May 8 '19 #3
twinnyfo
3,653 Expert Mod 2GB
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

Expand|Select|Wrap|Line Numbers
1. Sub Listtriples3()
2.     Dim lngRow  As Long 'just in case you have a huge spreadsheet
3.     Dim intCol  As Integer
4.     Dim myT     As Range
5.
6.     lngRow = 1
7.     intCol = 1
8.     Do While Not Cells(lngRow, intCol) = ""
9.         For intCol = 1 To 4 'for six cells you only need the first four values
10.             If Abs(Cells(lngRow, intCol) - Cells(lngRow, intCol + 1)) = 11 Then
11.                 If Abs(Cells(lngRow, intCol + 1) - Cells(lngRow, intCol + 2)) = 11 Then
12.                     MsgBox "Row: " & lngRow & vbCrLf & _
13.                            "Columns: " & intCol & ", " & intCol + 1 & ", " & intCol + 2 & vbCrLf & _
14.                            "Values: " & Cells(lngRow, intCol) & ", " & Cells(lngRow, intCol + 1) & ", " & Cells(lngRow, intCol + 2)
15.                 End If
16.             End If
17.         Next intCol
18.         lngRow = lngRow + 1
19.     Loop
20. 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:

Expand|Select|Wrap|Line Numbers
1. 1   12  23  24  34  35
2. 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!
May 8 '19 #4
brunoff
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 , 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.

May 8 '19 #5
twinnyfo
3,653 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
1. Cells(myC.Row, Col).Value = "(" & myC.Value & ", " & _
2.                             myC.Value + Diff & ", " & _
3.                             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.

Expand|Select|Wrap|Line Numbers
1. Option Explicit
2.
3. Sub Listtriples3()
4.     Dim lngRow  As Long 'just in case you have a huge spreadsheet
5.     Dim intCol  As Integer
6.
7.     lngRow = 1
8.     intCol = 1
9.     Do While Not Cells(lngRow, intCol) = ""
10.         For intCol = 1 To 4 'for six cells you only need the first four values
11.             If Abs(Cells(lngRow, intCol) - _
12.                 Cells(lngRow, intCol + 1)) = 11 Then
13.                 If Abs(Cells(lngRow, intCol + 1) - _
14.                     Cells(lngRow, intCol + 2)) = 11 Then
15.                     MsgBox "Row: " & lngRow & vbCrLf & _
16.                            "Columns: " & intCol & ", " & _
17.                                         intCol + 1 & ", " & _
18.                                         intCol + 2 & vbCrLf & _
19.                            "Values: " & Cells(lngRow, intCol) & ", " & _
20.                                         Cells(lngRow, intCol + 1) & ", " & _
21.                                         Cells(lngRow, intCol + 2)
22.                 End If
23.             End If
24.         Next intCol
25.         lngRow = lngRow + 1
26.     Loop
27. 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!
May 9 '19 #6
brunoff
10 Im so grateful for your help . I used the input data below to run your code:
Expand|Select|Wrap|Line Numbers
1. 6    11    14    16    21    22
2. 8    11    22    33    44    55
3. 8    10    11    17    20    22
4. 5    12    14    25    30    35
5. 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:
Expand|Select|Wrap|Line Numbers
1. 6    11    14    16    21    22
2. 8    (11, 22, 33    (22, 33, 44    (33, 44, 55    44    55
3. 8    10    11    17    20    22
4. 5    12    14    25    30    35
5. 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.
May 9 '19 #7
twinnyfo
3,653 Expert Mod 2GB
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
Expand|Select|Wrap|Line Numbers
1. (11, 22, 33    (22, 33, 44    (33, 44, 55
May 9 '19 #8
brunoff
10 I used the input data below to run your code:
Expand|Select|Wrap|Line Numbers
1. 6    11    14    16    21    22
2. 8    11    22    33    44    55
3. 8    10    11    17    20    22
4. 5    12    14    25    30    35
5. 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):

Expand|Select|Wrap|Line Numbers
1. Sub Listtriples4()
2.     Dim lngRow  As Long 'just in case you have a huge spreadsheet
3.     Dim intCol  As Integer
4.
5.     lngRow = 1
6.     intCol = 1
7.     Do While Not Cells(lngRow, intCol) = ""
8.         For intCol = 1 To 4 'for six cells you only need the first four values
9.             If Abs(Cells(lngRow, intCol) - _
10.                 Cells(lngRow, intCol + 1)) = 11 Then
11.                 If Abs(Cells(lngRow, intCol + 1) - _
12.                     Cells(lngRow, intCol + 2)) = 11 Then
13.                     Cells(lngRow, intCol).Value = _
14.                         "(" & Cells(lngRow, intCol) & ", " & _
15.                         Cells(lngRow, intCol + 1) & ", " & _
16.                         Cells(lngRow, intCol + 2)
17.                 End If
18.             End If
19.         Next intCol
20.         lngRow = lngRow + 1
21.     Loop
22. End Sub
23.
I've made this procedure and it gave the result that i mentioned in the previous post.

May 9 '19 #9
twinnyfo
3,653 Expert Mod 2GB
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.
May 9 '19 #10
brunoff
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

May 12 '19 #11
twinnyfo
3,653 Expert Mod 2GB
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):
Expand|Select|Wrap|Line Numbers
1. Option Explicit
2.
3. Sub Listtriples4()
4.     Dim lngRow  As Long 'just in case you have a huge spreadsheet
5.     Dim intCol  As Integer
6.
7.     lngRow = 1
8.     Do While Not Cells(lngRow, 1) = ""
9.         For intCol = 1 To 4 'for six cells you only need the first four values
10.             If Abs(Cells(lngRow, intCol) - _
11.                 Cells(lngRow, intCol + 1)) = 11 Then
12.                 If Abs(Cells(lngRow, intCol + 1) - _
13.                     Cells(lngRow, intCol + 2)) = 11 Then
14.                     Cells(lngRow, intCol + 7) = _
15.                         "(" & Cells(lngRow, intCol) & ", " & _
16.                         Cells(lngRow, intCol + 1) & ", " & _
17.                         Cells(lngRow, intCol + 2) & ")"
18.                 End If
19.             End If
20.         Next intCol
21.         lngRow = lngRow + 1
22.     Loop
23. 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.

Keep plugging away at it and never stop learning!
May 13 '19 #12
brunoff
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:
Expand|Select|Wrap|Line Numbers
1. 10    27    38    40    49    55
2.  3    14    20    25    40    48
3. 7    9    18    26    29    51
4.
5.
6.
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.
Expand|Select|Wrap|Line Numbers
1. Sub Listtriples04()
2.         Dim lngRow  As Long 'just in case you have a huge spreadsheet
3.         Dim intCol  As Integer
4.
5.         lngRow = 1
6.         Do While Not Cells(lngRow, 1) = ""
7.             For intCol = 1 To 4
8.                 If Abs(Cells(lngRow, intCol) - _
9.                     Cells(lngRow, intCol + 1)) = 11 Then
10.                     If Abs(Cells(lngRow, intCol + 1) - _
11.                         Cells(lngRow, intCol + 2)) = 11 Then
12.                         If Abs(Cells(lngRow, intCol) - _
13.                     Cells(lngRow, intCol + 2)) = 11 Then
14.
15.                                                 Cells(lngRow, intCol + 7) = _
16.                             "(" & Cells(lngRow, intCol) & ", " & _
17.                             Cells(lngRow, intCol + 1) & ", " & _
18.                             Cells(lngRow, intCol + 2) & ")"
19.                     End If
20.                 End If
21.             End If
22.
23.
24.             Next intCol
25.             lngRow = lngRow + 1
26.         Loop
27.     End Sub
I'm hoping you will return favourably. Many thanks
May 17 '19 #13
twinnyfo
3,653 Expert Mod 2GB
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.
May 17 '19 #14
zmbd
5,501 Expert Mod 4TB
@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...
May 17 '19 #15
Rabbit
12,516 Expert Mod 8TB
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.
May 17 '19 #16
zmbd
5,501 Expert Mod 4TB
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?
May 17 '19 #17
brunoff
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 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.
May 17 '19 #18
zmbd
5,501 Expert Mod 4TB
You are still not making any sense here...
Show us the math as I have done in step 5 of my post.
May 17 '19 #19
Rabbit
12,516 Expert Mod 8TB
@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.
May 17 '19 #20
brunoff
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.

Expand|Select|Wrap|Line Numbers
1.
2. 1    13    16    21    22    24    none
3. 8    11    22    33    44    55    (11,22,33);(22,33,44);    (33,44,55)
4.
5. 8    19    21    25    28    30    (8,19,30)
6. 24    26    27    29    35    46    (24,35,46)
7. 8    17    20    28    32    39    (17,28,39)
8. 10    27    38    40    49    55    (27,38,49)
9. 3    14    20    25    40    48    (3,14,25)
10. 7    9    18    26    29    51    (7,18,29)
11. 2    12    16    23    34    65    (12,23,34)
12. 4    15    19    22    26    68    (4,15,26)
13. 5    11    16    27    58    75    (5,16,27)
14. 6    14    17    20    22    28    (6,17,28)
15. 36    47    58    70    85    91    (36,47,58)
16. 26    27    31    37    48    82    (26,37,48)
17. 15    19    21    26    28    37    (15,26,37)
18. 9    51    62    65    69    73    (51,62,73)
19. 14    42    43    45    53    64    (42,53,64)
20. 1    14    45    56    60    67    (45,56,67)
21. 31    32    54    56    65    76    (54,65,76)
22.
23.
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
May 18 '19 #21
zmbd
5,501 Expert Mod 4TB
@ Rabbit - you are a genius!

@ brunoff

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.
May 18 '19 #22
brunoff
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.

Expand|Select|Wrap|Line Numbers
1.
2.     Sub Listtriples04()
3.             Dim lngRow  As Long
4.             Dim intCol  As Integer
5.
6.             lngRow = 1
7.             Do While Not Cells(lngRow, 1) = ""
8.                 For intCol = 1 To 4
9.                     If Abs(Cells(lngRow, intCol) - _
10.                         Cells(lngRow, intCol + 1)) = 11 Then
11.                         If Abs(Cells(lngRow, intCol + 1) - _
12.                             Cells(lngRow, intCol + 2)) = 11 Then
13.                             If Abs(Cells(lngRow, intCol) - _
14.                         Cells(lngRow, intCol + 2)) = 11 Then
15.
16.                                                     Cells(lngRow, intCol + 7) = _
17.                                 "(" & Cells(lngRow, intCol) & ", " & _
18.                                 Cells(lngRow, intCol + 1) & ", " & _
19.                                 Cells(lngRow, intCol + 2) & ")"
20.                         End If
21.                     End If
22.                 End If
23.
24.
25.                 Next intCol
26.                 lngRow = lngRow + 1
27.             Loop
28.         End Sub
29.
30.
So that it calculates the results accurately by using the input data :

Expand|Select|Wrap|Line Numbers
1.
2.         input data                         the desired results
3. 1    13    16    21    22    24    none
4. 8    11    22    33    44    55    (11,22,33);(22,33,44);    (33,44,55)
5.
6. 8    19    21    25    28    30    (8,19,30)
7. 24    26    27    29    35    46    (24,35,46)
8. 8    17    20    28    32    39    (17,28,39)
9. 10    27    38    40    49    55    (27,38,49)
10. 3    14    20    25    40    48    (3,14,25)
11. 7    9    18    26    29    51    (7,18,29)
12. 2    12    16    23    34    65    (12,23,34)
13. 4    15    19    22    26    68    (4,15,26)
14. 5    11    16    27    58    75    (5,16,27)
15. 6    14    17    20    22    28    (6,17,28)
16. 36    47    58    70    85    91    (36,47,58)
17. 26    27    31    37    48    82    (26,37,48)
18. 15    19    21    26    28    37    (15,26,37)
19. 9    51    62    65    69    73    (51,62,73)
20. 14    42    43    45    53    64    (42,53,64)
21. 1    14    45    56    60    67    (45,56,67)
22. 31    32    54    56    65    76    (54,65,76)
I hope to hear from you
May 19 '19 #23
Rabbit
12,516 Expert Mod 8TB
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.
May 20 '19 #24
SioSio
272 256MB
One less loop if compared by string
Expand|Select|Wrap|Line Numbers
1. Sub Listtriples04()
2.     Dim lngRow  As Long
3.     Dim intCol  As Integer
4.     Dim N(3) As Integer
5.     Dim SN(3) As String
6.     Dim Str As String
7.     Dim i As Integer
8.     For lngRow = 1 To Range("A1").End(xlDown).Row
9.         Str = ""
10.         For intCol = 1 To Cells(lngRow, 1).End(xlToRight).Column
11.             Str = Str & " " & Trim(Cells(lngRow, intCol).Value)
12.         Next
13.         i = Cells(lngRow, 1).End(xlToRight).Column
14.         For intCol = 1 To 6 'Cells(lngRow, 1).End(xlToRight).Column
15.             N(0) = CInt(Cells(lngRow, intCol).Value)
16.             N(1) = N(0) + 11
17.             N(2) = N(1) + 11
18.             SN(0) = " " & CStr(N(0))
19.             SN(1) = " " & CStr(N(1))
20.             SN(2) = " " & CStr(N(2))
21.             If InStr(Str, N(1)) > 0 And InStr(Str, N(2)) > 0 Then
22.                 i = i + 1
23.                 Cells(lngRow, i).Value = "(" & SN(0) & "," & SN(1) & "," & SN(2) & ")"
24.             End If
25.         Next
26.     Next
27. End Sub
28.
Dec 27 '19 #25

 1 by: RG | last post by: I have a technical question about using VB.NET & databases. I want to post it to microsoft.public.dotnet.languages.vb.data , but that choice is not listed in the selection box of discussion... 13 by: Karolina Jackiewicz | last post by: I want ask first question in newsgroup but I know evrything already about databases. Can I ask question on another theme? 2 by: John | last post by: Hi all, I really don't know where to post this question but it is urgent and I need resolution on this. I just re-installed my Windows since it was just downloading something from the... 4 by: Redefined Horizons | last post by: I still new to Python, and I've only dabbled in C, but I've got my first project I need to tackle that involves both languages. I was hoping to get some advice on how to proceed. There is a... 4 by: rengaraj | last post by: Can i post interview question here ?? Along the known answer (which i think it is correct) 1 by: 58TT | last post by: Hi Everyone, I am a new to this forum, I tried to post my question in .net forum but it not excepting question. Can anyone guide me like how and when I can post my question in that... 4 by: jonKushner | last post by: I cant really think of a reason why I couldn't pesist all my data from the previous post first, before applying the repost . Logically, if you think about it as a container of storage... the data... 3 by: Stang1 | last post by: The following statement: line_buf = ' '; is equivalent to: line_buf = ' '; line_len++; 0 by: lllomh | last post by: Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{ 2 by: DJRhino | last post by: Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing... 0 by: Aliciasmith | last post by: In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app... 0 by: tracyyun | last post by: Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to... 2 by: giovanniandrean | last post by: The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions... 4 by: NeoPa | last post by: Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :... 1 by: Teri B | last post by: Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and... 0 by: NeoPa | last post by: Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it... 2 by: GKJR | last post by: Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...