Separate database and count successive in string

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

Database Columns</SPAN> C:P</SPAN>
Count successive ( 1’s ) in columns R:AE (count successive as in order appears ) Row3 1’s result 3-1-1-2-1</SPAN></SPAN>
Count successive ( X’s + 2’s ) in columns AG:AT (count successive as in order appears ) Row3 X’s & 2’s result 1-3-2</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	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
2			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
3			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
4			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
5			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14		1	1	1	1	1	1	1	1	1	1	1	1	1	1		X	2	X	2	X	2	X	2	X	2	X	2	X	2
6			X	1	1	1	2	1	2	1	2	X	1	1	X	1		3	1	1	2	1											1	3	2
7			X	X	2	1	X	X	1	1	2	1	X	2	1	1		1	2	1	2												2	1	2	1	1	1
8			X	1	1	1	2	1	2	1	2	X	1	1	X	1		1	2	1	1	2	1										1	3	2
9			2	1	1	X	2	1	X	1	1	2	2	1	1	1		1	1	1	2	3											0	1	1	1	1	2
10			1	X	1	X	1	1	2	1	1	X	X	X	1	1		1	1	2	2	2											2	1	3
11			X	2	X	X	2	X	1	1	2	1	1	1	1	2		2	4														1	1	2	1	1	2
12			X	1	1	2	1	1	1	2	1	1	X	1	X	1		1	1	3	2	1											1	2	2
13			1	X	X	2	1	X	1	1	1	2	1	1	X	1		1	1	3	2	1											2	1	1	1	1
14			1	1	1	2	X	X	X	1	X	X	1	X	X	X		3	1	1													0	1	8
15			1	X	X	1	1	1	1	1	X	1	1	2	X	1		1	2	5	1												3	1	1
16			X	1	X	2	1	2	X	X	X	1	1	1	X	X		1	1	3													2	2	5
17			1	1	X	1	2	1	X	1	1	1	2	1	X	1		2	1	1	3	1	1										1	1	1	1	1
18			1	1	1	1	1	X	1	1	1	2	2	X	1	1		5	3	2													1	2	1
19			X	1	1	X	X	1	1	1	1	2	1	1	X	X		2	4	2													3	1	2
20			1	1	1	1	2	1	X	X	1	2	X	1	X	2		4	1	1	1												0	1	2	1	2	1
21			1	1	1	2	2	1	1	X	1	2	1	1	X	1		3	2	1	2	1											0	2	1	1	1
22			2	1	X	1	1	X	1	1	1	2	1	1	1	2		1	2	3	3												0	1	2	2
23			1	1	1	1	1	2	1	1	2	1	X	X	X	1		5	2	1	1												0	2	3
24			X	1	1	2	1	1	2	1	1	X	1	1	1	X		2	2	2	2	1											1	2	2
25			1	1	1	1	X	1	1	1	1	1	1	1	1	1		4	9														1
26			X	1	1	1	1	2	1	1	X	X	1	1	X	1		4	2	2	1												1	1	3
Sheet1

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

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[JLGWhiz]

I'll try anything once. See if this works for you.

Sub t()
Dim i As Long, c As Range, cnt1 As Long, cnt2 As Long
With ActiveSheet
    For i = 6 To .Range("C" & Rows.Count).End(xlUp).Row
        cnt1 = 0
        cmt2 = 0
        For Each c In Range("C" & i, "P" & i)
            If c.Value = 1 Then
                cnt1 = cnt1 + 1
            ElseIf cnt1 > 0 And c.Value <> 1 Then
                If .Cells(i, 18).Value = "" Then
                    .Cells(i, 18) = cnt1
                    cnt1 = 0
                Else
                    .Cells(i, "AF").End(xlToLeft).Offset(, 1) = cnt1
                    cnt1 = 0
                End If
                cnt1 = 0
            End If
            If c.Value = "X" Or c.Value = 2 Then
                cnt2 = cnt2 + 1
            ElseIf cnt2 > 0 And c.Value <> "X" And c.Value <> 2 Then
                If .Cells(i, "AG").Value = "" Then
                    .Cells(i, "AG") = cnt2
                    cnt2 = 0
                Else
                    .Cells(i, "AU").End(xlToLeft).Offset(, 1) = cnt2
                    cnt2 = 0
                End If
            End If
        Next
    Next
