Find patterns in data points

Ghris

Board Regular
Joined
Mar 17, 2012
Messages
73
Hello,

I wonder if there is a way to check for a certain pattern in a list of data points.

This image might clarify what I am looking for. I need a way to check if the pattern exists (and where) in the data points.
excelproblem.jpg


I have tried with vlookup but only got it to work with finding 1 number.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Ghris,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


What you are requesting can be done with a macro.

You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Thanks hiker95.

I uploaded my workbook using dropbox. Is that okay?

Link:https://www.dropbox.com/s/j3l020akd04wymn/Analysis.xlsx?dl=0

My excel version is 2013 and windows version is 7. Working on a normal PC.

In the uploaded file I tried to show what my intentions are. A dataset, a pattern, and finally a column with the location of that pattern in the data set. (Note; the locations given as a result are fictional)
 
Upvote 0
Ghris,

I now see from that last post that the scale of your data probably makes the following unviable however I will post it anyway.

The formula will count the matches if you wish and conditional formatting will indicate the start cell of each match.


Excel Workbook
ABCDE
1Data PointPatternMatches
2162
351
491
528
66
74
85
97
108
115
121
136
141
151
168
176
189
194
206
213
224
235
247
259
266
271
281
298
309
312
328
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =AND(A2=D$2,A3=D$3,A4=D$4,A5=D$5)Abc


You will be in good hands with Hiker for a vb. solution.
 
Upvote 0
Thanks a lot Snakehips. I believe that for this dataset, this solution is indeed not viable. But this surely learned me a new trick :) Thanks!
 
Upvote 0
Ghris,

Not a total waste of time then;)

However, this may well be.....
It takes a few seconds to calculate but it's certainly good for finding 6 or so matches in 200,000 rows of data.


Excel 2007
ABCDEFGHIJKL
1136.19
21.72Look for the following pattern:Result:Matches
31.061.77The pattern occurs at this row(s):6
41.093.1916
56.552.99700
62.131.1560000
72.192.5998696
81173.541.0998853
92.141.75197386
102.234.53
1111.511.03
121.473.74
131.88
149.15
1586.81
161.77
173.19
182.99
191.15
202.59
211.09
221.75
234.53
241.03
253.74
265.18
275.41
Sheet1
Cell Formulas
RangeFormula
L3=SUMPRODUCT((A1:A200000=D$3)*(A2:A200001=D$4)*(A3:A200002=D$5)*(A4:A200003=D$6)*(A5:A200004=D$7)*(A6:A200005=D$8)*(A7:A200006=D$9)*(A8:A200007=D$10)*(A9:A200008=D$11)*(A10:A200009=D$12))
H4{=IF(ROW()>3+L$3,"",(MATCH(LARGE(ROW(A$1:A$200000)*(A$1:A$200000=D$3)*(A$2:A$200001=D$4)*(A$3:A$200002=D$5)*(A$4:A$200003=D$6)*(A$5:A$200004=D$7)*(A$6:A$200005=D$8)*(A$7:A$200006=D$9)*(A$8:A$200007=D$10)*(A$9:A$200008=D$11)*(A$10:A$200009=D$12),L$3-ROW()+4),ROW(A$1:A$200000)*(A$1:A$200000=D$3)*(A$2:A$200001=D$4)*(A$3:A$200002=D$5)*(A$4:A$200003=D$6)*(A$5:A$200004=D$7)*(A$6:A$200005=D$8)*(A$7:A$200006=D$9)*(A$8:A$200007=D$10)*(A$9:A$200008=D$11)*(A$10:A$200009=D$12),0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

No response yet for vba solution.
There are a lot of zeros in your sample data column A.

It may help someone determine an approach if you can clarify....
What is a realistic max for rows of data to be tested?
Will the pattern to test always be 10 values or variable?
If variable, what max number of values?
Is there a sensible maximum for number of matches likely?
 
Last edited:
Upvote 0
Ghris,

I just finished the below macro, and, then I saw your link to your actual workbook.

I will address the new raw data in a little while.


The following is based on your posted graphic/text image.

Sample raw data:


Excel 2007
ABCDE
1Data pointsPattern
216
351
491
528
66
74
85
97
108
115
121
136
141
151
168
176
189
194
206
213
224
236
249
258
267
275
282
29
Sheet1


After the macro:


Excel 2007
ABCDE
1Data pointsPatternBegins in:
216A13
351
491
528
66
74
85
97
108
115
121
136
141
151
168
176
189
194
206
213
224
236
249
258
267
275
282
29
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, including the Option Base 1 code line.
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Base 1
Sub FindPatterns()
' hiker95, 11/23/2014, ME820043
Dim p As Variant, o As Variant
Dim i As Long, j As Long
Dim lra As Long, lrp As Long
Dim a As Range
Dim n As Long, nc As Long, sr As Long, nr As Long, pc As Long
Columns(5).ClearContents
Cells(1, 5) = "Begins in:"
n = Application.CountIf(Columns(1), Cells(2, 4).Value)
If n = 0 Then
  MsgBox ("There are no '" & Cells(2, 4).Value & "' values in column A - macro terminated!")
  Exit Sub
End If
Application.ScreenUpdating = False
ReDim o(1 To n, 1 To 1)
lra = Cells(Rows.Count, 1).End(xlUp).Row
lrp = Cells(Rows.Count, 4).End(xlUp).Row
p = Range("D2:D" & lrp)
sr = 1
For nc = 1 To n
  Set a = Range("A" & sr & ":A" & lra).Find(Range("D2").Value, LookAt:=xlWhole)
  If Not a Is Nothing Then
    pc = 1: nr = a.Row
    For i = 2 To UBound(p, 1)
      nr = nr + 1
      If nr > lra Then GoTo MyFinish
      If Cells(nr, 1) = p(i, 1) Then
        pc = pc + 1
      End If
    Next i
    If pc = UBound(p, 1) Then
      j = j + 1
      o(j, 1) = "A" & a.Row
    End If
  End If
  sr = a.Row
Next nc
MyFinish:
Cells(2, 5).Resize(, UBound(o, 2)).Value = o
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindPatterns macro.
 
Upvote 0
The following macro will highlight, in yellow, each and every location of the pattern within the set of data points...
Code:
Sub FindPattern()
  Dim X As Long, Commas As Long, Rw As Long, PatLen As Long
  Dim DataPoints As String, Pattern As String, Parts() As String
  DataPoints = "," & Join(Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))), ",,") & ","
  Pattern = "," & Join(Application.Transpose(Range("D2", Cells(Rows.Count, "D").End(xlUp))), ",,") & ","
  Columns("A").Interior.ColorIndex = xlColorIndexNone
  Parts = Split(DataPoints, Pattern)
  PatLen = UBound(Split(Replace(Pattern, ",,", ","), ",")) - 1
  Rw = 2
  For X = 0 To UBound(Parts) - 1
    Commas = UBound(Split(Replace(Parts(X), ",,", ","), ","))
    Rw = Rw + Commas + 1 - 2 * (Len(Parts(X)) = 0)
    Cells(Rw - 2, "A").Resize(PatLen).Interior.ColorIndex = 6
    Rw = Rw + PatLen - 2
  Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top