Identify and number Patterns

larbec · Nov 28th 2013

Re: Identify and number Patterns

there are no logical steps other than what you see. I can't get any clearer than I've already been. I'm looking for the same "numbers" I put in rows 1-8 Whatever numbers I put in rows 1-8. I want to find them in the same order throughout the 500,000 rows I'm sry , to me that's crystal clear and if I said anything different it would dirty the water. The cats and dogs is just a saying because there wasn't anything else to doing what I wanted to do.

I happen to speak with someone at the book store who immediately said " your looking to loop your information". No pictures, no other explanations and wallah I was reading up on looping and helper programs My step by step instructions would be

put numbers in rows 1-8
click on a button
find the "same" numbers in the "same" order throughout the 500,000 rows on sheet 1

thats it. Want to get fancy I can add " place the answers on sheet 2"
maybe it's just me, I've integrated equipment and programmed robots for 32 years. I see things different that others. Nick how else to explain this but I feel like a recording.

**KjBox** · Nov 28th 2013

Re: Identify and number Patterns

At last an explanation that goes some way towards clarifying what you want.

Pike gave you a solution that, with a bit of adjustment, produced the result you are after, that was in post #28 over a week ago!

I have modified and added to his filtering code so that the patterns found are listed on Sheet2. I also changed the message box which will now allow you to halt the code running at any stage whilst stepping through the 8 patterns being searched, you mentioned wanting to be able to check on results and change the search criteria if needed. Note that if you do stop the code running to view the partial results then you will have to start from the beginning again, the code cannot be resumed from the point at which it was stopped.

I hope this is what you are after, you can do adjustments to the code to display the results differently if desired.

Also note that if you follow the advice of the man in the bookshop and use loops you will be spending most of your time staring at the hourglass on your screen while the code loops through 500,000 rows 32 times! Why do you suppose pike used filters and not loops?

snb · Nov 28th 2013

Re: Identify and number Patterns

If you add columnlabels in A13:D13 this will suffice:

Code

Sub M_snb()
    Sheets("Sheet2").UsedRange.Offset(2).Clear


    sn = Sheets("sheet1").Cells(2, 2).CurrentRegion.Resize(10)
    With Sheets("Sheet1").Cells(13, 1).CurrentRegion
        For j = 2 To UBound(sn)
            If sn(j, 1) <> "" Then
            .AutoFilter 2, sn(j, 1)
            .AutoFilter 3, sn(j, 2)
            .AutoFilter 4, sn(j, 3)
            .AutoFilter 5, sn(j, 4)
            .Offset(1).Copy Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(2)
            .AutoFilter
            End If
        Next
    End With
End Sub

Display More

holycow · Nov 30th 2013

Re: Identify and number Patterns

Hi larbec

So this thread is long and hard to figure out the current status. I think, if I have understood correctly, that pike's solution works for you with one exception. That being that you need it to find 8 consecutive rows of the pattern simultaneously.

Please see attached. Slightly modified version of pike's masterpiece. It finds 8 consecutive patterns at a time.

I tested it on 500000 rows and the concatenation formulas in Column K really slowed things down so I had to copy paste as values only, leaving concatenation formula only in K3. When you add rows below Row 11 you will need to press the Update button to update concatenation in Column K.

If I have totally misunderstood things, then please just disregard this post. Also, if the loop solution from person in the bookstore is working for you, then likewise, please disregard this post.

larbec · Nov 30th 2013

Re: Identify and number Patterns

Hey guys, I'm still away for holiday. The loop program only started me thinking. It's actually a helper program. Works very quickly. I can place 8 rows of 4 digits and it takes no more than 15 seconds to find all of them. I will check thus out when I get home. An iPhone / iPad not doing me justice. Lol.

Once again, THANK YOU ALL and especially Pike for what you provided and lessons learned. I learned a lot thus past week and gave me insight to a new code Still have a long way to

larbec · Nov 30th 2013

Re: Identify and number Patterns

I just checked this out on my friends laptop. holy cow, this is perfect for what I want. Actually better than what I am using so THANK YOU DO MUCH!!!!

I entered around 400,000 rows and it keeps saying I have to click on update. Do I need to change some code?

