Counting the number of rows between identical data.

[Art MacNeil]

Hello,

I have a spreadsheet that lists the number of F1 races and the drivers who
won those races.

I'm trying to count the races that elapsed between wins.

Column B contains the race #: (from 1 through 754)

Column L has the race winners.

In Column M, I want to count how many races it's been since a driver last
won a race.

For example: Giancarlo Fisichella won his 2nd race at the 2005 Australian
G. P. (Race # 732)

He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
elapsed.

Is there a formula I can use in Column M that will display 20?

Thank you for your help,

Art.

P.S. - I tried using "Match", but that only counts the number of races
between the 1st win and the most recent. This works when the most recent win
was the 2nd career win, but it won't work when a driver has had more than 2
wins.

[Biff]

Hi!

Try this: (I guess you want a progressive total):

Joe 1
xx
xx
xx
Joe 4
xx
xx
Joe 3

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))

Copy down as needed.

Biff

"Art MacNeil" <artmacneil@shaw.ca> wrote in message
news:Xsg2g.58824$WI1.770@pd7tw2no...
> Hello,
>
> I have a spreadsheet that lists the number of F1 races and the drivers
> who won those races.
>
> I'm trying to count the races that elapsed between wins.
>
> Column B contains the race #: (from 1 through 754)
>
> Column L has the race winners.
>
> In Column M, I want to count how many races it's been since a driver last
> won a race.
>
> For example: Giancarlo Fisichella won his 2nd race at the 2005 Australian
> G. P. (Race # 732)
>
> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
> elapsed.
>
> Is there a formula I can use in Column M that will display 20?
>
> Thank you for your help,
>
> Art.
>
> P.S. - I tried using "Match", but that only counts the number of races
> between the 1st win and the most recent. This works when the most recent
> win was the 2nd career win, but it won't work when a driver has had more
> than 2 wins.
>
>
>
>
>

[Art MacNeil]

>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
> news:Xsg2g.58824$WI1.770@pd7tw2no...
>> Hello,
>>
>> I have a spreadsheet that lists the number of F1 races and the drivers
>> who won those races.
>>
>> I'm trying to count the races that elapsed between wins.
>>
>> Column B contains the race #: (from 1 through 754)
>>
>> Column L has the race winners.
>>
>> In Column M, I want to count how many races it's been since a driver last
>> won a race.
>>
>> For example: Giancarlo Fisichella won his 2nd race at the 2005
>> Australian G. P. (Race # 732)
>>
>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
>> elapsed.
>>
>> Is there a formula I can use in Column M that will display 20?
>>
>> Thank you for your help,
>>
>> Art.
>>
>> P.S. - I tried using "Match", but that only counts the number of races
>> between the 1st win and the most recent. This works when the most recent
>> win was the 2nd career win, but it won't work when a driver has had more
>> than 2 wins.
>>


>"Biff" <biffinpitt@comcast.net> wrote in message
>news:%23vhaSmcZGHA.3992@TK2MSFTNGP05.phx.gbl...
> Hi!
>
> Try this: (I guess you want a progressive total):
>
> Joe 1
> xx
> xx
> xx
> Joe 4
> xx
> xx
> Joe 3
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> =IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))
>
> Copy down as needed.
>
> Biff



Thanks Biff.

I tried it and it seems like it should work but it didn't. When I dragged
the formula down, every row displayed a 1 as a result. I changed your "L1"
and "B1" references to "L2" and "B2" because my 1st row is used for column
titles. The weird thing is, when I look at the function, the TRUE answer is
displayed as 1, and the FALSE answer is correct, but is doesn't appear in
the cell, a "1" does.. So the formula works, but it always displays a 1 when
the FALSE condition applies. Any ideas?

Thank you,

Art.

[Biff]

The formula is an array formula. For it to work properly you MUST enter it
using the key combination of CTRL,SHIFT,ENTER.

Select the first formula cell in column M.

