Identify and number Patterns

larbec · Nov 10th 2013

Re: Identify and number Patterns

I am not sure what it is doing but when I place a different number in the test section it does not bring up the numbers I place i there. It brings up 1 number and then ask the questions as in the pic. I am confused LOL

**pike** · Nov 10th 2013

Re: Identify and number Patterns

Hi larbec
when you run the macro it loops the rows in the test area for columns B and C

larbec · Nov 18th 2013

Re: Identify and number Patterns

The last thing I want to do is create more work or post a duplicate question. Is it possible to just add 3 more columns to this formula so I can search for 4 sets of numbers?http://http://www.ozgrid.com/forum/showthread.php?t=184200&p=691986#post691986

larbec · Nov 20th 2013

Re: Identify and number Patterns

Is this not possible folks? Thanks

**pike** · Nov 20th 2013

Re: Identify and number Patterns

Hi larbec,
For more columns E and F see the code below

Code

Dim LastMasterRow As Long
    Dim Test_Row As Long
    With Sheets("Filter")
        LastMasterRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        .Rows("7:7").AutoFilter
        For Test_Row = 2 To 6
            With .Range("B7:E" & LastMasterRow)
                .AutoFilter Field:=2, Criteria1:=.Parent.Cells(Test_Row, 3).Value
                .AutoFilter Field:=3, Criteria1:=.Parent.Cells(Test_Row, 4).Value
                 .AutoFilter Field:=4, Criteria1:=.Parent.Cells(Test_Row, 5).Value ' code added for column E
                 .AutoFilter Field:=5, Criteria1:=.Parent.Cells(Test_Row, 6).Value ' code added for column f
                MsgBox "Column C filter Value " & .Parent.Cells(Test_Row, 3).Value & Chr(10) _
                & "Column D filter Value " & .Parent.Cells(Test_Row, 4).Value & Chr(10) _
                & "Column E filter Value " & .Parent.Cells(Test_Row, 5).Value & Chr(10) _
                & "Column F filter Value " & .Parent.Cells(Test_Row, 6).Value, , "Click to advance Test Area"
                .AutoFilter
            End With
        Next
    End With

Display More

larbec · Nov 20th 2013

Re: Identify and number Patterns

Thanks Pike, I tried loading this in and must be something wrong UG, do you mind putting the sheet for me so I can sort all 4 columns BCDF. I will have a little over 500,000 rows of information. Thank you so much!!!

larbec · Nov 20th 2013

Re: Identify and number Patterns

When I add the code it only works 3 cells and it wipes out one complete cell and makes it have only 3 columns. I also notied that it finds any and all the numbers I place in a line to look at. What I mean is, if i want to find the following

1357
1347
1247

it will bring up
357
134
247

what I need is the entire block of cells. ALL as a group
1357
1347
1247
etc.....

**pike** · Nov 21st 2013

Re: Identify and number Patterns

Hi larbec,

try ..

Code

Option Explicit
Sub Macro1()
    Dim LastMasterRow As Long
    Dim Test_Row As Long
    With Sheets("Sheet1")
        LastMasterRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        .Rows("13:13").AutoFilter
        For Test_Row = 3 To 11
            With .Range("B13:E" & LastMasterRow)
                .AutoFilter Field:=1, Criteria1:=.Parent.Cells(Test_Row, 2).Value
                .AutoFilter Field:=2, Criteria1:=.Parent.Cells(Test_Row, 3).Value
                 .AutoFilter Field:=3, Criteria1:=.Parent.Cells(Test_Row, 4).Value
                 .AutoFilter Field:=4, Criteria1:=.Parent.Cells(Test_Row, 5).Value
                MsgBox "Column B filter Value " & .Parent.Cells(Test_Row, 2).Value & Chr(10) _
                & "Column C filter Value " & .Parent.Cells(Test_Row, 3).Value & Chr(10) _
                & "Column D filter Value " & .Parent.Cells(Test_Row, 4).Value & Chr(10) _
                & "Column E filter Value " & .Parent.Cells(Test_Row, 5).Value & Chr(10) _
                & "Row Count " & (.SpecialCells(xlCellTypeVisible).Count - 4) / 4, , "Click to advance Test Area"
                .AutoFilter
            End With
        Next
    End With
End Sub

Display More

larbec · Nov 21st 2013

Re: Identify and number Patterns

pike

Almost there Pike, It shows only 1 4 digit number at a time and I need to be able to find them in a group of numbers up to say 8 rows. If I put in the following numbers in rows 5 through 8 it only finds one set of numbers at a time ie 2568 or 1467. I need it to find them all in that exact order as displayed

2568
2467
1467
1367

Is there a way to have it stop at the next set of numbers like the 1st one you did for me? You are simply a genius Pike!!!!! making my job easier that's for sure

Here is a short video on how it looks and what I am looking for

External Content www.youtube.com

Content embedded from external sources will not be displayed without your consent.

Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

**pike** · Nov 22nd 2013

Re: Identify and number Patterns

