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

AS AT AU AV 3 W 1 8-26-2002 4 W 2 2 8-28-2002 5 L 0 6 W 1 9-4-2002 7 W 2 9-7-2002 8 W 3 9-12-2002 9 W 4 4 9-14-2002 10 L 0 <tbody> </tbody>

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

Basically, the desired end result I'm seeking is this:

9-4-2002 - 9-14-2002	4
8-26-2002 - 8-28-2002	2

<tbody>
</tbody>

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]

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​		AS	AT	AU	AV		Start	End	AU
2​	2​	L	0​				9/4/2002​	9/14/2002​	4​
3​	3​	W	1​		8/26/2002​		8/26/2002​	8/28/2002​	2​
4​	4​	W	2​	2​	8/28/2002​
5​	5​	L	0​
6​	6​	W	1​		9/4/2002​
7​	7​	W	2​		9/7/2002​
8​	8​	W	3​		9/12/2002​
9​	9​	W	4​	4​	9/14/2002​
10​	10​	L	0​

Sheet: Sheet81

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!