Multiple Occurances of a following value
Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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)

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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?
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Occurances of a following value

    Quote Originally Posted by Eric W View Post
    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. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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})))

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Occurances of a following value

    Quote Originally Posted by Eric W View Post
    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!

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Multiple Occurances of a following value

    Glad it works for you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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