Identify and number Patterns

  • Re: Identify and number Patterns


    hmmm .. just down loaded the Medifile workbook and it doesnt re-generate the H column for new data .. it work well for the current data and for patterns in that data


    it will need to have a reset and quailfy a few bits

  • Re: Identify and number Patterns


    you probably notice that i have removed the [] or Evaluate as it "is the shortcut convenient when you want to refer to an absolute range. However, it is not as flexible as the Range property as it cannot handle variable input as strings or object references".


    best way too work with ranges is full qualify everything
    http:// http://msdn.microsoft.com/en-us/library/office/aa139976(v=office.10).aspx

  • Re: Identify and number Patterns


    Quote from snb;693980

    Could someone post holycow's code instead of having to download the whole file ?
    Thanks.


  • Re: Identify and number Patterns


    Quote from pike;693981

    hmmm .. and it doesnt re-generate the H column for new data ..


    Yes I know when larbec chimes in I need to ask him how the big list gets added to - whether by blocks of copy/paste OR by manually typing in.

  • Re: Identify and number Patterns


    Hi larbec
    how often does the WHOLE big list change? It will be faster if we don't have to update the WHOLE concatenation column every time. An incremental update will be much faster.

  • Re: Identify and number Patterns


    The desired output on Sheet 2 Columns L-Q is as follows


    the 10 rows prior to first row of pattern as per grid
    the 8 rows of the pattern as per grid
    the 10 rows immediately after last row of pattern as per grid


    followed by a line break


    Then repeat this process for each find of the pattern

  • Re: Identify and number Patterns


    Like ?


  • Re: Identify and number Patterns


    No, snb


    You are still only finding he first row of the input pattern (ie B2 to E2) what is needed is to find a match for the entire 8 rows of the search criteria (ie B2 to E10) then copy each instance of the found pattern together with the 10 preceding and 10 following rows to sheet2. The actual workbook has 400,000 rows of data which can increase.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Identify and number Patterns


    Tweeked my code a bit and got running time cut by half, including automatic regeneration of filtering columns to account for any changes in the data set, however made (except bitmap pasting, larbec!).



    https://www.dropbox.com/s/wn3t…lter%20Code%20larbec.xlsm

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Identify and number Patterns


    Another version if you want to get rid of all those unnecessarily slowing down formulae:


Participate now!

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