How do I find a repeating set of cells in Excel?

Question

I Have a 2100 Rows and 6 Columns Table

Throughout the table there are only 12 Possible values, say A,B,C,D,E,F,G,H,I,J,K,L The 12th value L is just a blank filler. It denotes blank cell.

Since there are only 11 possible values througout the table, patterns are observed.

First a Pattern Appears and it is later repeated somewhere in the table. There can be any number of Patterns, but i have a specific format for a pattern which is to found and reported that way.

Solutions in EXCEL-VBA, PHP-MYSQL or C are welcome.

I have attached an example of what Iam looking for. Suggestions are most welcome to refine the questions.

Information & Format : http://ge.tt/8QkQJet1/v/0 [ DOCX File 234 KB ]

Example in Excel Sheet : http://ge.tt/69htuNt1/v/0 [ XLSX File 16 KB ]

Please comment for more information or specific requirement.

Firstly, do you wish to select a "pattern" to look for or are you asking for it to look at every possible combination there is in the 10,000 odd cells that you have information in? If it's the later, then this is going to take a long long time to check and capture anything that is found, it might not even be possible — LBPLC, Sep 03 '14 at 06:35
SilverShotBee : Thanks for your reply. The table has fixed values and are around 2200 rows now, the pattern, cannot exeed say 10 rows limit, means, the pattern will be present within 10 rows and max 15 rows. row 1 cannot match with any row that is more than 10 rows after it. there are only 10 possibilities in cells say, A B C D E F Z G H I. If you would like, i would attach an image. I want to find a pattern. If once matched, I can write an AND to match it wherever it is. — VIVEK, Sep 03 '14 at 06:39
@VIVEK: You need to tell us what pattern you are looking for. Else we can't help you tell your computer how to find it. — Jean-François Corbett, Sep 03 '14 at 07:10
@SilverShotBee : Sir I have provided some extra information in actual post and attached an example file for your reference. The example has a part of my table, an identified pattern and how arrangement of cells are needed to be matched. Please have a look. Thanks. — VIVEK, Sep 03 '14 at 18:52
@VIVEK - I dont really understand this comment: `A Pattern in my case refers to identical arrangements of cells. Cells may or may not contain same values, but can be arranged in exact manner. Partially even cells may match. In many cases, they match.` if the cells may or may not contain same values, how are they supposed to match? Please clarify this section — LBPLC, Sep 03 '14 at 19:10
@SilverShotBee Sir I meant that thier arrangement ( places match ) like in the attached file, Col6 Row2081 = Col1 Row2083 in table 1 and it exactly matches places with 2nd table's Col6 Row2091 = Col1 Row2093. Sample Places exactly. Sometimes values match, but main focus is places to match. A Pattern ( multi variable ) is set of such matches appearing in proximity of a grid and exactly match places with its repeating match. — VIVEK, Sep 03 '14 at 19:28
@SilverShotBee Sir , most of the patterns I observed does not exceed a spreading space of more than 50 rows. Even searching patterns in a fixed 50 rows and matching it with the remaining table will help me a lot. Patterns I observed dont use more than 7 variables (A-K), that means no pattern contains more than 7 matchables. — VIVEK, Sep 04 '14 at 00:03
@Jean-FrançoisCorbett I have provided an example excel table of pattern type im working on — VIVEK, Sep 04 '14 at 00:07
@VIVEK: Yes you have, but that example is not sufficient for us to unambiguously determine what the pattern is. Please spell it out in words, and illustrate with more examples, until the pattern is unambiguous. [Is this what you want?](http://i.stack.imgur.com/y7yRL.png) Maybe, maybe not, I just can't tell. — Jean-François Corbett, Sep 04 '14 at 06:38
@Jean-FrançoisCorbett I have attached a DOCX file with relevant information and two examples. Hope that helps . — VIVEK, Sep 04 '14 at 13:50
@SiddharthRout Minimum is 4 and Maximum 30 ( 15 Joint Sets of 2 ) I have attached a Docx file for your reference — VIVEK, Sep 04 '14 at 13:51
Now I think I get it. It's an interesting problem. But I'm afraid it's too broad and complex for a Stack Overflow question. We're not going to do your work for you. Try something, and then ask for help with any specific issues you run into. — Jean-François Corbett, Sep 04 '14 at 14:15
Agreed with @Jean-FrançoisCorbett, Im still struggling to understand your "Pattern", maybe Excel isn't the best pattern cracking software to be using. However, I have given you an answer that more than starts you in the right direction, so best of luck — LBPLC, Sep 04 '14 at 17:32
@SilverShotBee This will scan through the range, and find any pattern of two within a 10 row range, if you need it to find bigger patterns, youll need to add the same code again with an extra IF statement checking the next offset. Please mention a bit of IF code so that i will get a clear picture, I will customize and will report if solution is found — VIVEK, Sep 04 '14 at 18:58

LBPLC · Accepted Answer · 2014-09-03T13:08:50.277

Please try the code below, change the range to what you need it to be and the sheet number to the correct sheet number (I wouldn't put your full range in just yet because if you have 1000 pattern finds, you'll have to click OK on the message box 1000 times, just test with a partial range)

This will scan through the range, and find any pattern of two within a 10 row range, if you need it to find bigger patterns, youll need to add the same code again with an extra IF statement checking the next offset.

This will only find it if the same pattern exists and the same column structure is present, but its a start for you

Works fine on testing

Sub test10()

Dim rCell As Range
Dim rRng As Range

Set rRng = Sheets("Sheet1").Range("A1:I60") '-1 on column due to offset

'Scan through all cells in range and find pattern
For Each rCell In rRng.Cells
If rCell.Value = "" Then GoTo skip
         For i = 1 To 10
            If rCell.Value = rCell.Offset(i, 0).Value Then
                If rCell.Offset(0, 1).Value = rCell.Offset(i, 1) Then
                    MsgBox "Match Found at: " & rCell.Address & ":" & rCell.Offset(0, 1).Address & " and " & rCell.Offset(i, 0).Address & ":" & rCell.Offset(i, 1).Address
               End If
            End If
        Next i
skip:
Next rCell


End Sub

***UPDATE***

I have updated my code, the following now finds the pattern wherever it may appear in the next 10 rows:

Sub test10()

Dim rCell As Range
Dim rRng As Range
Dim r1 As Range
Dim r2 As Range

Set rRng = Sheets("Sheet1").Range("A1:I50") '-1 on column due to offset

i = 1 'row length
y = 0 'column length

'Scan through all cells in range and find pattern

For Each rCell In rRng.Cells
If rCell.Value = "" Then GoTo skip
i = 1
    Do Until i = 10
    y = 0
        Do Until y = 10
         xcell = rCell.Value & rCell.Offset(0, 1).Value
         Set r1 = Range(rCell, rCell.Offset(0, 1))
         r1.Select

         ycell = rCell.Offset(i, y).Value & rCell.Offset(i, y + 1).Value
         Set r2 = Range(rCell.Offset(i, y), rCell.Offset(i, y + 1))

            If ycell = xcell Then

                    Union(r1, r2).Font.Bold = True
                    Union(r1, r2).Font.Italic = True
                    Union(r1, r2).Font.Color = &HFF&
                    MsgBox "Match Found at: " & rCell.Address & ":" & rCell.Offset(0, 1).Address & " and " & rCell.Offset(i, y).Address & ":" & rCell.Offset(i, y + 1).Address
                    Union(r1, r2).Font.Bold = False
                    Union(r1, r2).Font.Italic = False
                    Union(r1, r2).Font.Color = &H0&
            End If
            y = y + 1
            Loop
            i = i + 1
        Loop
skip:
Next rCell


End Sub

How do I find a repeating set of cells in Excel?

1 Answers1