Find Number Pattern in Data - need 1 Column solution

[ibmy]

Hi Mr Excel,

How do find Number Pattern in data which is :
1. Starting a Negative value ( - )
2. Follow by at least a Zero ( 0 / 00 / 000 / 000..... )
3. Follow by a Positive value ( + )
4. End with a Negative value ( - )

Number Pattern : - 0 + -

When Number Pattern found, assign "1" at row of Positive Value.

Sample data of 200k row with description :

4 no.xlsb
	F			I	J
1	Data			Expected Result	Explanation
2
3	0.3
4	-0.1
5	0
6	0.9			1
7	-0.7				Pattern found. Assign "1" at Positive Value & Start new find at this row
8	-1.7
9	-1.1
10	-1
11	0
12	0
13	0
14	0.2			1
15	-1.8				Pattern found. Assign "1" at Positive Value & Start new find at this row
16	0
17	0
18	1			1
19	-2				Pattern found. Assign "1" at Positive Value & Start new find at this row
20	-0.6
21	0
22	1.3
23	1.2
24	-0.5
25	-1.9
26	-2
27	0.4
28	-0.1
29	0
30	1.2
31	0
32	-0.3
33	0
34	0
35	0.4
36	0
37	0.7
38	-0.7
Sheet1

This is my current solution but need 2 columns :

4 no.xlsb
	F	G	H
1	Data	Code	Function IF-AND
2
3	0.3
4	-0.1
5	0
6	0.9	1	1
7	-0.7
8	-1.7
9	-1.1
10	-1
11	0
12	0
13	0
14	0.2	1	1
15	-1.8
16	0
17	0
18	1	1	1
19	-2
20	-0.6
21	0
22	1.3	1
23	1.2
24	-0.5
25	-1.9
26	-2
27	0.4
28	-0.1
29	0
30	1.2	1
31	0
32	-0.3
33	0
34	0
35	0.4	1
36	0
37	0.7
38	-0.7
Sheet2

Cell Formulas
Range	Formula
H6:H38	H6	=IF(AND(G6=1,F7<0),1,"")

Column G, I use this code to find number pattern - 0 +

Sub MinusZeroPlus()

  Dim a As Variant, b As Variant
  Dim i As Long
  Dim bStart As Boolean, bCont As Boolean

  a = Range("F2", Range("F" & Rows.Count).End(xlDown)).Value
  
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    Select Case a(i, 1)
      Case Is < 0
        bStart = True
        bCont = False
      Case 0
        If bStart Then bCont = True
      Case Is > 0
        If bCont Then b(i, 1) = 1
        bStart = False
        bCont = False
    End Select
  Next i
  Range("G2").Resize(UBound(b)).Value = b
  
End Sub

Then, I use formula " =IF(AND(G6=1,F7<0),1,"") " to find what value of end pattern.


Thanks in advance, hopefully I can reduce column used in sheet and improve my work efficiency.

 

[DanteAmor]

Hi @ibmy .

Thanks for posting on MrExcel.​

I hope you are well.​

Try this.
It does not require the auxiliary column. Results in column "I"

Sub FindNumberPattern()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim itfound As Boolean
 
  a = Range("F2", Range("F" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a, 1) - 3
    If a(i, 1) < 0 And a(i + 1, 1) = 0 Then
      itfound = False
      For k = i + 2 To UBound(a) - 1
        If a(k, 1) > 0 And a(k + 1, 1) < 0 Then
          itfound = True
          b(k, 1) = 1
          Exit For
        ElseIf a(k, 1) = 0 Then
          'continue
        Else
          itfound = False
          Exit For
        End If
      Next
      If itfound = True Then i = k
    End If
  Next i
 
  Range("I2").Resize(UBound(b)).Value = b
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[ibmy]

Thanks expert, @DanteAmor
The code working great.

I manage to find opposite of its pattern with your code by changing > < > sign, + 0 - +

If you could help me one more thing with the data that consist of no negative value, all are positive value (absolute).
My interest number pattern is in range which is:

1. Starting a value greater than 0.5
2. Follow by at least a Zero ( 0 / 00 / 000 / 000..... )
3. Follow by a value less than 0.6 and greater than 0
4. End with a valuer greater than 0.5

I managed to get result by changing > 0.5 , <0.6 > 0.5 :

Sub Abs_Pattern()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim itfound As Boolean
 
  a = Range("F2", Range("F" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a, 1) - 3
    If a(i, 1) > 0.5 And a(i + 1, 1) = 0 Then
      itfound = False
      For k = i + 2 To UBound(a) - 1
        If a(k, 1) < 0.6 And a(k + 1, 1) > 0.5 Then
          itfound = True
          b(k, 1) = 1
          Exit For
        ElseIf a(k, 1) = 0 Then
          'continue
        Else
          itfound = False
          Exit For
        End If
      Next
      If itfound = True Then i = k
    End If
  Next i
 
  Range("I2").Resize(UBound(b)).Value = b
End Sub

But there is problem when I change this line a(k, 1) < 0.6 , it violate third rule ( 3. Follow by a value less than 0.6 and greater than 0 ) ,it includes 0.

I tried change the line to:
1) " If a(k, 1) = 0.1, 0.2, 0.3, 0.4, 0.5 "
2) " If 0.6 > a(k, 1) > 0 "

But none working.

range 4 no.xlsb
	F			I
1	Data			Range
2
3	0.6
4	0.8
5	0
6	0
7	0.2			1
8	0.9
9	0.1
10	1.6
11	0
12	0
13	0.5			1
14	1.1
15	0.7
16	0.7
17	0
18	0			1
19	0.7
20	1.4
21	0.5
22	0.9
23	0
24	0			1
25	0.7
26	0.2
Sheet14

Yellow Cell are wrong assing "1"

 

[DanteAmor]

1. Starting a value greater than 0.5
2. Follow by at least a Zero ( 0 / 00 / 000 / 000..... )
3. Follow by a value less than 0.6 and greater than 0
4. End with a valuer greater than 0.5

Try this:

Sub Abs_Pattern()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim itfound As Boolean
 
  a = Range("F2", Range("F" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a, 1) - 3
    If a(i, 1) > 0.5 And a(i + 1, 1) = 0 Then
      itfound = False
      For k = i + 2 To UBound(a) - 1
        If a(k, 1) < 0.6 And a(k, 1) > 0 And a(k + 1, 1) > 0.5 Then
          itfound = True
          b(k, 1) = 1
          Exit For
        ElseIf a(k, 1) = 0 Then
          'continue
        Else
          itfound = False
          Exit For
        End If
      Next
      If itfound = True Then i = k
    End If
  Next i
 
  Range("I2").Resize(UBound(b)).Value = b
End Sub

 

[ibmy]

Thanks again @DanteAmor

The code works very great.
Brilliant expert.

 

[Peter_SSs]

If you are interested in alternatives, I think this would be one for the original question & data layout.

Sub Find_Pattern()
  With Range("I5:I" & Range("F" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(AND(F5>0,F6<0,F4=0,IFERROR(AGGREGATE(14,6,ROW(F$3:F4)/(F$3:F4<0),1),0)>IFERROR(AGGREGATE(14,6,ROW(F$3:F4)/(F$3:F4>0),1),0)),1,"""")"
    .Value = .Value
  End With
End Sub

 

[ibmy]

Thanks @Peter_SSs for the code.

 

[Peter_SSs]

You're welcome. Glad to contribute.