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?
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?
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·| Read the rules
Use code tags to [code]enclose your code![/code]
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.
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?
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
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
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).
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
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.
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:
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 :-).
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.
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.
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?
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 » ! ◄ ◄
Last edited by Marc L; 05-16-2020 at 10:00 PM.
Reason: unused variable removed from Dim codeline …
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 » ! ◄ ◄
Last edited by Marc L; 05-16-2020 at 10:08 PM.
Reason: unused variable removed from Dim codeline …
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 ..
Bookmarks