End With
End Sub

 

[Kishan]

I'll try anything once.

Thank you JLGWhiz, for your help. Above phrase is lovely.

See if this works for you.

1^st Result for 1’s code shows 3-1-1-2, which should be 3-1-1-2-1
2^nd Result for (X’s + 2’s) is also not correct as per post#1

Any ways this gives me idea I must change question in 3 steps

Step-1, Count 1’s consecutive (example result Row6 = 3-1-1-2-1) result in column R:X

Step-2, This is bit awkward start counting 1^st with X (example result Row6 = 1-1-1-1-1-1 all are singles no consecutives). And the (example result Row9 = 0-1-1-1-1-2, X not find so first = 0 then 4 singles 1-1-1-1, and then 2 are consecutives = 2)

Step-3, Result must be 1-3-2 this is also bit awkward (count till same sign continue). For example row 6 first X =1 time, then 2 = 3 times, then X=2 times so result (1-3-2)

Please here is the new layout

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	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
2			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
3			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
4			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
5			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14		1	1	1	1	1	1	1		X	2								X	2	X	2	X	2	X
6			X	1	1	1	2	1	2	1	2	X	1	1	X	1		3	1	1	2	1				1	1	1	1	1	1				1	3	2
7			X	X	2	1	X	X	1	1	2	1	X	2	1	1		1	2	1	2					2	1	2	1	1	1				2	1	2	1	1	1
8			X	1	1	1	2	1	2	1	2	X	1	1	X	1		1	2	1	1	2	1			1	1	1	1	1	1				1	3	2
9			2	1	1	X	2	1	X	1	1	2	2	1	1	1		1	1	1	2	3				0	1	1	1	1	2				0	1	1	1	1	2
10			1	X	1	X	1	1	2	1	1	X	X	X	1	1		1	1	2	2	2				1	1	1	3						2	1	3
11			X	2	X	X	2	X	1	1	2	1	1	1	1	2		2	4							1	1	2	1	1	1	1			1	1	2	1	1	2
12			X	1	1	2	1	1	1	2	1	1	X	1	X	1		2	3	2	1	1				1	1	1	1	1					1	2	2
13			1	X	X	2	1	X	1	1	1	2	1	1	X	1		1	1	3	2	1				2	1	1	1	1					2	1	1	1	1
14			1	1	1	2	X	X	X	1	X	X	1	X	X	X		3	1	1						0	1	3	2	3					0	1	8
15			1	X	X	1	1	1	1	1	X	1	1	2	X	1		1	2	5	1					2	1	1	1						3	1	1
16			X	1	X	2	1	2	X	X	X	1	1	1	X	X		1	1	3						1	1	1	1	3	2				2	2	5
17			1	1	X	1	2	1	X	1	1	1	2	1	X	1		2	1	1	3	1	1			1	1	1	1	1					1	1	1	1	1
18			1	1	1	1	1	X	1	1	1	2	2	X	1	1		5	3	2						1	2	1							1	2	1
19			X	1	1	X	X	1	1	1	1	2	1	1	X	X		2	4	2						1	2	1	2						3	1	2
20			1	1	1	1	2	1	X	X	1	2	X	1	X	2		4	1	1	1					0	1	2	1	1	1	1			0	1	2	1	2	1
21			1	1	1	2	2	1	1	X	1	2	1	1	X	1		3	2	1	2	1				0	2	1	1	1					0	2	1	1	1
22			2	1	X	1	1	X	1	1	1	2	1	1	1	2		1	2	3	3					0	1	1	1	1	1				0	1	2	2
23			1	1	1	1	1	2	1	1	2	1	X	X	X	1		5	2	1	1					0	1	1	3						0	2	3
24			X	1	1	2	1	1	2	1	1	X	1	1	1	X		2	2	2	2	1				1	1	1	1	1					1	2	2
25			1	1	1	1	X	1	1	1	1	1	1	1	1	1		4	9							1									1
26			X	1	1	1	1	2	1	1	X	X	1	1	X	1		4	2	2	1					1	1	2	1						1	1	3
Sheet2

