Identify and number Patterns

  • Re: Identify and number Patterns


    pike


    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.

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

  • Re: Identify and number Patterns


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


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

  • 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

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

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

  • Re: Identify and number Patterns


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


    You should thank her for interpreting your question

  • 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

  • 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

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

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

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!