**pike** · Dec 1st 2013

Re: Identify and number Patterns

Hi larbec,
It asking you to click the "Update" Button
Enjoy your holiday .. Well dome sandgroper ... I think!

larbec · Dec 1st 2013

Re: Identify and number Patterns

Correct Pike. It doesn't run after entering 400,000 rows. I hit update and nothing happens.

holycow · Dec 1st 2013

Re: Identify and number Patterns

It does work, I will see what you are doing wrong later when I am back on computer

larbec · Dec 1st 2013

Re: Identify and number Patterns

Quote from holycow;693433

It does work, I will see what you are doing wrong later when I am back on computer

The only thing I did was add my files and it ask me to reset. I reset and it did not run

https://www.dropbox.com/s/opol…OW%20Code%20larbec-4.xlsm

**pike** · Dec 1st 2013

Re: Identify and number Patterns

Hi larbac,
hmm.. is it possible the four columns by eight rows of data has no match of four columns by eight rows within your data?

upload a zipped workbook with no conditional formating and only 100,000 rows of data 0r paste your data into holycows workbook
you do realise it will only search for and match the exact 4 by 8 input grid of data?

**pike** · Dec 1st 2013

Re: Identify and number Patterns

Hey larbec,
Removed a few possible overflows
Replace all the code in Holycow workbook with

Code

