Identify Number Pattern of Positive(+) -> Zero(0) -> Negative(-)

[ibmy]

Dear Smartest Excelers,

Some of sample from my 90k row data:

Book1
	A	B	C	D
1		Diff	Result
2		0
3		0.4
4		0.5
5		2.3
6		0
7		0
8		0
9		-3.9	1	B5(+) -> B6:B8(0) -> B9(-)
10		-0.1		Rule 4: Start new find
11		0
12		0.4		No count : B10(-) -> B11 (0) -> B12 (+)
13		0
14		-0.5	1	B12(+) -> B13(0) -> B14(-)
15		0		Rule 4: Start new find
16		-0.2
17		0.3
18		0
19		0
20		0
21		0
22		0.3		No count : B17(+) -> B18:B21 (0) -> B22 (+)
23		-0.5
24		0
25		-0.6		No count : B23(-) -> B24 (0) -> B25 (-)
26		0.9
27		0.1
28		0
29		0
30		-0.1	1	B27(+) -> B28:B29 (0) -> B30(-)
31		-0.1		Rule 4: Start new find
32		0.4
Sheet17

Rule:
1. Must start from a Positive Value
2. Follow by 0 ( at least one 0, no limit how many 0 can occur but for now, the max 0's I dealt is 50, might change later)
3. Ending with a Negative Value
4. After Number Pattern is complete, number "1" assigned and start a new find in next row (Rule 1).

Short : Before 0 must postive value & after 0 , must negative value ( + 0 - )

......

No count / Break Rule :
1. Not start from Positive Value = Not count
2. Start a Positive Value -> Follow by no 0 = No count
3. Start a Positive Value -> Follow by 0 -> Ending with not a Negative Value = Break Rule

Short : Invalid number pattern (- 0 + / - 0 - / + 0 + )

.....

Any outcome is welcome,(other than result in Column C) as long as I know which Row happen this kind of number pattern. I was thinking, I will do filter to "1" after get a result from Column C.

 

[Rick Rothstein]

Here is another macro that you can consider (maybe Peter will time test it us). Note that I made it easy for you to run your alternatives by giving you two variables that you can set. These variable take the sign of the number ("+" for positive number, "-" for negative number) before the zero and after the zero that you are looking for.

Sub PlusZeroMinus()
  Dim Ar As Range, SignBefore As String, SignAfter As String
  SignBefore = "+"
  SignAfter = "-"
  Application.ScreenUpdating = False
  With Range("B2", Cells(Rows.Count, "B").End(xlUp))
    .Offset(, 1).ClearContents
    .Offset(, 1) = Evaluate(Replace("IF(@=0,"""",IF(@>0,""+"",""-""))", "@", .Address))
    For Each Ar In .Offset(, 1).SpecialCells(xlBlanks).Areas
      If Ar(1).Offset(-1) = SignBefore And Ar(Ar.Count + 1) = SignAfter Then Ar(Ar.Count + 1) = 1
    Next
    .Offset(, 1) = Evaluate("IF(" & .Offset(, 1).Address & "=1,1,"""")")
  End With
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

maybe Peter will time test it

You wouldn't have needed a timer if you had tried them with data of any considerable size.
90,000 rows
Post #6: 0.1 seconds
Post #11: 30 seconds

please help me on pattern +0+ & -0-

Why don't you try amending @Peter_SSs code?

I think the OP did try that.

I tried change sign (>0,>0 & <0,<0) VBA in Post #6

Sub PlusZeroPlus()
  Dim a As Variant, b As Variant
  Dim i As Long
  Dim bStart As Boolean, bCont As Boolean

  a = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    Select Case a(i, 1)
      Case Is > 0
        If bCont Then
          b(i, 1) = 1
        Else
          bStart = True
          bCont = False
        End If
      Case 0
        If bStart Then bCont = True
      Case Is < 0
        bStart = False
        bCont = False
    End Select
  Next i
  Range("C2").Resize(UBound(b)).Value = b
End Sub

Sub MinusZeroMinus()
  Dim a As Variant, b As Variant
  Dim i As Long
  Dim bStart As Boolean, bCont As Boolean

  a = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    Select Case a(i, 1)
      Case Is < 0
        If bCont Then
          b(i, 1) = 1
        Else
          bStart = True
          bCont = False
        End If
      Case 0
        If bStart Then bCont = True
      Case Is > 0
        bStart = False
        bCont = False
    End Select
  Next i
  Range("C2").Resize(UBound(b)).Value = b
End Sub

 

[Rick Rothstein]

You wouldn't have needed a timer if you had tried them with data of any considerable size.
90,000 rows
Post #6: 0.1 seconds
Post #11: 30 seconds

Wow! Much, much worse than I thought it would be. Thanks for running the test.

 

[Peter_SSs]

Much, much worse than I thought it would be.

Might be my machine?

 

[Rick Rothstein]

Might be my machine?

I don't see how given your code ran against the same data.