Count consecutive till change of sign ignore "1's"

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

Data located in columns C:P in cells C6:P44</SPAN></SPAN>
X & 2 counts result is shown in the columns R:Z in the cells R6:Z44</SPAN></SPAN>
Note: Colours are filled just to show example clearer</SPAN></SPAN>

I need to count X's AND 2's consecutive till change of Sign X from 2 Or 2 From X (Ignore 1's) </SPAN></SPAN>

Example for row 6</SPAN></SPAN>
X = 1 time, result in R6 = 1 </SPAN></SPAN>
2 = 2 time, result in S6 = 2 </SPAN></SPAN>
X = 2 time, result in T6 = 2 </SPAN></SPAN>
2 = 1 time, result in U6 = 1</SPAN></SPAN>
X = 1 time, result in V6 = 1 </SPAN></SPAN>
2 = 1 time, result in W6 = 1</SPAN></SPAN>

Count method will be same for each line</SPAN></SPAN>

Result data example</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2
3
4
5			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14		X	2	X	2	X	2	X	2	X
6			X	1	2	1	2	X	X	1	1	2	X	1	1	2		1	2	2	1	1	1
7			1	X	2	2	2	1	X	X	2	2	X	X	1	1		1	3	2	2	2
8			1	X	1	1	X	X	2	2	X	X	1	1	X	X		3	2	4
9			1	X	1	2	2	1	X	1	1	X	1	2	2	1		1	2	2	2
10			X	1	X	1	X	1	1	X	1	1	1	X	X	1		6
11			X	X	X	1	1	1	1	X	1	X	2	1	2	X		5	2	1
12			2	X	1	1	1	2	X	X	X	1	1	1	2	1			1	1	1	3	1
13			X	1	X	X	1	1	1	1	1	1	1	X	1	1		4
14			1	X	X	1	2	1	2	1	1	1	2	X	2	X		2	1	5
15			X	2	X	1	1	X	1	1	1	1	X	1	1	X		1	1	4
16			1	1	1	1	1	1	X	X	X	1	1	1	2	X		3	1	1
17			1	2	1	2	X	1	1	2	X	2	1	1	1	X			2	1	1	1	1	1
18			1	X	X	1	1	2	X	X	1	X	1	1	X	1		2	1	4
19			1	X	X	1	1	2	1	1	2	2	1	X	1	1		2	3	1
20			1	1	1	X	1	1	1	1	X	1	1	1	1	1		2
21			X	1	2	X	1	1	1	2	X	1	1	2	X	X		1	1	1	1	1	1	2
22			1	1	1	X	1	X	X	1	X	1	1	1	1	1		4
23			1	1	1	1	1	X	X	2	1	1	1	1	1	2		2	2
24			1	1	1	1	1	1	1	1	1	1	2	X	X	1		1	2
25			1	2	2	1	1	1	1	1	1	1	1	X	1	1			2	1
26			1	1	X	1	1	1	1	1	1	1	1	1	2	1		1	1
27			X	X	X	1	1	1	X	1	1	2	X	1	1	1		4	1	1
28			X	2	1	1	2	1	1	1	1	1	X	X	1	X		1	2	3
29			1	X	2	2	1	1	1	2	X	X	1	1	1	X		1	3	3
30			2	1	X	X	1	1	X	X	1	X	1	1	1	1			1	5
31			1	X	1	2	2	1	1	1	1	2	1	1	1	X		1	3	1
32			1	1	X	1	2	2	2	1	1	X	1	X	1	X		1	3	3
33			1	1	1	1	1	1	1	1	1	1	1	1	1	1
34			X	1	1	1	1	1	2	1	2	X	X	1	X	X		1	2	4
35			1	2	1	1	X	1	1	1	X	2	X	1	X	X			1	2	1	3
36			X	1	1	1	1	X	1	2	1	1	X	1	X	X		2	1	3
37			2	X	1	1	1	X	X	1	1	X	1	1	X	1			1	5
38			1	X	2	X	1	X	1	1	1	1	1	2	1	X		1	1	2	1	1
39			X	1	X	X	1	X	1	1	X	1	2	2	1	1		5	2
40			1	X	X	1	2	X	1	1	X	1	X	1	2	1		2	1	3	1
41			1	X	1	1	X	1	X	X	2	1	1	1	1	X		4	1	1
42			1	2	1	1	1	1	X	1	X	X	1	X	2	2			1	4	2
43			X	1	1	1	1	1	1	1	2	1	2	2	1	X		1	3	1
44			2	X	X	1	1	X	1	1	X	X	X	X	1	X			1	8
45
46
47
Hoja1

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN>

 

[Yongle]

Here is some VBA that does what you want...
-

The VBA
- builds a string of counts for values found in columns C:P, starting at row 6
- splits that string into columns R onwards
- the string starts with a comma if the first value in range is "2" (eg row 12)
- below are the strings for rows 6 to 12
Row 6 1,2,2,1,1,1
Row 7 1,3,2,2,2
Row 8 3,2,4
Row 9 1,2,2,2
Row 10 6
Row 11 5,2,1
Row 12 ,1,1,1,3,1


VBA goes in sheet module
- right-click on sheet tab \ view code \ copy and paste into pane on right

Option Explicit
    Dim countX As Integer, count2 As Integer, myStr As String

Sub Count2orX()
    Dim r As Long, celA As Range, celB As Range, rng As Range
    Dim countArray As Variant
   
For r = 6 To Range("C" & Rows.Count).End(xlUp).Row
'build delimited string of counts for this row
    ResetAll
    Set rng = Range("C" & r).Resize(, 14)       '14 columns in range
    For Each celA In rng
        Select Case celA.Value
            Case "X"
                    countX = countX + 1
                    If count2 > 0 Then Reset2
            Case 2
                    count2 = count2 + 1
                    If countX > 0 Then ResetX
        End Select
    Next celA
    If count2 > 0 Then Reset2 Else ResetX
'place values in cells
    If myStr <> "0" Then
        countArray = Split(StringOfCounts(rng), ",")
        Set celB = Range("R" & r).Resize(, UBound(countArray) + 1)
        celB.Value = countArray
        celB.Value = celB.Value                 'avoids numbers being formatted as text
    End If
Next r
End Sub

Function StringOfCounts(aRange As Range) As String
'this function inserts an extra delimiter at beginning of string if first found value is "2"
    Dim colX As Long, col2 As Long
    col2 = 9999: colX = 9999                'large default value simplifies IF test
    On Error Resume Next
    With WorksheetFunction
        col2 = .Match(2, aRange, 0)
        colX = .Match("X", aRange, 0)
    End With
    If col2 < colX Then myStr = "," & myStr
Debug.Print "Row " & aRange.Row, myStr                   'see note
    StringOfCounts = myStr
End Function

Private Sub ResetX()
    If myStr = "" Then myStr = countX Else myStr = myStr & "," & countX
    countX = 0
End Sub
Private Sub Reset2()
    If myStr = "" Then myStr = count2 Else myStr = myStr & "," & count2
    count2 = 0
End Sub
Private Sub ResetAll()
    myStr = ""
    countX = 0
    count2 = 0
End Sub

see result of this line in VBA immediate window which is viewed with {CTRL} G

Debug.Print "Row " & aRange.Row, myStr

delete the line after testing

 

[Kishan]

Here is some VBA that does what you want...
-

The VBA
- builds a string of counts for values found in columns C:P, starting at row 6
- splits that string into columns R onwards
- the string starts with a comma if the first value in range is "2" (eg row 12)
- below are the strings for rows 6 to 12
Row 6 1,2,2,1,1,1
Row 7 1,3,2,2,2
Row 8 3,2,4
Row 9 1,2,2,2
Row 10 6
Row 11 5,2,1
Row 12 ,1,1,1,3,1


VBA goes in sheet module
- right-click on sheet tab \ view code \ copy and paste into pane on right

Option Explicit
    Dim countX As Integer, count2 As Integer, myStr As String

Sub Count2orX()
    
End Sub

see result of this line in VBA immediate window which is viewed with {CTRL} G

Debug.Print "Row " & aRange.Row, myStr

delete the line after testing

WOW!! Yongle, it worked like magic tried inclusive with my original data 9000+ rows it is so speedy. Also saw result of this line in VBA immediate window which is viewed with {CTRL} G, is perfect</SPAN></SPAN>

After running your code I did fine I had counting errors in cells U14, V14 and T24</SPAN></SPAN>

Thank you for your kind help</SPAN></SPAN>

Have a nice weekend</SPAN></SPAN>

Good Luck</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Yongle]

Thanks for the feedback