# Thread: Multiple Occurances of a following value Thanks: 0 Likes: 0

1. ## Multiple Occurances of a following value

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

2. ## Re: Multiple Occurances of a following value

Welcome to the Board.

Try:

ABCDEFGHI
1axcatValue1Value21 row after2 rows after3 rows after
21ydoga1532
32amouse
432a
5411
6aa1
7131
821horse
93b1
104b2
11aba
122b8
134c8
146a1
1571a
16b2bc
17a3de
1814f
192x
203g

Sheet2

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

3. ## Re: Multiple Occurances of a following value

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?

4. ## Re: Multiple Occurances of a following value

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?

5. ## Re: Multiple Occurances of a following value

Originally Posted by 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?
That would be only 1 since the "b" precedes the "a" in C1

6. ## Re: Multiple Occurances of a following value

Maybe:

ABCDEFGHIJK
1axcat4xValue1Value21 row after2 rows after3 rows after
21ydog1ra1847
32amousecat
432a1a
5411x2
6aa1y1
7131z1
821horsea1
93b1x1
104b2y7
11aba1a
122b8xb
134c8tcat
146a1ycat
1571aud
16b2bci1
17a3deo2
1814fp3
192x
203g

Sheet1

Worksheet Formulas
CellFormula
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})))

7. ## Re: Multiple Occurances of a following value

Originally Posted by Eric W
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
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})))
You are a GENIUS!!!!!!!!!
Thank you so much!