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]

Hi, if I change layout as below, extract the pattern start in row 6 and leave the 1 space between one and another pattern. Or any another idea you got please suggest.

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	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		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
7			1|1|1		1|1|X	1|X|X	1|2|X	X|1|1	2|1|1	1|1|1	1|1|2	1|X|1	2|1|X	1|X|2
8			1|1|X
9			1|X|X		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
10			1|X|1		1|1|1	1|X|1	1|2|1	X|1|1	2|1|1	1|1|1	1|1|2	1|2|1	1|X|1	1|X|X
11			X|1|1		1|1|1
12			1|1|1		1|1|X	1|1|X	1|1|2	1|X|1	1|2|1	X|1|1	2|1|1	1|X|X
13			1|1|1			1|X|X	1|2|1	X|1|1	2|1|1	1|1|1	1|1|1	X|1|1
14			1|1|1		1|1|1
15			1|1|X		1|1|1	1|1|X		1|1|X		X|1|1
16			1|X|1		1|1|1	1|X|2		1|X|2		1|1|1
17			X|1|1		1|1|X
18			1|1|1			1|1|X		1|X|1		X|1|1
19			1|1|1		1|1|1	1|X|1		1|X|1		1|1|1
20			1|1|1		1|1|X			X|1|1
21			1|1|X			1|1|X				X|1|1
22			1|X|X		1|1|1	1|X|1		1|X|1		1|1|1
23			1|2|1		1|1|2			X|1|1
24			2|1|1			1|1|X				X|1|1
25			1|1|2		1|1|1	1|X|1		1|X|1		1|1|1
26			1|2|X		1|1|1			X|1|1
27			1|X|2		1|1|1	1|1|X				X|1|1
28			2|1|X		1|1|2	1|X|1		1|X|1		1|1|1
29			1|X|1					1|2|X
30			X|1|1		1|1|1
31			1|1|1		1|1|1			1|X|1
32			1|1|X		1|1|1
33			1|X|2		1|1|1
34			1|X|1		1|1|1
35			1|X|1		1|1|X
36			X|1|1
37			1|1|1		1|1|1
38			1|1|2		1|1|X
39			1|2|1
40			2|1|1		1|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
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
Sheet5

Thank you in advance

Regards,
Kishan

 

[Kishan]

Hi, or may I get solution for only 1 pattern 1|1|1? As shown below

Book1
	A	B	C	D	E	F
1
2
3
4			Patt		Patt
5			Patt		1|1|1
6			X|1|1		1|1|1
7			1|1|1		1|1|X
8			1|1|X
9			1|X|X		1|1|1
10			1|X|1		1|1|1
11			X|1|1		1|1|1
12			1|1|1		1|1|X
13			1|1|1
14			1|1|1		1|1|1
15			1|1|X		1|1|1
16			1|X|1		1|1|1
17			X|1|1		1|1|X
18			1|1|1
19			1|1|1		1|1|1
20			1|1|1		1|1|X
21			1|1|X
22			1|X|X		1|1|1
23			1|2|1		1|1|2
24			2|1|1
25			1|1|2		1|1|1
26			1|2|X		1|1|1
27			1|X|2		1|1|1
28			2|1|X		1|1|2
29			1|X|1
30			X|1|1		1|1|1
31			1|1|1		1|1|1
32			1|1|X		1|1|1
33			1|X|2		1|1|1
34			1|X|1		1|1|1
35			1|X|1		1|1|X
36			X|1|1
37			1|1|1		1|1|1
38			1|1|2		1|1|X
39			1|2|1
40			2|1|1		1|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
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
Sheet5

Thank you in advance

Regards,
Kishan

 

[dave2018]

Why does E6 have a match in your last example? (i think some of your data results may be incorrect, based on your question...)

Also, do the colors matter? (ie. is this a formula or a VBA question)

 

[Kishan]

Why does E6 have a match in your last example? (i think some of your data results may be incorrect, based on your question...)

Also, do the colors matter? (ie. is this a formula or a VBA question)

Hi, dave2018 in the E5 I have pattern 1|1|1, which I want to extract from column C, so first patt find in the cell C7 and next to it E8, which is shown in the cells E6:E7, second find in cells C12, 13, 14 and next to it E15, which is shown in cells E9:E12.... an so on

Colour is no matter it is filled and shown to explain example clearly

Thank you in advance

Regards,
Kishan

 

[dave2018]

I'm sorry. i still dont understand how the numbers are supposed to appear in their respective cells based on the criteria in the header rows...

 

[Kishan]

I'm sorry. i still dont understand how the numbers are supposed to appear in their respective cells based on the criteria in the header rows...

Hi, dave2018

In the cells C6:C69 I got the data, and in the cell E5 I have the patter "1|1|1", which I want to look in column C
Bring out all "1|1|1" from C6:C69 along with 1 next following pattern

First pattern "1|1|1" found in column C, in the cell C7, so copy "C7 and next C8" and paste into the cells E6:E7

Second pattern "1|1|1" found in the column C, in the cells C12, 13, 14, so copy "C12, C13, C14 and next C15" and paste into the cells E9:E12

Third pattern "1|1|1" found in the column C, in the cells C18, 19, 20, so copy "C18, C19, C20 and next C21" and paste into the cells E14:E17

May this help

Formula will work; I prefer the VBA solution if possible

Thank you in advance

Regards,
Kishan

 

[dave2018]

oh i get it, the values in E do not correspond by ROW to the values in C...

Getting right to work on it...

 

[Kishan]

oh i get it, the values in E do not correspond by ROW to the values in C...

Getting right to work on it...

Hi, dave2018 it is correct you got it

Thank you in advance

Regards,
Kishan

 

[Kishan]

Hi, any suggestions

Question in post_3 and explained in post_7

Regards,
Kishan