Identify and number Patterns

larbec · Nov 3rd 2013

I did not close this and followed your rules???

A person on this board did this for me with this so far and I need to be able to view more than 16 rows. I would like to view all the rows it finds. I have 62,000 rows on information this far and will exceed 500,000 so you can see my need to see all involved to save hours of sifting through data. I would also like a place up top near the box (column G.H)(3,4,5) another box for me to enter a new number combination to search. Presently I have to go through the data to find the first one which is also time consuming. I would like to be able to put say for example in the area I am mentioning:

2 6

4
2 8

Please forgive me in advance that I am a newbie with excel and permanently disabled and at times have problems with my memory and paralysis but I promise I will get this eventually. If someone can help me this will speed up my process substantially and help me physically too. being disabled makes most task difficult and some folks on here are so helpful and a lot smarter than I with excel. Thanks in advance

I have posted this on here also and still need some help please

http://www.excelforum.com/exce…a-mining.html#post3459666

http://www.ozgrid.com/forum/showthread.php?t=183859

**cytop** · Nov 3rd 2013

Re: Identify and number Patterns

Quote

... and followed your rules

Other people have not necessarily read your other posts... You have not indicated that you have also posted this issue on another board.

Please reply with a link to your post on ExcelForum, plus links to any other posts for this issue on other boards, if any. Then this thread will comply with the board rules.

You may have noticed a similar reaction on ExcelForum. Just about every similar board have the same rule regarding cross posts.

larbec · Nov 4th 2013

Re: Identify and number Patterns

Due to my cross posting .... That mean no one help me now?

**KjBox** · Nov 4th 2013

Re: Identify and number Patterns

Just do ask the moderators have asked, once you comply with the forum rules you will get the help you need.

Reply to this with links to all posts you have made regarding this problem, that means links to posts on this forum as well as any other forum you have used.

larbec · Nov 4th 2013

Re: Identify and number Patterns

I think this is all of them.

http://www.excelforum.com/exce…ferently.html#post3459523

http://www.excelforum.com/excel-prog...ml#post3459666

http://www.ozgrid.com/forum/showthread.php?t=183859

**StephenR** · Nov 4th 2013

Re: Identify and number Patterns

I'm not sure I follow what you are trying to do. Can you indicate some expected results?

larbec · Nov 4th 2013

Re: Identify and number Patterns

If you open the attached you will see a group of numbers in two rows. I'm trying to get a listing where all the same group of numbers are displayed (what row they are in). You can click on the button , then hilite say rows 2,3,4,5,6 2nd and 3rd column then enter. It will display 14 locations that the similar pattern is in. I need to view ALL the rows they are in not just 14. Also, it would be nice to be able to click on say "next" and it goes to the next location I will has around 500,000 rows when it's all said and done. I have to look at all these locations to gather sole statistics Hope that makes sense.

larbec · Nov 5th 2013

Re: Identify and number Patterns

Quote from StephenR;690196

I'm not sure I follow what you are trying to do. Can you indicate some expected results?

Jindon did a fantastic job creating this sheet and it does exactly what I want BUT, I would like to see more than 14 rows of information to go view. It places a box around all of them but I have to scroll down to find the others past 14. When I have these sheets full it will be a nightmare to have to go through row by row

I would also like a spot (box) to place 2 to 7 numbers that I will be searching for. Basically a place to put a set of numbers to go find

2
4
6
8
10
8
10

larbec · Nov 6th 2013

Re: Identify and number Patterns

Hmm, does this not make sense or hard to do?

apo · Nov 6th 2013

Re: Identify and number Patterns

Hi..

Maybe this..

I made it add a "x" in a dummy column next to each Match.. and using FindNext and a MsgBox.. you can go down the Range and Select each Match..

Just press the big blue button and highlight your values as you do now.. then.. after the MsgBox shows you the Matches.. press the "Next" button that is in the top row.. and press "Yes" in the resulting MsgBox if you want to continue to the next Match.

The "x"'s in the 'dummy' column are cleared after each search..

Hope that helps..

larbec · Nov 6th 2013

Re: Identify and number Patterns

This is wonderful!! THANK YOU!!! Is it possible to give me a box to place the numbers in? If you look at column C , I have to go through line by line to find the first set of numbers I am seeking which takes time. I would love to be able to net to the present button put my own new numbers in that I am looking for. Then ..... do what I normally do to search for them. Does this make sense?

Right now there is this in the box: I would like to able to change these digit s to whatever I like and search for the new set. I will need at least 7 digits (rows) in each column J and K even though sometimes I will only be searching 2 rows

**pike** · Nov 7th 2013

