Search for similar Patterns in an Excel Sheet

1. 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. Re: Search for similar Patterns in an Excel Sheet

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

3. 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. 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.

5. 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.

Originally Posted by 6StringJazzer
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. 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. 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?

Originally Posted by Kaper
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. 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. 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. 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:

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.

11. 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.

Originally Posted by Kaper
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:

12. 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.``

13. Re: Search for similar Patterns in an Excel Sheet

I got an error running the Macro

Attachment 677948

14. Re: Search for similar Patterns in an Excel Sheet

Originally Posted by Energy48
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. Re: Search for similar Patterns in an Excel Sheet

That was a mistake. It should be 5 rows in both cases.
Originally Posted by Akuini
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. Re: Search for similar Patterns in an Excel Sheet

Originally Posted by Kaper
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?

17. Re: Search for similar Patterns in an Excel Sheet

Attached is the same pattern using CONCATENATE

18. 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 P = 29         Dim Rg(1) As Range, C%, 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(0) Is Nothing Then Exit Sub         C = Rg(0).Areas.Count:  If C = 1 Then Beep: Erase Rg: Exit Sub         ReDim N(1 To C, 1)     For R = 1 To C         If Rg(0).Areas(R).Count > 1 Then Beep: Erase Rg: Exit Sub         If R > 1 Then _         N(R, 0) = Rg(0).Areas(R).Row - Rg(0).Areas(1).Row: If N(R, 0) <= N(R - 1, 0) Then Beep: Erase Rg: Exit Sub         N(R, 1) = Rg(0).Areas(R).Column     Next         Application.ScreenUpdating = False     With Me.UsedRange.Rows           H = Application.Index(.Item(1).Columns("A:Z").Value2, 0)           Set Rg(0) = .Columns(N(1, 1)).Find(H(N(1, 1)), .Cells(N(1, 1)))         While Rg(0).Row > 1           Set Rg(1) = Union(Rg(0), .Cells(Rg(0).Row, P))             For R = 2 To C                 With .Item(Rg(0).Row + N(R, 0))                   If .Cells(N(R, 1)).Value2 = H(N(R, 1)) Then Set Rg(1) = Union(Rg(1), .Cells(N(R, 1)), .Cells(P)) _                                                          Else Exit For                 End With             Next             If R > C Then                 Rg(1).Interior.ColorIndex = 40                 For R = 1 To C:  Rg(1).Areas(R * 2).Value2 = H(N(R, 1)):  Next             End If           Set Rg(0) = .Columns(N(1, 1)).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 » ! ◄ ◄

19. The 'blank spaces exact match' variation …

According to post 9 …

Originally Posted by Energy48
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 P = 29         Dim Rg(1) As Range, C%, 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(0) Is Nothing Then Exit Sub         C = Rg(0).Areas.Count:  If C = 1 Then Beep: Erase Rg: Exit Sub         ReDim N(1 To C, 1)     For R = 1 To C             If Rg(0).Areas(R).Count > 1 Then Beep: Erase Rg: Exit Sub         If R > 1 Then             If Cells(Rg(0).Areas(1).Row, Rg(0).Areas(R).Column).End(xlDown).Row <> Rg(0).Areas(R).Row Then _                 Beep: Erase Rg: Exit Sub             N(R, 0) = Rg(0).Areas(R).Row - Rg(0).Areas(1).Row         End If             N(R, 1) = Rg(0).Areas(R).Column     Next         Application.ScreenUpdating = False     With Me.UsedRange.Rows           H = Application.Index(.Item(1).Columns("A:Z").Value2, 0)           Set Rg(0) = .Columns(N(1, 1)).Find(H(N(1, 1)), .Cells(N(1, 1)))         While Rg(0).Row > 1           Set Rg(1) = Union(Rg(0), .Cells(Rg(0).Row, P))             For R = 2 To C                 If .Cells(Rg(0).Row, N(R, 1)).End(xlDown).Row - Rg(0).Row <> N(R, 0) Then Exit For                 With .Item(Rg(0).Row + N(R, 0)):  Set Rg(1) = Union(Rg(1), .Cells(N(R, 1)), .Cells(P)):  End With             Next             If R > C Then                 Rg(1).Interior.ColorIndex = 40                 For R = 1 To C:  Rg(1).Areas(R * 2).Value2 = H(N(R, 1)):  Next             End If           Set Rg(0) = .Columns(N(1, 1)).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 » !

20. 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.

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

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