Identify and number Patterns

Re: Identify and number Patterns

with the orginal data set there are five matching sets your code captures one .. It also bugs out if there is not match ..

Re: Identify and number Patterns

snb,

My apologies, your code did find the complete 8 row pattern. I did not realise that you were using an old example with only about 50 rows of data that was repeated to create 1,200 rows, hence your find of 38 instances.

My first idea of using filters was like yours - to filter range H12 down to end using H3 as criteria but I ran into problems, my computer either hung or excel froze. I have tried your code on the full data set of 350,000 rows of data and the same thing is happening. Testing before seemed to show that the filter would only work for the first 200,000 rows or so. I thought that might be due to there being a byte limit to auto filter and filtering a 32 digit number in 350,000 rows exceeded that limit, do you know if this is the case? That might explain pike's

Quote

arrr no .used another data set and doesnt capture them all ..

if you did try the code on the full set of data, pike.

Anyway that is the reason I ended up splitting that 32 digit number into 32 columns and running 32 separate filters.

Can you try your code(s) on holycow's upload of the full data set complete with the 32 digit numbers:

http://www.mediafire.com/downl…6/Filter+Code+larbec.xlsm

I have just had another look at your code and noticed that the count of found patterns is 38 but sheet 2 has only 36 blocks of data, so all were not found.

Re: Identify and number Patterns

holycow was aware of the problems filtering the setup she designed. That is why she wrote code to replace matches of H3 in H12 to end with a blank cell, and then used SpecialCells(4) to get the matching rows and thus the data for copying.

That is lightning fast.

The problem now is regenerating the range H 12 to end when the data set changes, that is slow using the 32 digit number method but very fast to create 32 columns, but the running of the full code takes about 12 seconds (including population of the 32 columns).

Seems to be a catch 22 situation: fast code and slow generation or slow code and fast generation!

Re: Identify and number Patterns

That's another Excel limitation: the amount of areas in a range: 36.

So lets' avoid Excel's limitations and introduce a VBA approach:

Sub M_snb()
    Sheet2.Columns("L:P").Clear
    c00 = [B2&C2&D2&E2&B3&C3&D3&E3&B4&C4&D4&E4&B5&C5&D5&E5&B6&C6&D6&E6&B7&C7&D7&E7&B8&C8&D8&E8&B9&C9&D9&E9]
    
    sn = Sheet1.Cells(12, 1).CurrentRegion
    For j = 2 To UBound(sn)
      If sn(j, 2) = Val(Left(c00, 1)) Then
        For jj = 0 To 31
            If sn(j + jj \ 4, jj Mod 4 + 2) <> Val(Mid(c00, jj + 1, 1)) Then Exit For
        Next
        If jj = 32 Then Sheet2.Cells(Rows.Count, 12).End(xlUp).Offset(2).Resize(28, 5) = Application.Index(sn, Evaluate("row(" & j - 10 & ":" & j + 28 & ")"), Array(1, 2, 3, 4, 5))
      End If
    Next
End Sub

Re: Identify and number Patterns

Just ran that with the full data set and it did nothing!

Saw:

c00 = [B2&C2&D2&E2&B3&C3&D3&E3&B4&C4&D4&E4&B5&C5&D5&E5&B6&C6&D6&E6&B7&C7&D7&E7&B8&C8&D8&E8&B9&C9&D9&E9]

should be:

c00 = [B3&C3&D3&E3&B4&C4&D4&E4&B5&C5&D5&E5&B6&C6&D6&E6&B7&C7&D7&E7&B8&C8&D8&E8&B9&C9&D9&E9&B10&C10&D10&E10]

Ran again and got a type mismatch at:

Sheet2.Cells(Rows.Count, 12).End(xlUp).Offset(2).Resize(28, 5) = Application.Index(sn, Evaluate("row(" & j - 10 & ":" & j + 28 & ")"), Array(1, 2, 3, 4, 5))