Thank You

Regards,
Kishan

 

[Kishan]

Hi, Please ignore post#3 I think it is a bit messy. I will put all 3-steps separately in 3 different posts, here.

Sorry for inconvenience

Regards,
Kishan

 

[Kishan]

Hi, Step-1, Count 1’s consecutive (example result Row6 = 3-1-1-2-1) result in column R:AE

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	AB	AC	AD	AE
1			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
2			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
3			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
4			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
5			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14		1	1	1	1	1	1	1	1	1	1	1	1	1	1
6			X	1	1	1	2	1	2	1	2	X	1	1	X	1		3	1	1	2	1
7			X	X	2	1	X	X	1	1	2	1	X	2	1	1		1	2	1	2
8			X	1	1	1	2	1	2	1	2	X	1	1	X	1		1	2	1	1	2	1
9			2	1	1	X	2	1	X	1	1	2	2	1	1	1		1	1	1	2	3
10			1	X	1	X	1	1	2	1	1	X	X	X	1	1		1	1	2	2	2
11			X	2	X	X	2	X	1	1	2	1	1	1	1	2		2	4
12			X	1	1	2	1	1	1	2	1	1	X	1	X	1		1	1	3	2	1
13			1	X	X	2	1	X	1	1	1	2	1	1	X	1		1	1	3	2	1
14			1	1	1	2	X	X	X	1	X	X	1	X	X	X		3	1	1
15			1	X	X	1	1	1	1	1	X	1	1	2	X	1		1	2	5	1
16			X	1	X	2	1	2	X	X	X	1	1	1	X	X		1	1	3
17			1	1	X	1	2	1	X	1	1	1	2	1	X	1		2	1	1	3	1	1
18			1	1	1	1	1	X	1	1	1	2	2	X	1	1		5	3	2
19			X	1	1	X	X	1	1	1	1	2	1	1	X	X		2	4	2
20			1	1	1	1	2	1	X	X	1	2	X	1	X	2		4	1	1	1
21			1	1	1	2	2	1	1	X	1	2	1	1	X	1		3	2	1	2	1
22			2	1	X	1	1	X	1	1	1	2	1	1	1	2		1	2	3	3
23			1	1	1	1	1	2	1	1	2	1	X	X	X	1		5	2	1	1
24			X	1	1	2	1	1	2	1	1	X	1	1	1	X		2	2	2	2	1
25			1	1	1	1	X	1	1	1	1	1	1	1	1	1		4	9
26			X	1	1	1	1	2	1	1	X	X	1	1	X	1		4	2	2	1
Step-1

 

[Kishan]

