I need some help doing the following:

[Guest]

Below are the first six rows of my sheet (it continues on down the sheet in
the same way, just with different dates and teams)
A B C D E
F
Nov 3 2005, Thu AWAY Avalanche
Nov 4 2005, Fri HOME Sharks
Nov 6 2005, Sun HOME Wild
Nov 12 2005, Sat AWAY Coyotes
Nov 14 2005, Mon AWAY Bruins
Nov 15 2005, Tue AWAY Senators

I would like to have a column that lists the dates, another column list the
teams when AWAY occurs at least three times in a row. From the above this is
what I would like to happen:
M1 = Nov
N1 = 12
O1 = Coyotes

M2 = Nov
N2 = 14
O2 = Bruins

M3 = Nov
N3 = 15
O3 = Senators

What formula would I use in columns M, N, and O to do this? Thanks for the
help.

 

[Otto Moehrbach]

You say "another column list the
teams when AWAY occurs at least three times in a row." None of your samples
have "AWAY" more than once. Please post back and clarify what you want. Is
it that you want the data copied if AWAY appears in a specific row? HTH
Otto

 

[Guest]

Sorry, I meant at least three times in a column. Here is my post again in a
little clearer format.

Below are the first six rows of my sheet (it continues on down the sheet
in the same way, just with different dates and teams)

A B C D E F
Nov 3 2005 Thu AWAY Avalanche
Nov 4 2005 Fri HOME Sharks
Nov 6 2005 Sun HOME Wild
Nov 12 2005 Sat AWAY Coyotes
Nov 14 2005 Mon AWAY Bruins
Nov 15 2005 Tue AWAY Senators

I would like to have a column that lists the dates, another column list
the teams when AWAY occurs at least three times in a columnrow. From the
above this is
what I would like to happen:
M1 = Nov
N1 = 12
O1 = Coyotes

M2 = Nov
N2 = 14
O2 = Bruins

M3 = Nov
N3 = 15
O3 = Senators

What formula would I use in columns M, N, and O to do this? Thanks for
the help.