Hello,
Away for a few days and only have phone access.. I will do the one set on numbers at a time but it will be all the results

larbec · Nov 22nd 2013

Re: Identify and number Patterns

I understand but I can't view all of them and once. Is it possible to view all of them at once? I hit okay there gone It's only allowing me to view maybe 20 sets if rows. The last MACRO you wrote like this (kinda) stops in the run and allows me see all of them one at a time even though it may bring up 15 or so in gray box as a list This one groups all of them yes but I can't view all of them. It is finding all the numbers but separately I was hoping to view them as a whole group plus as explained I can't view them all because I can't scroll down unless I hit ok and then there gone

Were you able to view the short video i uploaded? That explains what I'm talking bout. Be safe on your trip and once again thank you so very much for your help

**pike** · Nov 22nd 2013

Re: Identify and number Patterns

No The Vedic is private or errors .. What have you tried ? I will help but wont do it all .. Unless you try learning the code you will know its limits .. You have quite a few option

larbec · Nov 23rd 2013

Re: Identify and number Patterns

I will give it a look at Pike, thanks for allowing me to take a shot at it and your right, I can not learn having someone else doing it. BTW, I made the video not private, that was my first attempt at the video too LOL

larbec · Nov 23rd 2013

Re: Identify and number Patterns

So sorry Pike , this is over my head but will read up and try a few things but I really do not even know what command to use. I want to group together the auto filter field 1,2,3 and 4 I think

larbec · Nov 23rd 2013

Re: Identify and number Patterns

Okay, I've been working on this for hours and tried several things but when I try to save them or run it licks up on me or won't key me save it. I've tried this below and failed. I do not know what to call what I'm trying to do and my classes at the Jr College do not start till next semester. I am trying )-:

Code

Dim LastMasterRow As Long 
Dim Test_Row As Long 
With Sheets("Filter") 
    LastMasterRow = .Cells(.Rows.Count, 2).End(xlUp).Row 
    .Rows("7:7").AutoFilter 
    For Test_Row = 2 To 6 
        With .Range("B7:E" & LastMasterRow) 
            .AutoFilter Field:=2, Criteria1:=.Parent.Cells(Test_Row, 3 and field 4).Value 
            .AutoFilter Field:=3, Criteria1:=.Parent.Cells(Test_Row, 4 and field 5).Value 
            .AutoFilter Field:=4, Criteria1:=.Parent.Cells(Test_Row, 5 and field 6).Value ' code added for column E
            .AutoFilter Field:=5, Criteria1:=.Parent.Cells(Test_Row, 6 and field 7).Value ' code added for column f
            MsgBox "Column C filter Value " & .Parent.Cells(Test_Row, 3).Value & Chr(10) _ 
            & "Column D filter Value " & .Parent.Cells(Test_Row, 4).Value & Chr(10) _ 
            & "Column E filter Value " & .Parent.Cells(Test_Row, 5).Value & Chr(10) _ 
            & "Column F filter Value " & .Parent.Cells(Test_Row, 6).Value, , "Click once to advance down 10 rows twice to advance Test Area" 
            .AutoFilter 
        End With 
    Next 
End With

Display More

**pike** · Nov 24th 2013

Re: Identify and number Patterns

Hi
there is plenty of good notes in the vba help
in the module highlight "AutoFilter" and click the F1 Function button

**pike** · Nov 24th 2013

Re: Identify and number Patterns

Hi
there is plenty of good notes in the vba help
in the module highlight "AutoFilter" and click the F1 Function button
The limitation will be with the loop .. and a break in the code to to change the data ..VBA doesnt work that way.

Just watched your Video ..so you would like to find the Pattern , of four numbers across the row/columns, so you can edit the next five rows?

larbec · Nov 24th 2013

Re: Identify and number Patterns

I would like to find as example all these in one search. So I would place all these numbers in the rows 1-7. click on the search button and it find all the different places that these are. But, it has to allow me to stop the scan and be able to look at the rest through out the 500,000 rows. Currently as you saw in the video, I can not scroll down nor does it find the entire group of numbers, it only finds one number at a time. I am still Reading up on the VBA's and THANK YOU!!!

[ATTACH=CONFIG]57620[/ATTACH]

**pike** · Nov 25th 2013

Re: Identify and number Patterns

Hi larbec
have a look at the workbook
all you need is a simple search sub

Code

Sub simple_search()
    Cells.Find(What:=Range("B3").Value & "^" & Range("C3").Value & "^" & Range("D3").Value & "^" & Range("E3").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
   
End Sub

and a worksheet formula
=B14&"^"&C14&"^"&D14&"^"&E14
some where in the sheet

larbec · Nov 25th 2013

Re: Identify and number Patterns

I tried adding the &B14&"^"&C14&"^"&D14&"^"&E14 coupled with the =B9&"^"&C9&"^"&D9&"^"&E9 &N15 and that did not do it. I also tried to add additional code without any luck. I understand what this is doing bt how would I add an "and" statement so to speak or am I heading in the wrong direction?

Identify and number Patterns

Participate now!

Share