Identify Number Patterns

larbec · Mar 30th 2014

If this post twice I am sorry. I tried to post earlier and I thought it posted but it didn't Anyways

I have a macro and need to expand some columns that I want to search. Presently I enter what I call counter numbers in 4 cells (the counters are 4 digits) The game number goes in column A, then counter digit 1 in B, digit 2 in C, digit 3 in D and digit 4 in E. I place the counter numbers I am searching for under the C1,C2,C3,C4 (HIJK) and then press find Pattern Matches. I can enter up to 12-15 rows if I like. Sometimes I only want to look for 4 rows, other times 10 so I have the ability to change what I want to look for by changing the number in N. When I press "find pattern matches" presently It will find all the location that the counter numbers are which is 4 column's. I want to be able to include some additional columns so I not only have the counter numbers but the additional columns too.

okay hope I haven't lost anyone yet LOL.I would also like to group together columns AB,AC,AD,AE,AF,AG,AH,AI,AJ like on sheet G-G+ and sheet 3 fro the Macro. Once it finds the counters it places the information on sheet 2. I have several different sheets to look through and each sheet has over 40,000 rows on counter numbers I will be needing to search through

Let me know if you have any questions and I REALLY appreciate your help

I corrected the sheet to how I want it to look. Presently the Macro does NOT work

Code

https://www.dropbox.com/s/cxa4…ern%20Search%20Bingo.xlsm

Code

https://www.dropbox.com/s/q8hg844sbyh313i/G-G%2B.xlsx

larbec · Mar 31st 2014

Re: Identify Number Patterns

I as just thinking. It probably does not matter which columns any of the information goes in on the other sheet or the first sheet as long as it sorts it all together. In other words, if I m searching for rows that are for example counter numbers 2233,2234,2235,2236 I get all the information associated with those counters i.e. U,D,G+,G- etc.... . Does this make sense I will copy and paste my data from the master files to this Macro to build the Master file to do the searches

**KjBox** · Mar 31st 2014

Re: Identify Number Patterns

The file you posted to dropbox was 14.2MB for some reason, I copied data to a new workbook (attached) and it reduced in size to 28.9KB! I have also redone your conditional formatting, many rules were duplicated or even conflicting.

The amended code is:

Code

Option Explicit




Sub PatternSearch()
Dim c00, sn, st
Dim j As Long, jj As Long, dPRw As Double, sPat As String
Dim iAbv As Integer, iBlw As Integer, rCel As Range, rPtn As Range
iAbv = [x3]
iBlw = [x4]
dPRw = Range("r3:r" & Range("r" & Rows.Count).End(xlUp).Row).Cells.Count
Set rPtn = Range("r3:u" & Range("u" & Rows.Count).End(xlUp).Row)
    
    If [x3] = vbNullString Or [x4] = vbNullString Then
        MsgBox "You must enter a value (even if it is 0) for Above and Below", , "Missing Value"
        Exit Sub
    End If
    For Each rCel In rPtn
        sPat = sPat & rCel
    Next
    c00 = [sPat]
    If Len(c00) <> dPRw * 4 Then
        MsgBox "Search criterion not complete. Enter 4 numbers for each row.", , "Criteria missing"
        Exit Sub
    End If
    Sheets("sheet2").Columns("a:p").ClearContents
    sn = Sheets("sheet1").Cells(1).CurrentRegion.Offset(1).Resize(Cells(1).CurrentRegion.Rows.Count - 1, 16)
    For j = 1 To UBound(sn) - dPRw
        If sn(j, 2) = Val(Left(c00, 1)) Then
            For jj = 0 To (dPRw * 4) - 1
                If sn(j + jj \ 4, jj Mod 4 + 2) <> Val(Mid(c00, jj + 1, 1)) Then Exit For
            Next
            If jj = dPRw * 4 Then
                ReDim st(1 To (dPRw + iAbv + iBlw), 1 To 16)
                For jj = 1 To (dPRw + iAbv + iBlw) * 16
                    If j - iAbv + (jj - 1) \ UBound(st, 2) > UBound(sn) Then Exit For
                    If j < iAbv And jj > UBound(sn, 2) * (dPRw + iBlw - 1 + j) Then Exit For
                    st((jj - 1) \ UBound(st, 2) + 1, (jj - 1) Mod UBound(st, 2) + 1) = sn(Application.Max(j - (iAbv + 1), 0) + (jj - 1) \ UBound(st, 2) + 1, (jj - 1) Mod UBound(st, 2) + 1)
                Next
                Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(UBound(st), UBound(st, 2)) = st
            End If
        End If
    Next
    With Sheets("sheet2")
        .Columns("p").ColumnWidth = 10
        .[a1] = "Search Results"
    End With
    If Sheets("sheet2").[a3] = vbNullString Then
        MsgBox "No matching pattern found", , "No Match"
        Application.Goto ([a2])
    Else
        Application.Goto (Sheets("sheet2").[a1])
    End If
