462,269 Members | 520 Online Need help? Post your question and get tips & solutions from a community of 462,269 IT Pros & Developers. It's quick & easy.

# Finding Exact Difference between Three Numbers

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 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, 32 views)
May 7 '19 #1
24 Replies

 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 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

 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 , 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. May 8 '19 #5

 P: 10 Im so grateful for your help . I used the input data below to run your code: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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. May 9 '19 #7

 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 Expand|Select|Wrap|Line Numbers (11, 22, 33    (22, 33, 44    (33, 44, 55 May 9 '19 #8

 P: 10 I used the input data below to run your code: Expand|Select|Wrap|Line Numbers 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): Expand|Select|Wrap|Line Numbers 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 May 9 '19 #9

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

 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 May 12 '19 #11

 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): Expand|Select|Wrap|Line Numbers 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! May 13 '19 #12

 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: Expand|Select|Wrap|Line Numbers 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. Expand|Select|Wrap|Line Numbers 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 May 17 '19 #13

 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. May 17 '19 #14

 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 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

 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. May 17 '19 #16

 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 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

 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 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

 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. May 17 '19 #19

 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. May 17 '19 #20

 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. Expand|Select|Wrap|Line Numbers   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 May 18 '19 #21

 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 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

 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. Expand|Select|Wrap|Line Numbers       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 : Expand|Select|Wrap|Line Numbers           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 May 19 '19 #23

 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. May 20 '19 #24

 100+ P: 161 One less loop if compared by string Expand|Select|Wrap|Line Numbers 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   Dec 27 '19 #25 