Find nth and nth + 1 values in a column
 

I am in charge of scheduling for a basketball league. In simplified
terms, I have 3 columns of data - the first contains a date, the
second has Team1 and the second has Team2. A team's name can occur in
either column (just not both at the same time). I would like to be
able to see how many days are between a team's current and previous
game. The math is fairly simple, but how do you find the 4th (or 12th
or 30th) occurence of a value in a column and then reference the
corresponding date so that you can compare that to the 3rd game's
date?

Thanks in advance,
Pete

Find nth and nth + 1 values in a column
 

=Small(If(($B$1:$B$200="Team1")+($C$2:$C$200="Team 1"),$A$1:$A$200),1)
Entered with Ctrl+Shift+Enter rather than just enter will give you the date
of the 1st game. Change the 1 to the Nth game.

This would give you the days between the 1st and second games

=SMALL(IF(($B$1:$B$200="Team1")+($C$1:$C$200="Team 1"),$A$1:$A$200),2)-SMALL(
IF(($B$1:$B$200="Team1")+($C$1:$C$200="Team1"),$A$ 1:$A$200),1)-1

Again, entered with Ctrl+Shift+Enter Rather than just enter

--
Regards,
Tom Ogilvy


"Pete" wrote in message
om...
I am in charge of scheduling for a basketball league. In simplified
terms, I have 3 columns of data - the first contains a date, the
second has Team1 and the second has Team2. A team's name can occur in
either column (just not both at the same time). I would like to be
able to see how many days are between a team's current and previous
game. The math is fairly simple, but how do you find the 4th (or 12th
or 30th) occurence of a value in a column and then reference the
corresponding date so that you can compare that to the 3rd game's
date?

Thanks in advance,
Pete

Find nth and nth + 1 values in a column
 

Add a UserForm (and macro to show it). Put a TextBox on the
Userform to enter a team, with the following code. It will show
the number of days between all games.

HTH,
Merjet

Private Sub TextBox1_Change()
Dim iCt As Integer
Dim c As Range
Dim rng As Range
Dim dtLast As Date

'Change next line to fit data. Col B assumed to
' hold 1st of 2 columns with team names
Set rng = Sheets("Sheet1").Range("B2:B13")
For Each c In rng
If c = TextBox1 Or c.Offset(0, 1) = TextBox1 Then
iCt = iCt + 1
If iCt 1 Then
MsgBox "Game " & iCt & " is " & c.Offset(0, -1) _
- dtLast & " days after game " & iCt - 1
End If
dtLast = c.Offset(0, -1)
End If
Next c
End Sub

Find nth and nth + 1 values in a column
 

Pete,

Add 2 more columns - Team1 Next Game and Team 2 Next Game.
Assuming your data goes from A2:C100
In D2 (Team1 Next Game) put
=INDEX(A3:A100,IF(ISERROR(MATCH(B2,B3:B100,0)),IF( ISERROR(MATCH
(B2,C3:C100,0)),"n",MATCH(B2,C3:C100,0)),MATCH(B2, B3:B100,0)))-A2

In E2 (Team2 Next Game) put
=INDEX(A3:A100,IF(ISERROR(MATCH(C2,B3:B100,0)),IF( ISERROR(MATCH
(C2,C3:C100,0)),"n",MATCH(C2,C3:C100,0)),MATCH(C2, B3:B100,0)))-A2

Fill these two columns down this will give you columns showing
when each team's next game is.

Dan E

"Pete" wrote in message om...
I am in charge of scheduling for a basketball league. In simplified
terms, I have 3 columns of data - the first contains a date, the
second has Team1 and the second has Team2. A team's name can occur in
either column (just not both at the same time). I would like to be
able to see how many days are between a team's current and previous
game. The math is fairly simple, but how do you find the 4th (or 12th
or 30th) occurence of a value in a column and then reference the
corresponding date so that you can compare that to the 3rd game's
date?

Thanks in advance,
Pete

Find nth and nth + 1 values in a column
 

I should have mentioned it gives the answer in days, if it shows up as
a date, simply format the cells as general or number

Dan E

"Dan E" wrote in message ...
Pete,

Add 2 more columns - Team1 Next Game and Team 2 Next Game.
Assuming your data goes from A2:C100
In D2 (Team1 Next Game) put
=INDEX(A3:A100,IF(ISERROR(MATCH(B2,B3:B100,0)),IF( ISERROR(MATCH
(B2,C3:C100,0)),"n",MATCH(B2,C3:C100,0)),MATCH(B2, B3:B100,0)))-A2

In E2 (Team2 Next Game) put
=INDEX(A3:A100,IF(ISERROR(MATCH(C2,B3:B100,0)),IF( ISERROR(MATCH
(C2,C3:C100,0)),"n",MATCH(C2,C3:C100,0)),MATCH(C2, B3:B100,0)))-A2

Fill these two columns down this will give you columns showing
when each team's next game is.

Dan E

"Pete" wrote in message om...
I am in charge of scheduling for a basketball league. In simplified
terms, I have 3 columns of data - the first contains a date, the
second has Team1 and the second has Team2. A team's name can occur in
either column (just not both at the same time). I would like to be
able to see how many days are between a team's current and previous
game. The math is fairly simple, but how do you find the 4th (or 12th
or 30th) occurence of a value in a column and then reference the
corresponding date so that you can compare that to the 3rd game's
date?

Thanks in advance,
Pete