End Sub

Display More

larbec · Mar 31st 2014

Re: Identify Number Patterns

wow!!! This looks like it will work. Let me add some data and play around with it and report back!!!! Again, you are AWESOME and AMAZING!!!!!

snb · Mar 31st 2014

Re: Identify Number Patterns

Code

Sub M_snb()
   with Sheet3
     sn = Cells(1).CurrentRegion.Resize(, 16)
     For j = 1 To UBound(sn)
       c00 = c00 & vbLf & Join(Application.Index(sn, j), "_")
     Next
   
     c01 = Join(Application.Transpose(Application.Transpose(.Cells(3, 18).Resize(, 4).Value)), "_") & "_"
     For Each it In Filter(Split(c00, vbLf), c01)
       .Columns(16).Find(Split(it, "_")(UBound(Split(it, "_"))), , , 1).Offset(-.Cells(3, 24), -15).Resize(1 + .Cells(3, 24) + .Cells(4, 24), 16).Copy Sheet5.Cells(Rows.Count, 1).End(xlUp).Offset(2)
     Next
  End With
End Sub

Display More

larbec · Mar 31st 2014

Re: Identify Number Patterns

when I copy the content on sheet 2 to a new sheet to compare the results I lose my borders. Why does this happen. I am opening a new sheet which is how I have don it in the past

**KjBox** · Mar 31st 2014

Re: Identify Number Patterns

Have you redone the conditional formatting for your file using the same conditions (in the same order) as the file I uploaded? You need to so for both the data sheet and the results sheet.

larbec · Mar 31st 2014

Re: Identify Number Patterns

when I copy the content on sheet 2 to a new sheet to compare the results I lose my borders. Why does this happen. I am opening a new sheet which is how I have don it in the past

Code

https://www.dropbox.com/s/q7c3…mdn/no%20border%20pic.png

Code

larbec · Mar 31st 2014

Re: Identify Number Patterns

This is how it looks after I copy and paste into the new Macro Book

Code

https://www.dropbox.com/s/8raa…3/Full%20no%20border.xlsm

Code

larbec · Mar 31st 2014

Re: Identify Number Patterns

What should I do with this and what does it do>

Thanks

Quote from snb;707998

Code

Sub M_snb()
   with Sheet3
     sn = Cells(1).CurrentRegion.Resize(, 16)
     For j = 1 To UBound(sn)
       c00 = c00 & vbLf & Join(Application.Index(sn, j), "_")
     Next
   
     c01 = Join(Application.Transpose(Application.Transpose(.Cells(3, 18).Resize(, 4).Value)), "_") & "_"
     For Each it In Filter(Split(c00, vbLf), c01)
       .Columns(16).Find(Split(it, "_")(UBound(Split(it, "_"))), , , 1).Offset(-.Cells(3, 24), -15).Resize(1 + .Cells(3, 24) + .Cells(4, 24), 16).Copy Sheet5.Cells(Rows.Count, 1).End(xlUp).Offset(2)
     Next
  End With
