Finding dates for streaks

[miles00577]

I've got a yearly history of game results and have just started incorporating winning/losing streaks. I've got a top 10 list of longest streaks, but I want to incorporate the dates of each streak. For example, on the data below, I want to be able to pull the date data from the first and last row of each streak.

COLUMNS
[TABLE="width: 241"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]AS[/TD]
[TD]AT[/TD]
[TD]AU[/TD]
[TD]AV[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]W[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]8-26-2002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]W[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]8-28-2002[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]L[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]W[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]9-4-2002[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]W[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]9-7-2002[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]W[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]9-12-2002[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]W[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]9-14-2002[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]L[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Basically, the desired end result I'm seeking is this:
[TABLE="width: 300"]
<tbody>[TR]
[TD]9-4-2002 - 9-14-2002[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8-26-2002 - 8-28-2002[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I've already got the right column to find LARGE in the AU Column from the spreadsheet, but not quite sure how get the desired end result for the date data.

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td]AS[/td][td]AT[/td][td]AU[/td][td]AV[/td][td][/td][td]Start[/td][td]End[/td][td]AU[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

2​

[/td][td]L[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td]

9/4/2002​

[/td][td]

9/14/2002​

[/td][td]

4​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

3​

[/td][td]W[/td][td]

1​

[/td][td][/td][td]

8/26/2002​

[/td][td][/td][td]

8/26/2002​

[/td][td]

8/28/2002​

[/td][td]

2​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

4​

[/td][td]W[/td][td]

2​

[/td][td]

2​

[/td][td]

8/28/2002​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

5​

[/td][td]L[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

6​

[/td][td]W[/td][td]

1​

[/td][td][/td][td]

9/4/2002​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

7​

[/td][td]W[/td][td]

2​

[/td][td][/td][td]

9/7/2002​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

8​

[/td][td]W[/td][td]

3​

[/td][td][/td][td]

9/12/2002​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

9​

[/td][td]W[/td][td]

4​

[/td][td]

4​

[/td][td]

9/14/2002​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

10​

[/td][td]L[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet81[/td][/tr][/table]

Formula in cell G2:

=INDEX($E$2:$E$9,LOOKUP(2,1/($E$2:INDEX($E$2:$E$9,MATCH(I2,$D$2:$D$9,0))=""),MATCH(ROW($E$2:INDEX($E$2:$E$9,MATCH(I2,$D$3:$D$9,0))),ROW($E$2:INDEX($E$2:$E$9,MATCH(I2,$D$2:$D$9,0)))))+1)

Formula in H2:
=INDEX($E$3:$E$9,MATCH(I2,$D$3:$D$9,0))

Formula in I2:
=LARGE($D$3:$D$10,ROWS($A$1:A1))

 

[miles00577]

The formula in H2 and I2 work fine. However, G2 pops up with an #N/A. I should add that the D2 runs the following formula down the column so that only the winning streak number appears:

=IF(C3=0,C2," ")

Also, if I get duplicate winning streaks, the end date (and eventually the start date once we get the formula correct) will repeat with the same dates from the first duplicate streak. I know I will be able to work around this by assigning a unique game ID number in a separate column.

 

[miles00577]

I GOT IT TO WORK! I took out the space on the =IF formula I added in the previous reply. Thanks so much. This saves time from trying to locate the dates in the spreadsheet and typing them out. I figured there was a way this could be done. Thanks for pointing me in the right direction.

 

[Oscar Cronquist]

You are welcome!