Hi, Step-2, This is bit awkward start counting 1^st with X (example result Row6 = 1-1-1-1-1-1 all are singles no consecutives). And the (example result Row9 = 0-1-1-1-1-2, X not find so first = 0 then 4 singles 1-1-1-1, and then 2 are consecutives = 2)

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	AB	AC	AD	AE
1			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
2			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
3			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
4			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
5			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14		X	2
6			X	1	1	1	2	1	2	1	2	X	1	1	X	1		1	1	1	1	1	1
7			X	X	2	1	X	X	1	1	2	1	X	2	1	1		2	1	2	1	1	1
8			X	1	1	1	2	1	2	1	2	X	1	1	X	1		1	1	1	1	1	1
9			2	1	1	X	2	1	X	1	1	2	2	1	1	1		0	1	1	1	1	2
10			1	X	1	X	1	1	2	1	1	X	X	X	1	1		1	1	1	3
11			X	2	X	X	2	X	1	1	2	1	1	1	1	2		1	1	2	1	1	1	1
12			X	1	1	2	1	1	1	2	1	1	X	1	X	1		1	1	1	1	1
13			1	X	X	2	1	X	1	1	1	2	1	1	X	1		2	1	1	1	1
14			1	1	1	2	X	X	X	1	X	X	1	X	X	X		0	1	3	2	3
15			1	X	X	1	1	1	1	1	X	1	1	2	X	1		2	1	1	1
16			X	1	X	2	1	2	X	X	X	1	1	1	X	X		1	1	1	1	3	2
17			1	1	X	1	2	1	X	1	1	1	2	1	X	1		1	1	1	1	1
18			1	1	1	1	1	X	1	1	1	2	2	X	1	1		1	2	1
19			X	1	1	X	X	1	1	1	1	2	1	1	X	X		1	2	1	2
20			1	1	1	1	2	1	X	X	1	2	X	1	X	2		0	1	2	1	1	1	1
21			1	1	1	2	2	1	1	X	1	2	1	1	X	1		0	2	1	1	1
22			2	1	X	1	1	X	1	1	1	2	1	1	1	2		0	1	1	1	1	1
23			1	1	1	1	1	2	1	1	2	1	X	X	X	1		0	1	1	3
24			X	1	1	2	1	1	2	1	1	X	1	1	1	X		1	1	1	1	1
25			1	1	1	1	X	1	1	1	1	1	1	1	1	1		1
26			X	1	1	1	1	2	1	1	X	X	1	1	X	1		1	1	2	1
Step-2

 

[Kishan]

Hi, Step-3, Result must be 1-3-2 this is also bit awkward (count till same sign continue). For example row6 first X =1 time, then 2 = 3 times, then X=2 times so result (1-3-2)

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	AB	AC	AD	AE
1			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
2			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
3			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
4			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
5			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14		X	2	X	2	X	2	X	2	X	2	X	2	X	2
6			X	1	1	1	2	1	2	1	2	X	1	1	X	1		1	3	2
7			X	X	2	1	X	X	1	1	2	1	X	2	1	1		2	1	2	1	1	1
8			X	1	1	1	2	1	2	1	2	X	1	1	X	1		1	3	2
9			2	1	1	X	2	1	X	1	1	2	2	1	1	1		0	1	1	1	1	2
10			1	X	1	X	1	1	2	1	1	X	X	X	1	1		2	1	3
11			X	2	X	X	2	X	1	1	2	1	1	1	1	2		1	1	2	1	1	2
12			X	1	1	2	1	1	1	2	1	1	X	1	X	1		1	2	2
13			1	X	X	2	1	X	1	1	1	2	1	1	X	1		2	1	1	1	1
14			1	1	1	2	X	X	X	1	X	X	1	X	X	X		0	1	8
15			1	X	X	1	1	1	1	1	X	1	1	2	X	1		3	1	1
16			X	1	X	2	1	2	X	X	X	1	1	1	X	X		2	2	5
17			1	1	X	1	2	1	X	1	1	1	2	1	X	1		1	1	1	1	1
18			1	1	1	1	1	X	1	1	1	2	2	X	1	1		1	2	1
19			X	1	1	X	X	1	1	1	1	2	1	1	X	X		3	1	2
20			1	1	1	1	2	1	X	X	1	2	X	1	X	2		0	1	2	1	2	1
21			1	1	1	2	2	1	1	X	1	2	1	1	X	1		0	2	1	1	1
22			2	1	X	1	1	X	1	1	1	2	1	1	1	2		0	1	2	2
23			1	1	1	1	1	2	1	1	2	1	X	X	X	1		0	2	3
24			X	1	1	2	1	1	2	1	1	X	1	1	1	X		1	2	2
25			1	1	1	1	X	1	1	1	1	1	1	1	1	1		1
26			X	1	1	1	1	2	1	1	X	X	1	1	X	1		1	1	3
Step-3

 

