Multiple Occurances of a following value

[Golpha]

Is there a way, given a 3000 rows x 5 columns set of data, to find out how many times in that set a certain value (text or number) follows another value, either 1, 2 or 3 rows after?

Thanks
Golpha

 

[Eric W]

Welcome to the Board.

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	a	x	cat		Value1	Value2	1 row after	2 rows after	3 rows after
2	1	y	dog		a	1	5	3	2
3	2	a	mouse
4	3	2	a
5	4	1	1
6	a	a	1
7	1	3	1
8	2	1	horse
9	3	b	1
10	4	b	2
11	a	b	a
12	2	b	8
13	4	c	8
14	6	a	1
15	7	1	a
16	b	2	bc
17	a	3	de
18	1	4	f
19	2	x
20	3		g
Sheet2

Cell Formulas
Range	Formula
G2	=COUNTIFS(A1:C20,E2,A2:C21,F2)
H2	=COUNTIFS(A1:C20,E2,A3:C22,F2)
I2	=COUNTIFS(A1:C20,E2,A4:C23,F2)

 

[Golpha]

Thanks Eric
That worked a little. But if I'm looking for how many times "a" followed "1", regardless of the column, it only counts the instances when they are in the same column. Is there away to have excel look in any column in the following 1st, 2d or 3rd row?

 

[Eric W]

If you're looking for b's that follow a's, and you have a in A1 and C1, and there's a b in B2, does that count as 1 or 2?

 

[Golpha]

If you're looking for b's that follow a's, and you have a in A1 and C1, and there's a b in B2, does that count as 1 or 2?

That would be only 1 since the "b" precedes the "a" in C1

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	a	x	cat	4	x		Value1	Value2	1 row after	2 rows after	3 rows after
2	1	y	dog	1	r		a	1	8	4	7
3	2	a	mouse		cat
4	3	2	a	1	a
5	4	1	1	x	2
6	a	a	1	y	1
7	1	3	1	z	1
8	2	1	horse	a	1
9	3	b	1	x	1
10	4	b	2	y	7
11	a	b	a	1	a
12	2	b	8	x	b
13	4	c	8	t	cat
14	6	a	1	y	cat
15	7	1	a	u	d
16	b	2	bc	i	1
17	a	3	de	o	2
18	1	4	f	p	3
19	2	x
20	3		g
Sheet1

Cell Formulas
Range	Formula
I2	=SUMPRODUCT(SIGN(MMULT(--(A1:E20=G2),{1;1;1;1;1})),SIGN(MMULT(--(A2:E21=H2),{1;1;1;1;1})))
J2	=SUMPRODUCT(SIGN(MMULT(--(A1:E20=G2),{1;1;1;1;1})),SIGN(MMULT(--(A3:E22=H2),{1;1;1;1;1})))
K2	=SUMPRODUCT(SIGN(MMULT(--(A1:E20=G2),{1;1;1;1;1})),SIGN(MMULT(--(A4:E23=H2),{1;1;1;1;1})))

 

[Golpha]

Maybe:

	A	B	C	D	E	F	G	H	I	J	K
1	a	x	cat	4	x		Value1	Value2	1 row after	2 rows after	3 rows after
2	1	y	dog	1	r		a	1	8	4	7
3	2	a	mouse		cat
4	3	2	a	1	a
5	4	1	1	x	2
6	a	a	1	y	1
7	1	3	1	z	1
8	2	1	horse	a	1
9	3	b	1	x	1
10	4	b	2	y	7
11	a	b	a	1	a
12	2	b	8	x	b
13	4	c	8	t	cat
14	6	a	1	y	cat
15	7	1	a	u	d
16	b	2	bc	i	1
17	a	3	de	o	2
18	1	4	f	p	3
19	2	x
20	3		g

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I2 =SUMPRODUCT(SIGN(MMULT(--(A1:E20=G2),{1;1;1;1;1})),SIGN(MMULT(--(A2:E21=H2),{1;1;1;1;1}))) J2 =SUMPRODUCT(SIGN(MMULT(--(A1:E20=G2),{1;1;1;1;1})),SIGN(MMULT(--(A3:E22=H2),{1;1;1;1;1}))) K2 =SUMPRODUCT(SIGN(MMULT(--(A1:E20=G2),{1;1;1;1;1})),SIGN(MMULT(--(A4:E23=H2),{1;1;1;1;1}))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You are a GENIUS!!!!!!!!!
Thank you so much!

 

[Eric W]

Glad it works for you!