Identifying Repeating Pairs and Triples?

[binar]

Fellow Forum Members,
I have four columns of numbers that range between 1 through 44 and span 100 rows. Each row shows a unique combination as shown below:

Row 1 - 07 18 11 09
Row 2 - 03 29 08 05
Row 3 - 01 20 11 44
Row 4 - 09 17 14 24
Row 5 - 18 11 20 26
etc...

Can anyone give me some tips on creating a VBA script that will scan a hundred rows of the data shown above and identify for me the rows where number pairs and number triples repeat? For example the data above shows that the pairs "18 , 11" repeat on Row 5 and Row 1. Can EXCEL perform such a task with the right VBA script or forumula? Any help will be greatly appreciated. Thanks.

[MSP77079]

To be a match, do the numbers need to be in the same order? For example, is row 3 also a "type-2" match with row 5 (they both contain 20 & 11).

What do you want to do if/when you find a match?

Here is code in the case that the numbers do NOT need to be in the same order. This highlights "yellow" for a "type-2" match and "red" for a "type3" match.

Please Login or Register  to view this content.

[binar]

To be a match, do the numbers need to be in the same order? For example, is row 3 also a "type-2" match with row 5 (they both contain 20 & 11).

What do you want to do if/when you find a match?

Here is code in the case that the numbers do NOT need to be in the same order. This highlights "yellow" for a "type-2" match and "red" for a "type3" match.

Please Login or Register  to view this content.

MSP77079,
Thanks a lot for the VBA script you have developed. I have reviewed it and have even tried running it and I now have some questions.

First, I have added "A", "B", "C", "D" column headers because I forgot to this last time. This headers indicate that each number in each row occupies one cell, therefore each row uses four cells (one cell per number).

To answer your question, I would like your script to seek out number matches that are in the same order. So 20,11 and 11,20 will not be considered a match. The same will be true for triples. For example a triple like 3,5,7 will not be a match with 5,7,3. I have to praise you for pointing this out because I overlooked this detail.

Once your VBA script finds a match in the same number order, it would be cool if it can highlight a "type2" match as yellow and a "type3" match as red.

Lastly, I copied your code and then went to TOOLS -> MACRO and created a new macro named "checkmatches" and pasted your code. I then hit RUN and nothing happens. Obviously, I'm doing something wrong. My guess is that the rows and columns where my data is located on the worksheet doesn't match the parameters in your VBA script. Please let me know where you need the data located so that I can make sure this is not a problem.

Again, thanks a lot for what you have done. I never in a thousand years would have been able to do this because the VBA script seems to be very complicated. If you have any other questions please let me know. Thanks.



_______ A_ B_ C_ D
Row 1 - 07 18 11 09
Row 2 - 03 29 08 05
Row 3 - 01 20 11 44
Row 4 - 09 17 14 24
Row 5 - 18 11 20 26
etc...

[MSP77079]

I interpretted the data set up the same way you describe it (20 cells: rows 1 through 5 X columns A through D).

The code does NOT do what you want, as it identifies both row 1 and row 3 as a "type 2" match with row 5.

But, I just re-pasted the code to a new workbook and it does do that much.

Try it again.

Meanwhile, I will try to add the extra dimension you seek. Not sure how long my brain will hold out tonight, though.

[MSP77079]

OK, this is ugly, but it seems to work.

I had to change the dimension on the array to "fake in" a couple of extra "columns" (that are filled with blanks) to avoid a lot more logic. I also added a 6th row for testing of a type-3 pairing.

Instead of pasting the code, I'm uploading the workbook.

[Maistrye]

binar:

I'm curious too... how did you want to indicate multiple matches? ie. It is possible that a two number pairing could appear in three different lines (or more).

Also, what would you do to indicate that both matches occurred on a given line. This works fine:
(1) 00 01 02 03
(2) 00 01 02 04 -> obviously a 3-match, not a 2-match, against line (1)

but this:

(1) 00 01 02 03 -> 2-match and 3-match against several different lines
(2) 00 01 02 04 -> this is a 3-match against line (1), 2-match against (3)
(3) 01 02 03 09 -> this is a different 3-match against line (1), 2-match against (2)
(4) 00 01 04 05 -> this is a 2-match against line (1) and (2)
(5) 05 02 03 06 -> this is also a 2-match against line (1) and (3)

and i've only listed some of the combinations above.

This stuff might need some clarification. What MSP's already got for you may suit your needs though.

Scott