Double click that cell to be in Edit mode.
Hold down both the CTRL key and the SHIFT key then hit ENTER.
When done properly Excel will enclose the formula in squiggly braces { }.
You cannot just type the braces in, you MUST use the key combo. Also, if you
edit an array formula it MUST be re-entered as an array using the key combo.

Then copy the array formula down as needed.

The first result has to be 1!

Biff

"Art MacNeil" <artmacneil@shaw.ca> wrote in message
news:eej2g.59075$WI1.54626@pd7tw2no...
>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
>> news:Xsg2g.58824$WI1.770@pd7tw2no...
>>> Hello,
>>>
>>> I have a spreadsheet that lists the number of F1 races and the drivers
>>> who won those races.
>>>
>>> I'm trying to count the races that elapsed between wins.
>>>
>>> Column B contains the race #: (from 1 through 754)
>>>
>>> Column L has the race winners.
>>>
>>> In Column M, I want to count how many races it's been since a driver
>>> last won a race.
>>>
>>> For example: Giancarlo Fisichella won his 2nd race at the 2005
>>> Australian G. P. (Race # 732)
>>>
>>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
>>> elapsed.
>>>
>>> Is there a formula I can use in Column M that will display 20?
>>>
>>> Thank you for your help,
>>>
>>> Art.
>>>
>>> P.S. - I tried using "Match", but that only counts the number of races
>>> between the 1st win and the most recent. This works when the most recent
>>> win was the 2nd career win, but it won't work when a driver has had more
>>> than 2 wins.
>>>
>
>
>>"Biff" <biffinpitt@comcast.net> wrote in message
>>news:%23vhaSmcZGHA.3992@TK2MSFTNGP05.phx.gbl...
>> Hi!
>>
>> Try this: (I guess you want a progressive total):
>>
>> Joe 1
>> xx
>> xx
>> xx
>> Joe 4
>> xx
>> xx
>> Joe 3
>>
>> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>
>> =IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))
>>
>> Copy down as needed.
>>
>> Biff
>
>
>
> Thanks Biff.
>
> I tried it and it seems like it should work but it didn't. When I
> dragged the formula down, every row displayed a 1 as a result. I changed
> your "L1" and "B1" references to "L2" and "B2" because my 1st row is used
> for column titles. The weird thing is, when I look at the function, the
> TRUE answer is displayed as 1, and the FALSE answer is correct, but is
> doesn't appear in the cell, a "1" does.. So the formula works, but it
> always displays a 1 when the FALSE condition applies. Any ideas?
>
> Thank you,
>
> Art.
>

[Domenic]

Here's a slight variation of Biff's solution...

First, define the following name...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

M2, copied down:

=LOOKUP(BigNum,CHOOSE({1,2},0,SUM(LARGE(IF($L$2:L2=L2,$B$2:B2),{1,2})*{1,
-1})))

Hope this helps!

In article <Xsg2g.58824$WI1.770@pd7tw2no>,
"Art MacNeil" <artmacneil@shaw.ca> wrote:

