+ Reply to Thread
Results 1 to 20 of 20

Search for similar Patterns in an Excel Sheet

  1. #1
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    19

    Search for similar Patterns in an Excel Sheet

    Is it possible to search for recurring patterns in an excel sheet?

    As you can see from the attached, the pattern on the top half between Rows 4 through 20 of columns "EGOK" is repeated in the bottom half of the sheet from rows 895 through 910.

    Is there a formula to do a search for such recurring patterns in an excel sheet?

    RepeatingPattern.png

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Arrow Re: Search for similar Patterns in an Excel Sheet


    If you are looking for a formula so you posted in the wrong forum

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,457

    Re: Search for similar Patterns in an Excel Sheet

    You can create four conditional formatting rules (without VBA), one for each letter that you are looking for relative the other letters. In your example you would use these rules. The only difference is in the "applies to" ranges, which have to be different rows. Adjust the ending row to match your actual expectation.

    Applies to E1:E10001
    =AND(E1="E",G8="G",K20="K",O14="O")

    Applies to G8:G10008
    =AND(E1="E",G8="G",K20="K",O14="O")

    Applies to K20:K10020
    =AND(E1="E",G8="G",K20="K",O14="O")

    Applies to O14:O10014
    =AND(E1="E",G8="G",K20="K",O14="O")

    However, this has to be custom-made for each pattern. A general-purpose solution would be much more complicated. What do you need?

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,477

    Re: Search for similar Patterns in an Excel Sheet

    But why not EGNK (N is also both row 14 and 905) Or may be EGND or EGOD (with D in rows 20 and 910) , VGOK (etc) V being in rows 5 and 896


    Surely more detailed explanation would be useful. And note that after just a brief view more "patterns" were spotted with ***** eye. But excel would not give up after just few of them, (unless instructed to do so) and will search them probably for ages, as I'd expect zillions of similar patterns in the file.
    Best Regards,

    Kaper

  5. #5
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Search for similar Patterns in an Excel Sheet

    Thank you very much for your response. But IMO a general purpose solution will be better.

    Quote Originally Posted by 6StringJazzer View Post
    You can create four conditional formatting rules (without VBA), one for each letter that you are looking for relative the other letters. In your example you would use these rules. The only difference is in the "applies to" ranges, which have to be different rows. Adjust the ending row to match your actual expectation.

    Applies to E1:E10001
    =AND(E1="E",G8="G",K20="K",O14="O")

    Applies to G8:G10008
    =AND(E1="E",G8="G",K20="K",O14="O")

    Applies to K20:K10020
    =AND(E1="E",G8="G",K20="K",O14="O")

    Applies to O14:O10014
    =AND(E1="E",G8="G",K20="K",O14="O")

    However, this has to be custom-made for each pattern. A general-purpose solution would be much more complicated. What do you need?

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,477

    Re: Search for similar Patterns in an Excel Sheet

    Have you also seen my comments (see post #4).

    How do you define "pattern" because your example was not specific enough (at least in my opinion) . As for example - see also hint in a yellow banner near the page top

  7. #7
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Search for similar Patterns in an Excel Sheet

    What I am basically asking for is how to design a CUSTOM SEARCH based on the VALUES I put in a search box. Does that help?

    Quote Originally Posted by Kaper View Post
    Have you also seen my comments (see post #4).

    How do you define "pattern" because your example was not specific enough (at least in my opinion) . As for example - see also hint in a yellow banner near the page top

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,477

    Re: Search for similar Patterns in an Excel Sheet

    Sure, It gives some insight.

    Would there be always 4 values?
    What is the max distance (rows) between 1st and last to treat them as pattern.

    Let me say this again: Probably a hint in a yellow banner near the top (may be except the part about 10-20 rows, but anyway not thousands of them) would be useful. But note that it is about a workbook, not a screenshot .

    By the way - what would be the real estimated rows count?

    And final comment - we can see previous posts, so it's usually enough just to "reply", not "reply with quote" (if it's not obvious to whom you answer - partial quote would be more than enough).

  9. #9
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Search for similar Patterns in an Excel Sheet

    The number of Values will vary but the minimum should be two. The row count would be that highlighted by the User.

    What I am thinking of is to create a search box where the User inputs the initial Values by click on them in the sheet + the number of blank spaces between them . Excels then looks through the sheet to find values that are an exact match

  10. #10
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,477

    Re: Search for similar Patterns in an Excel Sheet

    I'm just about to quit this conversation, but as I think I know how to do it (and - just for curiosity - wonder if my idea will be effective enough to deal with more than 2-3 elements patters, becasue I'm sure that for few thousand rows of data and 2 elemments and it will be effective enough, 3 elements - shall be affective enough too but for 4 or more ?), let me try the last time:

    Please read the yellow banner at the top of the page.

    Extended version:

    Will you please attach a small sample Excel workbook (you already did it - but published only a screenshot)
    Please don't attach only a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If there are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, empty cells between data, mixed text/numbers, merged cells, some important cells filled with content by macro/manually/by formula, etc. - please show them all, or at least indicate in text). The use of unrepresentative data and formats is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

    3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

    To attach an Excel file you have to do the following (paperclip icon does not work!): Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.
    Last edited by Kaper; 05-15-2020 at 05:51 AM.

  11. #11
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Search for similar Patterns in an Excel Sheet

    Attached is the Excel sheet I am working on.(hope it uploaded) Basically I am looking to search for "EGOK" in the sheet and it should find the corresponding pattern with the right spacing on the sheet.

    "EGOK" is the initial INPUT to base the search on."EGOK" everywhere in the sheet so the correct pattern one should match the first (input) as shown in Column AC.


    Quote Originally Posted by Kaper View Post
    I'm just about to quit this conversation, but as I think I know how to do it (and - just for curiosity - wonder if my idea will be effective enough to deal with more than 2-3 elements patters, becasue I'm sure that for few thousand rows of data and 2 elemments and it will be effective enough, 3 elements - shall be affective enough too but for 4 or more ?), let me try the last time:
    Attached Files Attached Files
    Last edited by Energy48; 05-15-2020 at 08:48 PM.

  12. #12
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,477

    Re: Search for similar Patterns in an Excel Sheet

    See attached file.
    Allow macros and rum (Alt+F8) macro search_patterns

    If we shall limit ourselves to just finding only topmost match then code could be much shorter - could end just after the first Next j.
    Painting cells interiors in column AC is made anyway following this idea.

    But I made a macro to explore a but deeper the dataset and there is a kind of executive summary about the patterns found :-).

    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Search for similar Patterns in an Excel Sheet

    I got an error running the Macro

    Attachment 677948

  14. #14
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    791

    Re: Search for similar Patterns in an Excel Sheet

    Quote Originally Posted by Energy48 View Post
    where the User inputs the initial Values by click on them in the sheet + the number of blank spaces between them
    Basically I am looking to search for "EGOK" in the sheet and it should find the corresponding pattern with the right spacing on the sheet.
    1. What do you mean by "space"? Is it row?
    2. So, the number of row between the "element/letter" must be the same? but in your example in post #1 in the first EGOK, there are 5 rows between O & K, but only 4 rows in the second EGOK.

  15. #15
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Search for similar Patterns in an Excel Sheet

    That was a mistake. It should be 5 rows in both cases.
    Quote Originally Posted by Akuini View Post
    1. What do you mean by "space"? Is it row?
    2. So, the number of row between the "element/letter" must be the same? but in your example in post #1 in the first EGOK, there are 5 rows between O & K, but only 4 rows in the second EGOK.

  16. #16
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Search for similar Patterns in an Excel Sheet

    Quote Originally Posted by Kaper View Post
    See attached file.
    Allow macros and rum (Alt+F8) macro search_patterns

    If we shall limit ourselves to just finding only topmost match then code could be much shorter - could end just after the first Next j.
    Painting cells interiors in column AC is made anyway following this idea.

    [/CODE]
    I am thinking I could do it using =CONCATENATE. But what will make it different from using CONCATENATE is to Highlight or paint ONLY the pairs that match. What do you think?
    Last edited by Energy48; 05-16-2020 at 10:01 AM.

  17. #17
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    19

    Re: Search for similar Patterns in an Excel Sheet

    Attached is the same pattern using CONCATENATE
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Cool Try this demonstration !


    According to post #11 attachment as a VBA beginner starter to paste to the Sheet1 worksheet module :

    PHP Code: 
    Sub Demo1()
            Const 
    29
            Dim Rg
    (1) As RangeC%, N%(), R%, H()
            
    Me.UsedRange.Columns(P).Offset(1).ClearContents
            Me
    .UsedRange.Interior.ColorIndex xlNone
            On Error Resume Next
            Set Rg
    (0) = Application.InputBox(vbLf "Select each cell in the worksheet row by row" vbLf _
                                             vbLf 
    "(Ctrl Click for the second and more)""Pattern choice"Type:=8)
            
    On Error GoTo 0
            
    If Rg(0Is Nothing Then Exit Sub
            C 
    Rg(0).Areas.Count:  If 1 Then BeepErase Rg: Exit Sub
            ReDim N
    (1 To C1)
        For 
    1 To C
            
    If Rg(0).Areas(R).Count 1 Then BeepErase Rg: Exit Sub
            
    If 1 Then _
            N
    (R0) = Rg(0).Areas(R).Row Rg(0).Areas(1).Row: If N(R0) <= N(10Then BeepErase Rg: Exit Sub
            N
    (R1) = Rg(0).Areas(R).Column
        Next
            Application
    .ScreenUpdating False
        With Me
    .UsedRange.Rows
              H 
    Application.Index(.Item(1).Columns("A:Z").Value20)
              
    Set Rg(0) = .Columns(N(11)).Find(H(N(11)), .Cells(N(11)))
            While 
    Rg(0).Row 1
              Set Rg
    (1) = Union(Rg(0), .Cells(Rg(0).RowP))
                For 
    2 To C
                    With 
    .Item(Rg(0).Row N(R0))
                      If .
    Cells(N(R1)).Value2 H(N(R1)) Then Set Rg(1) = Union(Rg(1), .Cells(N(R1)), .Cells(P)) _
                                                             
    Else Exit For
                    
    End With
                Next
                
    If C Then
                    Rg
    (1).Interior.ColorIndex 40
                    
    For 1 To C:  Rg(1).Areas(2).Value2 H(N(R1)):  Next
                End 
    If
              
    Set Rg(0) = .Columns(N(11)).FindNext(Rg(0))
            
    Wend
        End With
            Application
    .ScreenUpdating True
            Erase Rg
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon Add Reputation ! ◄ ◄
    Last edited by Marc L; 05-16-2020 at 10:00 PM. Reason: unused variable removed from Dim codeline

  19. #19
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Lightbulb The 'blank spaces exact match' variation


    According to post 9

    Quote Originally Posted by Energy48 View Post
    the User inputs the initial Values by click on them in the sheet + the number of blank spaces between them . Excels then looks through the sheet to find values that are an exact match
    another VBA beginner starter to paste to the Sheet1 worksheet module of post #11 attachment :

    PHP Code: 
    Sub Demo1v()
            Const 
    29
            Dim Rg
    (1) As RangeC%, N%(), R%, H()
            
    Me.UsedRange.Columns(P).Offset(1).ClearContents
            Me
    .UsedRange.Interior.ColorIndex xlNone
            On Error Resume Next
            Set Rg
    (0) = Application.InputBox(vbLf "Select each cell in the worksheet row by row" vbLf _
                                             vbLf 
    "(Ctrl Click for the second and more)""Pattern choice"Type:=8)
            
    On Error GoTo 0
            
    If Rg(0Is Nothing Then Exit Sub
            C 
    Rg(0).Areas.Count:  If 1 Then BeepErase Rg: Exit Sub
            ReDim N
    (1 To C1)
        For 
    1 To C
                
    If Rg(0).Areas(R).Count 1 Then BeepErase Rg: Exit Sub
            
    If 1 Then
                
    If Cells(Rg(0).Areas(1).RowRg(0).Areas(R).Column).End(xlDown).Row <> Rg(0).Areas(R).Row Then _
                    Beep
    Erase Rg: Exit Sub
                N
    (R0) = Rg(0).Areas(R).Row Rg(0).Areas(1).Row
            End 
    If
                
    N(R1) = Rg(0).Areas(R).Column
        Next
            Application
    .ScreenUpdating False
        With Me
    .UsedRange.Rows
              H 
    Application.Index(.Item(1).Columns("A:Z").Value20)
              
    Set Rg(0) = .Columns(N(11)).Find(H(N(11)), .Cells(N(11)))
            While 
    Rg(0).Row 1
              Set Rg
    (1) = Union(Rg(0), .Cells(Rg(0).RowP))
                For 
    2 To C
                    
    If .Cells(Rg(0).RowN(R1)).End(xlDown).Row Rg(0).Row <> N(R0Then Exit For
                    
    With .Item(Rg(0).Row N(R0)):  Set Rg(1) = Union(Rg(1), .Cells(N(R1)), .Cells(P)):  End With
                Next
                
    If C Then
                    Rg
    (1).Interior.ColorIndex 40
                    
    For 1 To C:  Rg(1).Areas(2).Value2 H(N(R1)):  Next
                End 
    If
              
    Set Rg(0) = .Columns(N(11)).FindNext(Rg(0))
            
    Wend
        End With
            Application
    .ScreenUpdating True
            Erase Rg
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 05-16-2020 at 10:08 PM. Reason: unused variable removed from Dim codeline

  20. #20
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,477

    Re: Search for similar Patterns in an Excel Sheet

    as for the encountered error (no attachment to post #13 visible) - I suspect wrong form of the input string.
    It shall be just (and only) letters like
    EGOK
    or
    ABCD
    or
    XHLRK
    etc.

    I have added information about it in the prompt ..
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. it is possible to create a search algorithm for defined patterns * no excel
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2018, 02:30 PM
  2. You can create a macro with the function of an algorithm Search patterns?
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-24-2016, 10:06 AM
  3. Search and Replace patterns of strings with a RULE with any new message received.
    By sev7en in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 05-04-2015, 09:37 AM
  4. [SOLVED] How to get this and 250+ similar Word documents in one excel sheet
    By waavn in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 09-28-2014, 01:26 PM
  5. Replies: 17
    Last Post: 08-18-2013, 03:29 AM
  6. Macro to search for patterns in a range of dates
    By bsweet0us in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2011, 10:49 PM
  7. Recognizing Input Patterns From Sheet to Sheet with linked cells
    By mastermezz in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-13-2010, 10:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1