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.

 

[Akuini]

Hi, @ibmy
Interesting case, try this:

Sub a1159856a()
'https://www.mrexcel.com/board/threads/identify-number-pattern-of-positive-zero-0-negative.1159856/

Dim i As Long, j As Long, n As Long
Dim va, z
Dim x As Double

n = Range("B" & Rows.Count).End(xlUp).Row + 1
va = Range("B1:B" & n)
Range("C2:C" & n).ClearContents
ReDim z(1 To 3)

For i = 2 To UBound(va, 1) - 1
    x = va(i, 1)
    
    Select Case x
        Case Is > 0
           If va(i + 1, 1) = 0 Then z(1) = x
        Case 0
           If va(i + 1, 1) < 0 And Len(z(1)) <> 0 Then z(2) = 0
        Case Is < 0
           If va(i - 1, 1) = 0 And Len(z(1)) <> 0 Then z(3) = x Else z(1) = Empty: z(2) = Empty
    End Select

    If Len(z(1)) <> 0 And Len(z(2)) <> 0 And Len(z(3)) <> 0 Then
        Cells(i, "C") = 1
        z(1) = Empty: z(2) = Empty: z(3) = Empty
    End If

Next
End Sub

Book2
	B	C
1	Diff
2	0
3	0.4
4	0.5
5	2.3
6	0
7	0
8	0
9	-3.9	1
10	-0.1
11	0
12	0.4
13	0
14	-0.5	1
15	0
16	-0.2
17	0.3
18	0
19	0
20	0
21	0
22	0.3
23	-0.5
24	0
25	-0.6
26	0.9
27	0.1
28	0
29	0
30	-0.1	1
31	-0.1
32	0.4
Sheet2

 

[ibmy]

Thanks @Akuini , VBA is working and fast

May I know how about pattern ( - 0 + )?
Same rule as above.

With my zero knowledge of VBA, I tried to modify VBA a bit but did not work. ?


Attemp 1 Fail : (Change the order)

Select Case x
Case Is < 0
If va(i - 1, 1) = 0 And Len(z(1)) <> 0 Then z(3) = x Else z(1) = Empty: z(2) = Empty
Case 0
If va(i + 1, 1) < 0 And Len(z(1)) <> 0 Then z(2) = 0
Case Is > 0
If va(i + 1, 1) = 0 Then z(1) = x
End Select

Attemp 2 Fail : (Change condition inside case >0 and case <0 )

Select Case x
Case Is < 0
If va(i - 1, 1) = 0 Then z(1) = x
Case 0
If va(i + 1, 1) < 0 And Len(z(1)) <> 0 Then z(2) = 0
Case Is > 0
If va(i + 1, 1) = 0 And Len(z(1)) <> 0 Then z(3) = x Else z(1) = Empty: z(2) = Empty
End Select

 

[alz]

Hi here is with formula!
you will need two rows of headers for formula .

TempO.xlsx
	A	B
1
2	Diff
3	0
4	0.4
5	0.5
6	2.3
7	0
8	0
9	0
10	-3.9	1
11	-0.1
12	0
13	0.4
14	0
15	-0.5	1
16	0
17	-0.2
18	0.3
19	0
20	0
21	0
22	0
23	0.3
24	-0.5
25	0
26	-0.6
27	0.9
28	0.1
29	0
30	0
31	-0.1	1
32	-0.1
33	0.4
18

Cell Formulas
Range	Formula
B3:B33	B3	=IF(IF(AND((AND(A2>0,A3=0)+AND(A2=0,A1>=0)+AND(A2<0,A1=0,A3<=0))=1,(AND(A3>0,A4=0)+AND(A3=0,A2>=0)+AND(A3<0,A2=0,A4<=0))=1,(AND(A4>0,A5=0)+AND(A4=0,A3>=0)+AND(A4<0,A3=0,A5<=0))=0,A3<0),1,0),1,"")

 

[Akuini]

May I know how about pattern ( - 0 + )?

Try:

Sub a1159856b()
'https://www.mrexcel.com/board/threads/identify-number-pattern-of-positive-zero-0-negative.1159856/

Dim i As Long, j As Long, n As Long
Dim va, z
Dim x As Double

n = Range("B" & Rows.Count).End(xlUp).Row + 1
va = Range("B1:B" & n)
Range("C2:C" & n).ClearContents
ReDim z(1 To 3)

For i = 2 To UBound(va, 1) - 1
    x = va(i, 1)
    
    Select Case x
        Case Is < 0
           If va(i + 1, 1) = 0 Then z(1) = x
        Case 0
           If va(i + 1, 1) > 0 And Len(z(1)) <> 0 Then z(2) = 0
        Case Is > 0
           If va(i - 1, 1) = 0 And Len(z(1)) <> 0 Then z(3) = x Else z(1) = Empty: z(2) = Empty
    End Select

    If Len(z(1)) <> 0 And Len(z(2)) <> 0 And Len(z(3)) <> 0 Then
        Cells(i, "C") = 1
        z(1) = Empty: z(2) = Empty: z(3) = Empty
    End If

Next
End Sub

Book2
	B	C
1	Diff
2	0
3	0.4
4	0.5
5	-2.3
6	0
7	0
8	0
9	5	1
10	-0.1
11	0
12	0.4	1
13	0
14	-0.5
15	0
16	-0.2
Sheet2

 

[Peter_SSs]

VBA is working and fast

Either is probably fast enough, but by my testing this (for the initial question) gives the same results about 7x faster.

Sub PlusZeroMinus()
  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
        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("C2").Resize(UBound(b)).Value = b
End Sub

 

[ibmy]

Thanks @Peter_SSs

For Number Pattern of ( - 0 + ), just change this part, right?

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

 

[Peter_SSs]

just change this part, right?

Yes, just two characters need changing

  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
        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("C2").Resize(UBound(b)).Value = b

 

[ibmy]

Sorry to bump this thread,

If you have a time, please help me on pattern +0+ & -0- . I thought this both pattern do not important in my case reasearch, but they do a little impact.

-0+ +0-.xlsb
	A	B	C
1		Diff	Plus Zero Plus
2		0
3		0.4
4		0.5
5		2.3
6		0
7		0
8		0
9		-3.9
10		-0.1
11		0
12		0.4
13		0
14		-0.5
15		0
16		-0.2
17		0.3
18		0
19		0
20		0
21		0
22		0.3	1
23		-0.5
24		0
25		-0.6
26		0.9
27		0.1
28		0
29		0
30		-0.1
31		-0.1
32		0.4
Sheet9

-0+ +0-.xlsb
	A	B	C
1		Diff	Minus Zero Minus
2		0
3		0.4
4		0.5
5		2.3
6		0
7		0
8		0
9		-3.9
10		-0.1
11		0
12		0.4
13		0
14		-0.5
15		0
16		-0.2	1
17		0.3
18		0
19		0
20		0
21		0
22		0.3
23		-0.5
24		0
25		-0.6	1
26		0.9
27		0.1
28		0
29		0
30		-0.1
31		-0.1
32		0.4
Sheet9

I tried change sign (>0,>0 & <0,<0) VBA in Post #6 and work around VBA in #Post 5 but failed ?

 

[Akuini]

Why don't you try amending @Peter_SSs code?
It's faster & easier to understand.