[Kishan]

Hi, I think 3 codes 1 code for each step will be better.

Thank you in advance

Regards,
Kishan

 

[B___P]

Hi Kishan,
you explanation is becoming a bit problematic.

I solved your question with one step code. Your second step looks like a helper step to achieve 3rd step and I think it is unnecessary.

Give this a try

Sub kishan()
Dim range1 As Range
Dim range1b As Range
Dim range2 As Range
Dim range3 As Range
Dim lastrow As Long
Dim c As Range
Dim d As Range
Dim alfa As String
Dim alfa1 As String
Dim alfaX2 As String
Dim Pattern1
Dim PatternX2
Dim t As Long
Dim off As Long

lastrow = ActiveSheet.Cells(6, 3).End(xlDown).Row
Set range1 = Range(Cells(6, 3), Cells(6, 3).End(xlDown))
Set range2 = ActiveSheet.Range(Range("R6"), Cells(lastrow, 31))
Set range3 = ActiveSheet.Range(Range("AG6"), Cells(lastrow, 46))
range2.ClearContents
range3.ClearContents
For Each c In range1
    Set range1b = Range(c, Cells(c.Row, 17))
    
    For Each d In range1b
        alfa = alfa & d.Value
    Next d
    
    alfa1 = Trim(Replace(Replace(alfa, 2, " "), "X", " "))
    
    Do Until InStr(1, alfa1, "  ") = 0
        alfa1 = Replace(alfa1, "  ", " ")
    Loop
    
    alfaX2 = Trim(Replace(alfa, 1, ""))
    alfaX2 = Replace(Replace(alfaX2, "2X", "2 X"), "X2", "X 2")
    Pattern1 = Split(alfa1, " ")
    
    For t = 0 To UBound(Pattern1)
        Cells(c.Row, 18 + t).Value = Len(Pattern1(t))
    Next t
    
    PatternX2 = Split(alfaX2, " ")
    
    For t = 0 To UBound(PatternX2)
        If (t = 0 And Mid(PatternX2(t), 1, 1) = "2") Then 'Or (t = 0 And Mid(PatternX2(t), 1, 1) = "X") Then
            Cells(c.Row, 33 + t).Value = 0
            Cells(c.Row, 33 + t + 1).Value = Len(PatternX2(t))
            off = 1
        Else
            Cells(c.Row, 33 + t + off).Value = Len(PatternX2(t))
        End If
    Next t
    
    off = 0
    alfa = ""
    alfa1 = ""
    alfaX2 = ""
Next c
End Sub

Hope this helps

 

[Kishan]

Hi Kishan,
you explanation is becoming a bit problematic.

Hi B___P, I totally agree with you when I reread my post I found it was not understandable even to me.

Hi Kishan,
I solved your question with one step code. Your second step looks like a helper step to achieve 3rd step and I think it is unnecessary.

Yes, what you are thinking is right, 2^nd step was helpful to achieve the 3^rd step.

But now I am thinking it can be useful. For example I am getting the results for (X’s + 2’s) say for the Row6 = (1-3-2) mean 1=X’s, 3=2’s & again 2=X’s (So step-2 if result is 1-1-1-1-1-1 this assign X’s & 2’s are separate)

For example the results for (X’s + 2’s) say for the Row10 = (2-1-3) mean 2=X’s, 1=2’s & again 3=X’s (So step-2 if result is 1-1-1-3 this assign all first X’s & 2’s are separate and last 3 X’s are consecutive)

So I am thinking if it is not much trouble for you can you make a separate macro for Step-2 (which places result at the end of macro Step-1) in columns AV:BI (separate will be good because it will give me an option and I can use it as necessary)

Hi Kishan,
Give this a try

Sub kishan()
End Sub

Hope this helps

Great!!
Thank you very much B___P, this is working flawless!!

I appreciate your help and time.

Kind Regards,
Kishan