Obtain historical time period for pattern

[Kishan]

Using Excel 2000

Hi,

I got patterns data in cells C6:C85
In the cells E5:G5 I got 3 patterns,
In the cell C4 got 1 pattern X|X|1, what I want look this pattern from C6:C85 and get historical time period for the following patterns are listed in E5:G5

For example after the X|X|1 following pattern X|1|2 do have historical period 7 from the C6 place 7 in the under X|1|2 in G6

For example after the X|X|1 following pattern X|1|1 do have historical period 12 from the C6 place 12 in the under X|1|1 in E6

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

Example data...

Book1
	A	B	C	D	E	F	G	H
1
2
3
4			X|X|1
5			Patt		X|1|1	X|1|X	X|1|2
6			X|1|1		12	18	7
7			1|1|1		23		41
8			1|1|1		27		64
9			1|1|X		33		67
10			1|X|X		51		80
11			X|X|1		57
12			X|1|2	7
13			1|2|1
14			X|1|X
15			1|X|X
16			X|X|1
17			X|1|1	12
18			1|1|1
19			1|1|1
20			1|X|X
21			X|X|X
22			X|X|1
23			X|1|X	18
24			1|X|1
25			X|1|1
26			1|X|X
27			X|X|1
28			X|1|1	23
29			1|1|1
30			1|X|X
31			X|X|1
32			X|1|1	27
33			1|X|1
34			X|1|1
35			1|1|X
36			1|X|X
37			X|X|1
38			X|1|1	33
39			1|1|1
40			1|1|1
41			1|2|1
42			1|1|X
43			1|X|X
44			X|X|X
45			X|X|1
46			X|1|2	41
47			1|2|1
48			2|1|1
49			1|1|1
50			1|1|2
51			1|2|1
52			2|1|1
53			1|1|X
54			1|X|X
55			X|X|1
56			X|1|1	51
57			1|1|1
58			1|1|2
59			1|2|X
60			2|X|X
61			X|X|1
62			X|1|1	57
63			1|1|2
64			1|2|2
65			2|2|1
66			2|1|X
67			1|X|X
68			X|X|1
69			X|1|2	64
70			1|2|X
71			X|X|1
72			X|1|2	67
73			1|2|X
74			2|X|2
75			1|X|1
76			X|1|2
77			1|2|1
78			2|1|1
79			1|1|1
80			1|1|1
81			1|1|X
82			1|X|X
83			X|X|X
84			X|X|1
85			X|1|2	80
86
87
Sheet4

Thank you in advance

Regards,
Kishan

 

[Kishan]

Please need help, I do appreciate better if can have VBA otherwise formula if it is a easy

Regards,
Kishan

 

[offthelip]

Your question is not clear: you mention

get historical time period

but there is no indication as to what this relates to, there no column with title "Time period" there are no time values on the example, so we are just trying to guess what you want. My guess is "42" the answer to everything. I hope that helps

 

[markmzz]

Using Excel 2000

Hi,

I got patterns data in cells C6:C85
In the cells E5:G5 I got 3 patterns,
In the cell C4 got 1 pattern X|X|1, what I want look this pattern from C6:C85 and get historical time period for the following patterns are listed in E5:G5

For example after the X|X|1 following pattern X|1|2 do have historical period 7 from the C6 place 7 in the under X|1|2 in G6

For example after the X|X|1 following pattern X|1|1 do have historical period 12 from the C6 place 12 in the under X|1|1 in E6

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

Example data...

Thank you in advance

Regards,
Kishan

Hi!

If I understand correctly what you want, maybe the Array Formula below can helps.

Use Ctrl+Shift+Enter to enter the formula

=IF(ROWS(E$6:E6)>SUM(($C$6:$C$85=E$5)*($C$5:$C$84=$C$4)),"",
SMALL(IF($C$6:$C$85=E$5,IF($C$5:$C$84=$C$4,ROW($C$6:$C$85)-ROW($C$6)+1)),ROWS(E$6:E6)))

Ps: I don't have Excel 2000 in my computer, I tested the formula above in Excel 2003 and it works.

Markmzz

 

[Kishan]

Your question is not clear: you mention but there is no indication as to what this relates to, there no column with title "Time period" there are no time values on the example, so we are just trying to guess what you want. My guess is "42" the answer to everything. I hope that helps

Hi offthelip, C4 Pattern X|X|1 is the key pattern, which I wanted to look in column C starting from cell C6 to down, basing to this key pattern I wanted to look 3 patterns which are in cells E5:G5 after who much time appears below the Key Pattern X|X|1

So if you see below the key pattern "X|X|1", X|1|2 has appeared in row 12 calculating time from C6 time period is = 7, so far result period 7 is shown below the pattern X|1|2 in the cell G7 = 7

Next if you see below the key pattern "X|X|1", X|1|1 has appeared in row 17 calculating time from C6 time period is = 12, so far result period 12 is shown below the pattern X|1|1 in the cell E7 = 12

In other words you can say who much time is being delayed each pattern is listed in cells E5:G5 below the key pattern X|X|1 in the column C From Cell C6

Hope this helps

Thank you for looking in it.

Have a nice weekend

Kind Regards,
Kishan

 

[Kishan]

Hi!

If I understand correctly what you want, maybe the Array Formula below can helps.

Use Ctrl+Shift+Enter to enter the formula

=IF(ROWS(E$6:E6)>SUM(($C$6:$C$85=E$5)*($C$5:$C$84=$C$4)),"",
SMALL(IF($C$6:$C$85=E$5,IF($C$5:$C$84=$C$4,ROW($C$6:$C$85)-ROW($C$6)+1)),ROWS(E$6:E6)))

Ps: I don't have Excel 2000 in my computer, I tested the formula above in Excel 2003 and it works.

Markmzz

Hi markmzz,

Thank you for giving a formula it is working flawless, and giving a result as request

Have a nice weekend

Kind Regards,
Kishan

 

[markmzz]

Hi markmzz,

Thank you for giving a formula it is working flawless, and giving a result as request

Have a nice weekend

Kind Regards,
Kishan

Hi Kishan,

You are welcome.

I'm glad to help and thanks for the feedback.

By the way, have a nice weekend too.

Markmzz