> Hello,
>
> I have a spreadsheet that lists the number of F1 races and the drivers who
> won those races.
>
> I'm trying to count the races that elapsed between wins.
>
> Column B contains the race #: (from 1 through 754)
>
> Column L has the race winners.
>
> In Column M, I want to count how many races it's been since a driver last
> won a race.
>
> For example: Giancarlo Fisichella won his 2nd race at the 2005 Australian
> G. P. (Race # 732)
>
> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
> elapsed.
>
> Is there a formula I can use in Column M that will display 20?
>
> Thank you for your help,
>
> Art.
>
> P.S. - I tried using "Match", but that only counts the number of races
> between the 1st win and the most recent. This works when the most recent win
> was the 2nd career win, but it won't work when a driver has had more than 2
> wins.

[Art MacNeil]

Right you are. It works now, thank you.


"Biff" <biffinpitt@comcast.net> wrote in message
news:ubNDKSdZGHA.3972@TK2MSFTNGP04.phx.gbl...
> The formula is an array formula. For it to work properly you MUST enter it
> using the key combination of CTRL,SHIFT,ENTER.
>
> Select the first formula cell in column M.
>
> Double click that cell to be in Edit mode.
> Hold down both the CTRL key and the SHIFT key then hit ENTER.
> When done properly Excel will enclose the formula in squiggly braces { }.
> You cannot just type the braces in, you MUST use the key combo. Also, if
> you edit an array formula it MUST be re-entered as an array using the key
> combo.
>
> Then copy the array formula down as needed.
>
> The first result has to be 1!
>
> Biff
>
> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
> news:eej2g.59075$WI1.54626@pd7tw2no...
>>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
>>> news:Xsg2g.58824$WI1.770@pd7tw2no...
>>>> Hello,
>>>>
>>>> I have a spreadsheet that lists the number of F1 races and the drivers
>>>> who won those races.
>>>>
>>>> I'm trying to count the races that elapsed between wins.
>>>>
>>>> Column B contains the race #: (from 1 through 754)
>>>>
>>>> Column L has the race winners.
>>>>
>>>> In Column M, I want to count how many races it's been since a driver
>>>> last won a race.
>>>>
>>>> For example: Giancarlo Fisichella won his 2nd race at the 2005
>>>> Australian G. P. (Race # 732)
>>>>
>>>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
>>>> elapsed.
>>>>
>>>> Is there a formula I can use in Column M that will display 20?
>>>>
>>>> Thank you for your help,
>>>>
>>>> Art.
>>>>
>>>> P.S. - I tried using "Match", but that only counts the number of races
>>>> between the 1st win and the most recent. This works when the most
>>>> recent win was the 2nd career win, but it won't work when a driver has
>>>> had more than 2 wins.
>>>>
>>
>>
>>>"Biff" <biffinpitt@comcast.net> wrote in message
>>>news:%23vhaSmcZGHA.3992@TK2MSFTNGP05.phx.gbl...
>>> Hi!
>>>
>>> Try this: (I guess you want a progressive total):
>>>
>>> Joe 1
>>> xx
>>> xx
>>> xx
>>> Joe 4
>>> xx
>>> xx
>>> Joe 3
>>>
>>> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>>
>>> =IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))
>>>
>>> Copy down as needed.
>>>
>>> Biff
>>
>>
>>
>> Thanks Biff.
>>
>> I tried it and it seems like it should work but it didn't. When I
>> dragged the formula down, every row displayed a 1 as a result. I changed
>> your "L1" and "B1" references to "L2" and "B2" because my 1st row is used
>> for column titles. The weird thing is, when I look at the function, the
>> TRUE answer is displayed as 1, and the FALSE answer is correct, but is
>> doesn't appear in the cell, a "1" does.. So the formula works, but it
>> always displays a 1 when the FALSE condition applies. Any ideas?
>>
>> Thank you,
>>
>> Art.
>>
>
>

[Art MacNeil]

That is an interesting idea. I may not use it for this problem but
hopefully I can use it to solve another one. Thank you for your time.