End Sub

Display More

snb · Mar 31st 2014

Re: Identify Number Patterns

Take the file in post #3 and test the code.

**KjBox** · Mar 31st 2014

Re: Identify Number Patterns

Look at the conditional formatting for the file you just posted to dropbox, it is nothing like the formatting I redid for you. You need to go to the file from which you copy and redo the conditional formatting for that file exactly the same as the file I uploaded on post #3.

Try:

Copying VALUES ONLY of your master file onto my uploaded file, it should format correctly, then use that as your master and NOT the original one

Update:

I just copied the data from your latest dropbox upload and and pasted values only onto my workbook, see attachment.

**KjBox** · Mar 31st 2014

Re: Identify Number Patterns

Sorry last attachment was not the correct conditional formatting, use this one.

larbec · Mar 31st 2014

Re: Identify Number Patterns

Thanks KJ. I will try that when I get home. I'm on my iPad and when I download the file the color is missing. Not sure if it's my iPad or the macro. I use the color(s) because it's easier and quicker for our brains. When we see red at a traffic light we know to stop. Green we go. For me Red font with yellow is odd and green font with gray is even. So I don't even have to think about it plus for the pattern recognition it's necessary for my calculations I know you didn't ask about this but thought I would share my thoughts as to why it's that way. You have no idea how much I appreciated your help. I really need to take some classes on this because I'm clueless and get frustrated. Again, THANK YOU!!!

**KjBox** · Mar 31st 2014

Re: Identify Number Patterns

Pleased you said that, made me realise I had formatted the other way round (red/yellow = even). The attached file has that corrected.

larbec · Mar 31st 2014

Re: Identify Number Patterns

Yes Red Font shaded yellow is Odd. Green Font shaded gray is even. On my system UP is also even and DOWN is Odd I do a lot with colors (stop light theory) and patterns thanks!!

larbec · Apr 1st 2014

Re: Identify Number Patterns

I stand to be corrected. When I copy from sheet 2 and paste on to a "new" sheet from another book it does not have the borders around the cells. Is there a special way to paste them? Some cells lose the formatting of the gray and yellow

larbec · Apr 1st 2014

Re: Identify Number Patterns

I do have another question. Is it possible to search for the 4 columns that are labeled U and D and have the counters C1,C2,C3,C4 follow suit? Kinda searching opposite of what we have here. I'm looking for specific patterns that match. If I need to make a separate post I can.

Im im still having an issue when I copy and paste. The borders disappear. Thank you so much for all your help

**KjBox** · Apr 1st 2014

Re: Identify Number Patterns

No need for a second post as it just involves a modification of the current code as a separate routine.

Do you mean you want all 16 columns displayed as for the counter search? Do you want results displayed on the same sheet as the counter search results, and in adjacent columns, with a space between each set of results?

larbec · Apr 1st 2014

Re: Identify Number Patterns

I will try to explain it this way. I want to search for columns JKMN you can take out columns LO if you have to if not they can become a dummy column for the search where the C1,C2,C3,C4 is. Its the 4 numbers that I need. The breaks just help me split them up for viewing purposes. If I can have the breaks fantastic, if not no big deal. Okay slide columns JKMN as C1,C2,C,C4. Slide B-I to the right and keep the game number as is. I need the black breaks (columns shaded in black for viewing)

On your other question about all 16 column's, are you asking me do I want both ways to view? You can do that? LOL I mean....yeah YOU probably can and I would love that, it not I can use two different files. They can be displayed on the same sheet or you can put them on different sheets, up to you as long as the sheets are labeled. I can figure them out if not. If you want names for the sheets. The sheet with JKMN can be called G+G- and the other you can call Counters

I hope this all makes sense/ Also, if possible I really need the ability to copy and paste with the borders. Not sure why its not working as I am opening a page within the same book. It does not allow me to put borders on it either after I paste the cells

Identify Number Patterns

Participate now!

Share

Users Viewing This Thread