Along with requested pattern; Bring out 1 next following pattern

[Kishan]

Using Excel 2000

Hi,

I got patterns data in cells C6:C69
In the cells E5:N5 I got patterns, which I want to extract from cells C6:C69 and put them in corresponding column E5:N5 Along with requested pattern; Bringing out 1 next following pattern

For example in column E I want to extract out pattern 1|1|1 from C6:C69 with 1 next following pattern as shown

And in column F I want to extract out pattern 1|1|X from C6:C69 with 1 next following pattern as shown

And so on for the rest.........

Example data...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4					Patt	Patt	Patt	Patt	Patt	Patt	Patt	Patt	Patt	Patt
5			Patt		1|1|1	1|1|X	1|1|2	1|X|1	1|2|1	X|1|1	2|1|1	1|X|X	1|X|2	1|2|X
6			X|1|1							X|1|1
7			1|1|1		1|1|1					1|1|1
8			1|1|X		1|1|X	1|1|X
9			1|X|X			1|X|X				X|1|1		1|X|X
10			1|X|1		1|1|1			1|X|1		1|1|1		1|X|1
11			X|1|1		1|1|1	1|1|X		X|1|1
12			1|1|1		1|1|1	1|X|1				X|1|1		1|X|X
13			1|1|1		1|1|X			1|X|1		1|1|1		1|2|1
14			1|1|1			1|1|X		X|1|1
15			1|1|X		1|1|1	1|X|X				X|1|1		1|X|X
16			1|X|1		1|1|1			1|X|1		1|1|1		X|1|1
17			X|1|1		1|1|1	1|1|X		X|1|1
18			1|1|1		1|1|X	1|X|2				X|1|1
19			1|1|1					1|1|X		1|1|1
20			1|1|1		1|1|1	1|1|X		1|X|2
21			1|1|X		1|1|X	1|X|1				X|1|1
22			1|X|X					1|X|1		1|1|1
23			1|2|1		1|1|1	1|1|X		1|X|1	1|2|1
24			2|1|1		1|1|2	1|X|1		X|1|1	2|1|1	X|1|1	2|1|1
25			1|1|2				1|1|2			1|1|1	1|1|2
26			1|2|X		1|1|1	1|1|X	1|2|X	1|X|1	1|2|1					1|2|X
27			1|X|2		1|1|1	1|X|1		X|1|1	2|1|1	X|1|1	2|1|1		1|X|2	1|X|2
28			2|1|X		1|1|1		1|1|2			1|1|1	1|1|2		2|1|X
29			1|X|1		1|1|2	1|1|X	1|2|1	1|X|1	1|2|1					1|2|X
30			X|1|1			1|X|1		X|1|1	2|1|1		2|1|1		1|X|2	1|X|X
31			1|1|1		1|1|1		1|1|2				1|1|1		1|X|1
32			1|1|X		1|1|1		1|2|1	1|X|1
33			1|X|2		1|1|1			1|2|X
34			1|X|1		1|1|1
35			1|X|1		1|1|1			1|X|1
36			X|1|1		1|1|X
37			1|1|1
38			1|1|2		1|1|1
39			1|2|1		1|1|X
40			2|1|1
41			1|1|X		1|1|1
42			1|X|1		1|1|1
43			X|1|1		1|1|1
44			1|1|1		1|1|1
45			1|1|1		1|1|X
46			1|1|1
47			1|1|2
48			1|2|1
49			2|1|1
50			1|1|1
51			1|1|1
52			1|1|1
53			1|1|1
54			1|1|1
55			1|1|X
56			1|X|1
57			X|1|1
58			1|1|1
59			1|1|X
60			1|X|1
61			1|2|X
62			1|X|X
63			X|1|1
64			1|1|1
65			1|1|1
66			1|1|1
67			1|1|1
68			1|1|X
69			1|X|1
70
Sheet4

Thank you in advance

Regards,
Kishan

 

[Kishan]

Bump

 

[Kishan]

Hi, any suggestions

Question in post_3 and explained in post_7

Regards,
Kishan

Hi, I am giving a round my head to change the question to make it easy but can not find any way

May some can help with this as it is requested please.

Thank you in advance

Regards,
Kishan

 

[Eric W]

With the layout from post # 2:

Sub GetPatterns()

    For c = 5 To Cells(5, "E").End(xlToRight).Column
        Cells(6, c).Resize(Cells(Rows.Count, "C").End(xlUp).Row).ClearContents
        r1 = 6
        For r = 6 To Cells(6, "C").End(xlDown).Row
            If Cells(r, "C") = Cells(5, c) Then
                Cells(r1, c).Resize(2).Value = Cells(r, "C").Resize(2).Value
                r1 = r1 + IIf(Cells(r1 + 1, c) = Cells(5, c), 1, 3)
            End If
        Next r
    Next c
    
End Sub

 

[Kishan]

With the layout from post # 2:

Sub GetPatterns()

    For c = 5 To Cells(5, "E").End(xlToRight).Column
        Cells(6, c).Resize(Cells(Rows.Count, "C").End(xlUp).Row).ClearContents
        r1 = 6
        For r = 6 To Cells(6, "C").End(xlDown).Row
            If Cells(r, "C") = Cells(5, c) Then
                Cells(r1, c).Resize(2).Value = Cells(r, "C").Resize(2).Value
                r1 = r1 + IIf(Cells(r1 + 1, c) = Cells(5, c), 1, 3)
            End If
        Next r
    Next c
    
End Sub

Wow Erik, thank you so much I was desperate to get solution because I wanted it for final step analysis.

I appreciate your help a lot. Code works perfectly

Have a nice day

Kind Regards,
Kishan

 

[Kishan]

With the layout from post # 2:

Hi Erik, you gave a very good code solution, I showed 10 patterns in the example now extended up to 27 and didn't require any change worked like magic.

Thank you once again

Kind Regards,
Kishan

 

[Eric W]

Glad it worked for you.