"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-EE0689.08464322042006@msnews.microsoft.com...
> Here's a slight variation of Biff's solution...
>
> First, define the following name...
>
> Insert > Name > Define
>
> Name: BigNum
>
> Refers to:
>
> =9.99999999999999E+307
>
> Click Ok
>
> Then try the following formula, which needs to be confirmed with
> CONTROL+SHIFT+ENTER...
>
> M2, copied down:
>
> =LOOKUP(BigNum,CHOOSE({1,2},0,SUM(LARGE(IF($L$2:L2=L2,$B$2:B2),{1,2})*{1,
> -1})))
>
> Hope this helps!
>
> In article <Xsg2g.58824$WI1.770@pd7tw2no>,
> "Art MacNeil" <artmacneil@shaw.ca> wrote:
>
>> Hello,
>>
>> I have a spreadsheet that lists the number of F1 races and the drivers
>> who
>> won those races.
>>
>> I'm trying to count the races that elapsed between wins.
>>
>> Column B contains the race #: (from 1 through 754)
>>
>> Column L has the race winners.
>>
>> In Column M, I want to count how many races it's been since a driver last
>> won a race.
>>
>> For example: Giancarlo Fisichella won his 2nd race at the 2005
>> Australian
>> G. P. (Race # 732)
>>
>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
>> elapsed.
>>
>> Is there a formula I can use in Column M that will display 20?
>>
>> Thank you for your help,
>>
>> Art.
>>
>> P.S. - I tried using "Match", but that only counts the number of races
>> between the 1st win and the most recent. This works when the most recent
>> win
>> was the 2nd career win, but it won't work when a driver has had more than
>> 2
>> wins.

[Biff]

You're welcome!

Biff

"Art MacNeil" <artmacneil@shaw.ca> wrote in message
news:zYs2g.60021$P01.38667@pd7tw3no...
> Right you are. It works now, thank you.
>
>
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:ubNDKSdZGHA.3972@TK2MSFTNGP04.phx.gbl...
>> The formula is an array formula. For it to work properly you MUST enter
>> it using the key combination of CTRL,SHIFT,ENTER.
>>
>> Select the first formula cell in column M.
>>
>> Double click that cell to be in Edit mode.
>> Hold down both the CTRL key and the SHIFT key then hit ENTER.
>> When done properly Excel will enclose the formula in squiggly braces { }.
>> You cannot just type the braces in, you MUST use the key combo. Also, if
>> you edit an array formula it MUST be re-entered as an array using the key
>> combo.
>>
>> Then copy the array formula down as needed.
>>
>> The first result has to be 1!
>>
>> Biff
>>
>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
>> news:eej2g.59075$WI1.54626@pd7tw2no...
>>>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
>>>> news:Xsg2g.58824$WI1.770@pd7tw2no...
>>>>> Hello,
>>>>>
>>>>> I have a spreadsheet that lists the number of F1 races and the
>>>>> drivers who won those races.
>>>>>
>>>>> I'm trying to count the races that elapsed between wins.
>>>>>
>>>>> Column B contains the race #: (from 1 through 754)
>>>>>
>>>>> Column L has the race winners.
>>>>>
>>>>> In Column M, I want to count how many races it's been since a driver
>>>>> last won a race.
>>>>>
>>>>> For example: Giancarlo Fisichella won his 2nd race at the 2005
>>>>> Australian G. P. (Race # 732)
>>>>>
>>>>> He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
>>>>> elapsed.
>>>>>
>>>>> Is there a formula I can use in Column M that will display 20?
>>>>>
>>>>> Thank you for your help,
>>>>>
>>>>> Art.
>>>>>
>>>>> P.S. - I tried using "Match", but that only counts the number of races
>>>>> between the 1st win and the most recent. This works when the most
>>>>> recent win was the 2nd career win, but it won't work when a driver has
>>>>> had more than 2 wins.
>>>>>
>>>
>>>
>>>>"Biff" <biffinpitt@comcast.net> wrote in message
>>>>news:%23vhaSmcZGHA.3992@TK2MSFTNGP05.phx.gbl...
>>>> Hi!
>>>>
>>>> Try this: (I guess you want a progressive total):
>>>>
>>>> Joe 1
>>>> xx
>>>> xx
>>>> xx
>>>> Joe 4
>>>> xx
>>>> xx
>>>> Joe 3
>>>>
>>>> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>>>
>>>> =IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))
>>>>
>>>> Copy down as needed.
>>>>
>>>> Biff
>>>
>>>
>>>
>>> Thanks Biff.
>>>
>>> I tried it and it seems like it should work but it didn't. When I
>>> dragged the formula down, every row displayed a 1 as a result. I changed
>>> your "L1" and "B1" references to "L2" and "B2" because my 1st row is
>>> used for column titles. The weird thing is, when I look at the function,
>>> the TRUE answer is displayed as 1, and the FALSE answer is correct, but
>>> is doesn't appear in the cell, a "1" does.. So the formula works, but it
>>> always displays a 1 when the FALSE condition applies. Any ideas?
>>>
>>> Thank you,
>>>
>>> Art.
>>>
>>
>>
>
>