# 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  Reply With Quote

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)  Reply With Quote

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?  Reply With Quote

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?  Reply With Quote

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  Reply With Quote

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})))  Reply With Quote

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!  Reply With Quote

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

Glad it works for you!   Reply With Quote

## User Tag List

#### Tags for this Thread

multiple, number, rows, set, text #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•