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

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
Dear Smartest Excelers,

Some of sample from my 90k row data:

Book1
ABCD
1DiffResult
20
30.4
40.5
52.3
60
70
80
9-3.91B5(+) -> B6:B8(0) -> B9(-)
10-0.1Rule 4: Start new find
110
120.4No count : B10(-) -> B11 (0) -> B12 (+)
130
14-0.51B12(+) -> B13(0) -> B14(-)
150Rule 4: Start new find
16-0.2
170.3
180
190
200
210
220.3No count : B17(+) -> B18:B21 (0) -> B22 (+)
23-0.5
240
25-0.6No count : B23(-) -> B24 (0) -> B25 (-)
260.9
270.1
280
290
30-0.11B27(+) -> B28:B29 (0) -> B30(-)
31-0.1Rule 4: Start new find
320.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.
 
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.
VBA Code:
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
 
Last edited:
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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


VBA Code:
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

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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