Option Explicit
Dim Mrow
Dim sRow
Sub simple_search()
    If [COUNTA(B3:E10)] <> 32 Then
        MsgBox "Enter new search pattern"
        Exit Sub
    End If
    If Range("A" & Rows.Count).End(xlUp).Row <> Range("K" & Rows.Count).End(xlUp).Row Then
        MsgBox "Update required"
        Exit Sub
    End If
    Mrow = Range("A" & Rows.Count).End(xlUp).Row + 10
    Application.ScreenUpdating = False
    Range("K11").Select
    Cells.Find(What:=Range("K3").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
         xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
    sRow = ActiveCell.Row
    If sRow > 13 Then
    With Rows("12:" & ActiveCell.Row - 1)
        .Hidden = True
    End With
    With Rows(ActiveCell.Row + 8 & ":" & Range("A" & Rows.Count).End(xlUp).Row)
        .Hidden = True
    End With
    
    End If
    ActiveSheet.Shapes.Range(Array("Button 1", "Button 4")).Visible = False
    ActiveSheet.Shapes("Button 3").Visible = True
    [F3] = 1
    Application.ScreenUpdating = True
End Sub
Sub find_next()
    If [F3] = [F5] Then Exit Sub
    Application.ScreenUpdating = False
    Rows("1:" & Rows.Count).Hidden = False
    Cells.Find(What:=Range("K3").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
    sRow = ActiveCell.Row
   If sRow > 13 Then
    With Rows("12:" & ActiveCell.Row - 1)
        .Hidden = True
    End With
    With Rows(ActiveCell.Row + 8 & ":" & Range("A" & Rows.Count).End(xlUp).Row)
        .Hidden = True
    End With
    End If
    [F3] = [F3] + 1
    ActiveSheet.Shapes("Button 5").Visible = True
    If [F3] = 1 Then ActiveSheet.Shapes("Button 5").Visible = False
    If [F3] = [F5] Then ActiveSheet.Shapes("Button 3").Visible = False
    Application.ScreenUpdating = True
End Sub
Sub find_previous()
    If [F3] = 1 Then Exit Sub
    Application.ScreenUpdating = False
    Rows("1:" & Mrow).Hidden = False
    Cells.Find(What:=Range("K3").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Activate
    sRow = ActiveCell.Row
    If sRow > 13 Then
    With Rows("12:" & ActiveCell.Row - 1)
        .Hidden = True
    End With
    With Rows(ActiveCell.Row + 8 & ":" & Range("A" & Rows.Count).End(xlUp).Row)
        .Hidden = True
    End With
    End If
    [F3] = [F3] - 1
    If [F3] = 1 Then ActiveSheet.Shapes("Button 5").Visible = False
    If [F3] < [F5] Then ActiveSheet.Shapes("Button 3").Visible = True
    Application.ScreenUpdating = True
End Sub


Sub reset()
    Application.ScreenUpdating = False
    Rows("1:" & Mrow).Hidden = False
    Cells(sRow, 11).Select
    ActiveSheet.Shapes.Range(Array("Button 1", "Button 4")).Visible = True
    ActiveSheet.Shapes.Range(Array("Button 3", "Button 5")).Visible = False
    [F3] = 0
    [K11].Activate
    Application.ScreenUpdating = True
End Sub


Sub update()
    Application.ScreenUpdating = False
    With Range("K12:K" & Range("A" & Rows.Count).End(xlUp).Row)
        .NumberFormat = "0"
        .Value = "=B12&C12&D12&E12&B13&C13&D13&E13&B14&C14&D14&E14&B15&C15&D15&E15&B16&C16&D16&E16&B17&C17&D17&E17&B18&C18&D18&E18&B19&C19&D19&E19"
        .NumberFormat = "@"
        .Value = .Value
    End With
    [F5] = "=COUNTIF(K12:K" & Range("A" & Rows.Count).End(xlUp).Row & ",K3)"
    Application.ScreenUpdating = True
End Sub

Display More

You should thank her for interpreting your question

larbec · Dec 1st 2013

Re: Identify and number Patterns

Quote from pike;693436

Hi larbac,
hmm.. is it possible the four columns by eight rows of data has no match for the four columns by eight rows within your data?

upload a zipped workbook with no conditional formating and only 100,000 rows of data 0r paste your data into holycows workbook
you do realise it will only search for and match the exact 4 by 8 input grid of data?

yes i I understand about the grid. Will try this shortly. Thanks again Pike

larbec · Dec 1st 2013

Re: Identify and number Patterns

This works great Pike and Holycow, thanks so much, I like this a hole lot better than what i did. You all are the guru's and I have a lot to learn. THANK YOU THANK YOU THANK YOU

holycow · Dec 1st 2013

Re: Identify and number Patterns

Hi larbec

If things don't work please say so. Dont just say this works great. I just found that the reset button which was working, is now not working.

Please copy below line of code and paste it into the Sub reset code

Code

Mrow = Range("A" & Rows.Count).End(xlUp).Row + 10

Paste it after this line

Code

Application.ScreenUpdating = False

I think that's the right value to assign to Mrow pike (hi pike :thumbcoo: can you please check)

**pike** · Dec 1st 2013

Re: Identify and number Patterns

hi Holycow,
This works on the Mrow being Dimention with the first find ( say 1000000+10).If its dim in the reset() when the last mRow may only be the lat visible row(say100+10) and therefore not unhide all the rows to the last row.. but i will have another look

Code

Sub reset()
    Application.ScreenUpdating = False
    Rows("1:" & Mrow).Hidden = False
    Cells(sRow, 11).Select
    ActiveSheet.Shapes.Range(Array("Button 1", "Button 4")).Visible = True
    ActiveSheet.Shapes.Range(Array("Button 3", "Button 5")).Visible = False
    [F3] = 0
    [K11].Activate
    Application.ScreenUpdating = True
End Sub

holycow · Dec 1st 2013

Re: Identify and number Patterns

This is strange actually pike. I just did some further testing since Post #95 and it only bugs out sometimes. It works your way more often than it bugs out.

**pike** · Dec 1st 2013

Re: Identify and number Patterns

LOL what do you mean bugs out? .. give your self an upper cut

**pike** · Dec 1st 2013

Re: Identify and number Patterns

try adding an else to reset if you have ended up back at the start or only the seach data is found?

Code

If sRow > 13 Then
    With Rows("12:" & ActiveCell.Row - 1)
        .Hidden = True
    End With
    With Rows(ActiveCell.Row + 8 & ":" & Range("A" & Rows.Count).End(xlUp).Row)
        .Hidden = True
    End With
        Else
     Call reset
    End If

holycow · Dec 1st 2013

Re: Identify and number Patterns

It only bugged out twice. After heaps more attempts I couldn't manage another debug.

Quote

give your self an upper cut

done, ouch.

Identify and number Patterns

Participate now!

Share