Re: Identify and number Patterns

Hi larbec,
Use a Filter on the columns would be the quickest and best way to show the result in one window.
This is also a method you can sustain

my take on the code to loop the first two two column values in the boardered area

Code

Option Explicit
Sub test()
    Dim myPtn As Range, r As Range, x, myTxt, mymatch As Range, TestArea
    Dim ff As String, i As Long, y, flg As Boolean, myAreas As Areas
    Columns("c:e").Borders.LineStyle = xlNone
    
   For Each TestArea In Range("J4:J8")
      If TestArea.Value <> "" Or TestArea.Offset(, 1).Value <> "" Then
    Set myAreas = Union(Range(TestArea.Address), Range(TestArea.Offset(, 1).Address)).Areas
   ' Set myAreas = Application.InputBox("Select the pattern range(s)", Type:=8).Areas
    
    
    For Each myPtn In myAreas
        myTxt = myPtn(1).Value
        Set r = Columns(3).Find(myTxt, , , 1)
        If Not r Is Nothing Then
            ff = r.Address
            Do
                x = Evaluate(r.Resize(myPtn.Rows.Count, 2).Address & "=" & myPtn.Address)
                For i = 1 To 2
                    y = Filter(Application.Transpose(Application.Index(x, 0, i)), False)
                    If UBound(y) <> -1 Then flg = True: Exit For
                Next
                If Not flg Then
                    If mymatch Is Nothing Then
                        Set mymatch = r.Resize(myPtn.Rows.Count, 3)
                    Else
                        Set mymatch = Union(mymatch, r.Resize(myPtn.Rows.Count, 3))
                    End If
                    r.Resize(myPtn.Rows.Count, 3).BorderAround Weight:=xlThick
                End If
                Set r = Columns(3).FindNext(r): flg = False
            Loop Until ff = r.Address
        End If
    Next
   
    If mymatch Is Nothing Then
           MsgBox "No Match", , "Not Found"
           Else
           MsgBox Replace(mymatch.Address, ",", vbLf), , "Found"
       End If
    If Not mymatch Is Nothing Then mymatch.Select
  End If
  Next TestArea
 End Sub

Display More

larbec · Nov 7th 2013

Re: Identify and number Patterns

Woah ... Lol. I'm a newbie in excel VB I have no clue lol BUT want to learn so so so bad. Can you PLEASE give me step by step instructions where to place this code and what I need to do? I have excel 2010. I know enough to be dangerous lol. Thank you so so so very much for your valuable time

**pike** · Nov 8th 2013

Re: Identify and number Patterns

Hi,
If you are just starting to learn vba I highly recommend that you Watch the training videos tutorials in the link at the top of this page.( it explains how to add code and where)
In the interim you are better off using the filter (how to in the training videos) in two columns to get your desired result.
To explain jindons code would be to confusing to you if you don't have the basics or even a intermediate level of knowledge of vba.
Only way to learn is to develop your own code that we can help with. Filter is the way to go also watch how to record macros. Other than the advice To use a filter and watch the videos I cant help you in this thread

larbec · Nov 8th 2013

Re: Identify and number Patterns

@ Pike,

I agree with our statement/ However, I am using the filters now and it takes forever. I am learning the VBA and very informative so far. I was only asking for now where to place the code so I can start using it Thanks for the reply

**pike** · Nov 8th 2013

Re: Identify and number Patterns

Hi
To open the script editor use the Function key commands "Alt" and "F11"
copy the code and paste it into the module

**pike** · Nov 8th 2013

Re: Identify and number Patterns

also some good help here http://www.contextures.com/xlvba01.html#videoreg

larbec · Nov 8th 2013

Re: Identify and number Patterns

pike,

Thanks Pike, I am out for the evening with my wife and will check this out tonight. Thank you so much for your time and help. I REALLY do appreciate this

**pike** · Nov 10th 2013

Re: Identify and number Patterns

Hi larbec,
This code is from a recorded macro. Add some very basic house keeping and the code becomes...

Code

Option Explicit
Sub Macro1()
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
                MsgBox "Filter for Values: Column B " & .Parent.Cells(Test_Row, 3).Value & " Column C " & .Parent.Cells(Test_Row, 4).Value, , "Click to advance Test Area"
                .AutoFilter
            End With
        Next
    End With
End Sub

Display More

forum.ozgrid.com/index.php?attachment/57360/

This way all the results are filtered in one sheet

larbec · Nov 10th 2013

Re: Identify and number Patterns

Wow! Thanks Pike! I've been watching the training videos. So much to learn but really cool. Again THANK YOU!

Identify and number Patterns

Participate now!

Share