Counting Baseball Series

  • I have a spreadsheet with baseball scores. I can't get my head around how to count individual series. Column L has most of what I need, but the games April 9 to 11 are series 1, games August 27 to 29 series 2, etc.


    What sort of formula (helper columns are ok) would I need to look at cell L660, determine that its the same series as L661 and L662, but different than series in L780.


    Thanks.



    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Baseball 1.jpg","data-attachmentid":1205332}[/ATTACH]

  • I know nothing about baseball, so how would I know that L780 is a different series? If I don't know, I can't tell Excel how to know.


    Attach a workbook if you can - we can't do anything with your image, and we aren't going to recreate your data in order to help you. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Oops, forgot my manners please see the attached Baseball Example.xlsm


    In baseball, a team will travel to a city, play a number of games, then move on to another city. [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 45"]Year[/TD]
    [TD="width: 32"]Gm#[/TD]
    [TD="width: 158"]Date[/TD]
    [TD="width: 36"]M[/TD]
    [TD="width: 26"]D[/TD]
    [TD="width: 32"]Loc[/TD]
    [TD="width: 68"]Opp[/TD]
    [TD="width: 113"]Series[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]1[/TD]
    [TD="align: left"]Monday, Mar 31[/TD]
    [TD="align: left"]Mar[/TD]
    [TD="align: right"]31[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]2[/TD]
    [TD="align: left"]Tuesday, Apr 1[/TD]
    [TD="align: left"]Apr[/TD]
    [TD="align: right"]1[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]3[/TD]
    [TD="align: left"]Wednesday, Apr 2[/TD]
    [TD="align: left"]Apr[/TD]
    [TD="align: right"]2[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]4[/TD]
    [TD="align: left"]Thursday, Apr 3[/TD]
    [TD="align: left"]Apr[/TD]
    [TD="align: right"]3[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]94[/TD]
    [TD="align: left"]Friday, Jul 11[/TD]
    [TD="align: left"]Jul[/TD]
    [TD="align: right"]11[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]95[/TD]
    [TD="align: left"]Saturday, Jul 12[/TD]
    [TD="align: left"]Jul[/TD]
    [TD="align: right"]12[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]96[/TD]
    [TD="align: left"]Sunday, Jul 13[/TD]
    [TD="align: left"]Jul[/TD]
    [TD="align: right"]13[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]137[/TD]
    [TD="align: left"]Tuesday, Sep 2[/TD]
    [TD="align: left"]Sep[/TD]
    [TD="align: right"]2[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]138[/TD]
    [TD="align: left"]Wednesday, Sep 3[/TD]
    [TD="align: left"]Sep[/TD]
    [TD="align: right"]3[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2014[/TD]
    [TD="align: right"]139[/TD]
    [TD="align: left"]Thursday, Sep 4[/TD]
    [TD="align: left"]Sep[/TD]
    [TD="align: right"]4[/TD]

    [td]

    A

    [/td]


    [td]

    TBR

    [/td]


    [td]

    2014 TBR A

    [/td]


    [/tr]


    [/TABLE]

    In this case, the Blue Jays traveled to Tampa Bay on March 31st, played a game four games over four days, then left. So its:


    2014 (Year)
    TBR (Tampa Bay Rays)
    A (Away)


    They returned to Tampa Bay on Friday July 11 to play three games, then on September 2 to play three games.


    I am looking for a formula (this spreadsheet has data since 1977 can't manually do it easily) that will provide me with something like:


    2014 TBR A #1 (for Mar 31 to Apr 3)
    2014 TBR A #2 (Jul 11 to 13)
    2014 TBR A #3 (Sep 2 to Sep 4)


    Problems I'm having are:

    • Series can be 1, 2, 3, or 5 games
    • Dates are mostly, but not always, consecutive

    Thanks.

  • Thanks - I will have a look at this later on. I don't have time this morning - sorry.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • OK, so I think I have what you want. It needs two helper columns, which can be hidden:


    Excel 2016 (Windows) 32 bit [TABLE="class: head"]
    [TR="bgcolor: #888888"]
    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]1[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]Year[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]Gm#[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]Date[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]DoW[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]M[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]D[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]Loc[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]Opp[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]Helper[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]Helper[/SIZE]


    [/TD]
    [TD="bgcolor: #FFFF00"]

    [SIZE=10px]Series[/SIZE]


    [/TD]
    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]2[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]1[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Monday, Mar 31[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Monday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Mar[/SIZE]

    [/td]


    [td]

    [SIZE=10px]31[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]1[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #1[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]3[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Tuesday, Apr 1[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Tuesday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Apr[/SIZE]

    [/td]


    [td]

    [SIZE=10px]1[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]1[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #1[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]4[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]3[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Wednesday, Apr 2[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Wednesday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Apr[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]1[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #1[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]5[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]4[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Thursday, Apr 3[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Thursday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Apr[/SIZE]

    [/td]


    [td]

    [SIZE=10px]3[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]1[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #1[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]53[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]52[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Monday, May 26[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Monday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]May[/SIZE]

    [/td]


    [td]

    [SIZE=10px]26[/SIZE]


    [/td]


    [td]

    [SIZE=10px]H[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR H[/SIZE]

    [/td]


    [td]

    [SIZE=10px]1[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR H #1[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]54[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]53[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Tuesday, May 27[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Tuesday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]May[/SIZE]

    [/td]


    [td]

    [SIZE=10px]27[/SIZE]


    [/td]


    [td]

    [SIZE=10px]H[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR H[/SIZE]

    [/td]


    [td]

    [SIZE=10px]1[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR H #1[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]55[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]54[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Wednesday, May 28[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Wednesday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]May[/SIZE]

    [/td]


    [td]

    [SIZE=10px]28[/SIZE]


    [/td]


    [td]

    [SIZE=10px]H[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR H[/SIZE]

    [/td]


    [td]

    [SIZE=10px]1[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR H #1[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]95[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]94[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Friday, Jul 11[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Friday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Jul[/SIZE]

    [/td]


    [td]

    [SIZE=10px]11[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #2[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]96[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]95[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Saturday, Jul 12[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Saturday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Jul[/SIZE]

    [/td]


    [td]

    [SIZE=10px]12[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #2[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]97[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]96[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Sunday, Jul 13[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Sunday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Jul[/SIZE]

    [/td]


    [td]

    [SIZE=10px]13[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #2[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]129[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]128[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Friday, Aug 22[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Friday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Aug[/SIZE]

    [/td]


    [td]

    [SIZE=10px]22[/SIZE]


    [/td]


    [td]

    [SIZE=10px]H[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR H[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR H #2[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]130[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]129[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Saturday, Aug 23[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Saturday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Aug[/SIZE]

    [/td]


    [td]

    [SIZE=10px]23[/SIZE]


    [/td]


    [td]

    [SIZE=10px]H[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR H[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR H #2[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]131[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]130[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Sunday, Aug 24[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Sunday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Aug[/SIZE]

    [/td]


    [td]

    [SIZE=10px]24[/SIZE]


    [/td]


    [td]

    [SIZE=10px]H[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR H[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR H #2[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]138[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]137[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Tuesday, Sep 2[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Tuesday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Sep[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]3[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #3[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]139[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]138[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Wednesday, Sep 3[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Wednesday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Sep[/SIZE]

    [/td]


    [td]

    [SIZE=10px]3[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]3[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #3[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]140[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]139[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Thursday, Sep 4[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Thursday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Sep[/SIZE]

    [/td]


    [td]

    [SIZE=10px]4[/SIZE]


    [/td]


    [td]

    [SIZE=10px]A[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR A[/SIZE]

    [/td]


    [td]

    [SIZE=10px]3[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR A #3[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]147[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]146[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Friday, Sep 12[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Friday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Sep[/SIZE]

    [/td]


    [td]

    [SIZE=10px]12[/SIZE]


    [/td]


    [td]

    [SIZE=10px]H[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR H[/SIZE]

    [/td]


    [td]

    [SIZE=10px]3[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR H #3[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]148[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]147[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Saturday, Sep 13[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Saturday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Sep[/SIZE]

    [/td]


    [td]

    [SIZE=10px]13[/SIZE]


    [/td]


    [td]

    [SIZE=10px]H[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR H[/SIZE]

    [/td]


    [td]

    [SIZE=10px]3[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR H #3[/SIZE]

    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]149[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]2014[/SIZE]


    [/td]


    [td]

    [SIZE=10px]148[/SIZE]


    [/td]


    [td]

    [SIZE=10px]Sunday, Sep 14[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Sunday[/SIZE]

    [/td]


    [td]

    [SIZE=10px]Sep[/SIZE]

    [/td]


    [td]

    [SIZE=10px]14[/SIZE]


    [/td]


    [td]

    [SIZE=10px]H[/SIZE]


    [/td]


    [td]

    [SIZE=10px]TBR[/SIZE]

    [/td]


    [td]

    [SIZE=10px]2014 TBR H[/SIZE]

    [/td]


    [td]

    [SIZE=10px]3[/SIZE]


    [/td]


    [td]

    [SIZE=10px]2014 TBR H #3[/SIZE]

    [/td]


    [/TR]
    [/TABLE]
    [TABLE="class: grid"]

    [tr]


    [td]

    Sheet: database

    [/td]


    [/tr]


    [/TABLE]
    Excel 2016 (Windows) 32 bit [TABLE="class: head"]
    [TR="bgcolor: #888888"]
    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    [SIZE=10px]2[/SIZE]


    [/TD]

    [td]

    [SIZE=10px]=IF(B2=1,1,SUM(IF(FREQUENCY(IF(I$2:I2=I2,ROW(B$1:B1)),IF(I$2:I2<>I2,ROW(B$1:B1)))>0,1)))[/SIZE]


    [/td]


    [td]

    [SIZE=10px]=I2&" #"&J2[/SIZE]

    [/td]


    [/TR]
    [/TABLE]
    [TABLE="class: grid"]

    [tr]


    [td]

    Sheet: database

    [/td]


    [/tr]


    [/TABLE]
    The formula in J2 is an array formula. Enter it into J2 and then click CTRL+SHIFT+ENTER to confirm it (not just ENTER). This will make the curly brackets appear (don't try to type these yourself). Once confirmed, you can drag copy down.

  • I would appreciate knowing if this has helped you. Thanks!

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Great to know and glad to have helped! :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Ooh, what a bonus! Where were the errors and were they on something for which you could write a formula, I wonder? Happy to help some more, if so. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!