I will leave it up to you to see what is causing the error as I could not!

BTW I do not think Excel's limitation of 36 areas will be a problem in the full data set, testing my code with numerous patterns there was never more than 5 matches.

Re: Identify and number Patterns

See the attachment

Re: Identify and number Patterns

Ok this is weird!

Code ran in your sample (though I did notice that after you deleted row 1 in order to correct the starting row of c00 you did not change the current.region to cells(11,1))

But, running the same code on the full data set (with row 1 deleted and current.region corrected) gives the type mismatch.

There is no change in the structure of the full data set, the only difference is the number of rows (plus the data not being a repeating block of 50 rows as in your example). Full data set goes to row 334588, but as I understand it this will increase to 500,000+ later.

Re: Identify and number Patterns

Just noticed another issue.

With your workbook sample if I change the search criteria to a starting row that is less that the 10th row of the data then the first area of the results is nothing but #Value!.

You need to incorporate some code so that if the pattern match starts in less than the tenth row of the data set then the first area for copying needs to start at the start of the data set rather than 10 rows above the matching pattern start.

Just tried a pattern at the end of the set and if less 10 rows of data are below the end of the matching pattern then rows above 10 have #Ref! in the results.

Also, there are only 5 columns in the results. There should be 6.

Re: Identify and number Patterns

Not sure what would cause that error. Try a different pattern, if that runs ok then retry the pattern that caused the error. If it still errors let me know what pattern you were searching for and I will try it and investigate.

Re: Identify and number Patterns

KjBox

If you step trough the code or introduce a on error handler you can indicate exactly which line produces the error, and the values of the variabels involved.

Mainly I am interested in the method; taking care of the first 10 rows or the last 10 rows I consider finetuning. The same applies to 6 columns in sheet2.

Using currentregion takes care to select the whole currentregion, no matter from which cell it's being invoked from (as long as it is part of that region).

So if you can inform me at what point the error occurs, I might be able to fix that (although I might have in the attachment).

Re: Identify and number Patterns

I did step through the code and gave the point at which the error occurs in post #165.

Run time error 13: Type mismatch occurs at:

Sheet2.Cells(Rows.Count, 12).End(xlUp).Offset(2).Resize(28, 5) = Application.Index(sn, Evaluate("row(" & j - 10 & ":" & j + 28 & ")"), Array(1, 2, 3, 4, 5))

When error occurs jj is 32 and j is 12 which is correct for the start row for the first pattern match.

Re: Identify and number Patterns

Just forget the last version.

Try this one: (it must be fast as lightning)

Re: Identify and number Patterns

Same thing. Code works with your workbook but same error with full data set.

Error occurs at:

Sheet2.Cells(1, 12).Resize(UBound(sp), 6) = Application.Index(sn, sp, Array(1, 2, 3, 4, 5, 6))

j=334571 and jj=2

Well an error handler moved down through the code still handles the error when placed immediately above that line, I cannot step through the code to be absolutely certain as the error is occurring on the 334,571th row!

That is 18 rows above the last row of data but there is no pattern match anywhere near there.

Re: Identify and number Patterns

Tried other patterns (ones that produce results with other code) and they all gave the same error. j=334571 every time but j is different.

Re: Identify and number Patterns

KjBox

j=334571 means it's the last row +1 because the code loops through all rows before writing anything.

Can you please check using:

sp = Application.Transpose(Split(Mid(c01, Len(Format(UBound(sn))) + 3), "|"))
MsgBox UBound(sp) & vbLf & UBound(sp, 2)
Sheet2.Cells(1, 12).Resize(UBound(sp), 6) = Application.Index(sn, sp, Array(1, 2, 3, 4, 5, 6))

Re: Identify and number Patterns

Hi Larbec
Use this copy
Paste your values below the line
Press "Update" then "Run"
Results in Sheet two
I have removed your conditional formationing to reduce the file size for attaching to the forum..
This thread will now be closed
If you have any new questions please start a new thread.