[JAgger1]

Is there a simple formula for getting cell count beween a certain
value? Example:

In column A1:A8 I have the following numbers.

1
3
6
9
6
7
3
2

I would like a formula that can give me the cell count between
A2(number3) and A7(number 3), which would be 4.

[[email protected]]

Are you trying to count the rows between those two cells? I am not sure what yo mean by count between values. There are no values between 3 and 3; but they happen to be 4 rows apart, so would the answer be 4? Are you always going to go with cells A2 and A7? Or is always going to be the the two threes? Please explain what you are trying to accomplish, and I am sure there will be a formula that can figure it out.
Ken

On Monday, July 23, 2012 1:09:14 PM UTC-4, JAgger1 wrote:
Is there a simple formula for getting cell count beween a certain
value? Example:

In column A1:A8 I have the following numbers.

1
3
6
9
6
7
3
2

I would like a formula that can give me the cell count between
A2(number3) and A7(number 3), which would be 4.

[JAgger1]

On Jul 23, 10:14*pm, wrote:
Are you trying to count the rows between those two cells? *I am not sure what yo mean by count between values. *There are no values between 3 and 3; but they happen to be 4 rows apart, so would the answer be 4? *Are you always going to go with cells A2 and A7? *Or is always going to be the the two threes? *Please explain what you are trying to accomplish, and I am sure there will be a formula that can figure it out.
Ken



On Monday, July 23, 2012 1:09:14 PM UTC-4, JAgger1 wrote:
Is there a simple formula for getting cell count beween a certain
value? Example:

In column A1:A8 I have the following numbers.

1
3
6
9
6
7
3
2

I would like a formula that can give me the cell count between
A2(number3) and A7(number 3), which would be 4.- Hide quoted text -

- Show quoted text -

Yes, I'm trying to get a row count (in this case it's 4 rows) between
the two values (3),

[JAgger1]

I found this formula and it works well,

=ABS(MATCH(B1,A1:A8,0)-MATCH(B2,A1:A8))-1

By entering the value 3 in B1 and B2 I get my answer of 4.

Now if I have a larger range of values, with duplicates,

In column A1:A10 I have the following numbers.


1
3
6
9
6
7
3
2
4
3

anyone know how to modify this formula so I can get a correct answer
of 4 (A2 - A7) and 2 (A7 - A10)

I would like to have a formula that would work with a set of 100
(A1:A100) numbers with multiple duplicates, thanks

[JAgger1]

On Jul 24, 7:22*am, JAgger1 wrote:
On Jul 23, 10:14*pm, wrote:





Are you trying to count the rows between those two cells? *I am not sure what yo mean by count between values. *There are no values between 3 and 3; but they happen to be 4 rows apart, so would the answer be 4? *Are you always going to go with cells A2 and A7? *Or is always going to be the the two threes? *Please explain what you are trying to accomplish, and I am sure there will be a formula that can figure it out.
Ken

On Monday, July 23, 2012 1:09:14 PM UTC-4, JAgger1 wrote:
Is there a simple formula for getting cell count beween a certain
value? Example:

In column A1:A8 I have the following numbers.

1
3
6
9
6
7
3
2

I would like a formula that can give me the cell count between
A2(number3) and A7(number 3), which would be 4.- Hide quoted text -

- Show quoted text -

Yes, I'm trying to get a row count (in this case it's 4 rows) between
the two values (3),- Hide quoted text -

- Show quoted text -

Better yet, If I have a list of 100 numbers with multiple duplicate's,
can this formula:

=ABS(MATCH(BA1,A1:A100,0)-MATCH(B2,A1:A100))-1 (where B1 & B2 are the
search value, in previous case number 3)

be modified to show the largest row count between duplicates?

[Claus Busch]

Hi JAgger,

Am Tue, 24 Jul 2012 04:56:09 -0700 (PDT) schrieb JAgger1:

1
3
6
9
6
7
3
2
4
3

anyone know how to modify this formula so I can get a correct answer
of 4 (A2 - A7) and 2 (A7 - A10)

I would like to have a formula that would work with a set of 100
(A1:A100) numbers with multiple duplicates, thanks

try following formula (with 1. criteria in B1 and 2. in B2):
=MAX((A1:A1000=B2)*ZEILE(1:1000))-VERGLEICH(B1;A1:A1000;0)-1
and enter the array formula with CRTL+Shift+Enter


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Claus Busch]

Hi Jagger,

Am Tue, 24 Jul 2012 15:29:16 +0200 schrieb Claus Busch:

try following formula (with 1. criteria in B1 and 2. in B2):
=MAX((A1:A1000=B2)*ZEILE(1:1000))-VERGLEICH(B1;A1:A1000;0)-1
and enter the array formula with CRTL+Shift+Enter

sorry, I posted the formula in German. Here the formula in English:
=MAX((A1:A1000=B2)*ROW(1:1000))-MATCH(B1,A1:A1000,0)-1
and enter with CTRL+Shift+Enter


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[JAgger1]

On Jul 24, 9:36*am, Claus Busch wrote:
Hi Jagger,

Am Tue, 24 Jul 2012 15:29:16 +0200 schrieb Claus Busch:

try following formula (with 1. criteria in B1 and 2. in B2):
=MAX((A1:A1000=B2)*ZEILE(1:1000))-VERGLEICH(B1;A1:A1000;0)-1
and enter the array formula with CRTL+Shift+Enter

sorry, I posted the formula in German. Here the *formula in English:
=MAX((A1:A1000=B2)*ROW(1:1000))-MATCH(B1,A1:A1000,0)-1
and enter with CTRL+Shift+Enter

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks for your reply.

I tried your formula with a set of 1000 numbers with multiple
duplicate's, the result the formula gives me is the row count between
the first and last occurance, in this case I used the number 2 (A3 &
A1000) and it gave me the result of 996.

Can this be modified to show the largest row count between duplicates?
I've set up the list so that some duplicate's are right after each
other and some are a couple of row's apart and the largest is just
under 100 rows apart. Thanks

[[email protected]]

That seems like two right answers, 2 and 4. Is the "right" answer an array of numbers? Are you only looking for duplicate 3's? Do you want the number of rows between consecutive duplicates? Or the max difference between any two 3's.
Ken

On Tuesday, July 24, 2012 7:56:09 AM UTC-4, JAgger1 wrote:
I found this formula and it works well,

=ABS(MATCH(B1,A1:A8,0)-MATCH(B2,A1:A8))-1

By entering the value 3 in B1 and B2 I get my answer of 4.

Now if I have a larger range of values, with duplicates,

In column A1:A10 I have the following numbers.


1
3
6
9
6
7
3
2
4
3

anyone know how to modify this formula so I can get a correct answer
of 4 (A2 - A7) and 2 (A7 - A10)

I would like to have a formula that would work with a set of 100
(A1:A100) numbers with multiple duplicates, than

[JAgger1]

On Jul 24, 11:39*am, wrote:
That seems like two right answers, 2 and 4. *Is the "right" answer an array of numbers? *Are you only looking for duplicate 3's? *Do you want the number of rows between consecutive duplicates? Or the max difference between any two 3's.
Ken



On Tuesday, July 24, 2012 7:56:09 AM UTC-4, JAgger1 wrote:
I found this formula and it works well,

=ABS(MATCH(B1,A1:A8,0)-MATCH(B2,A1:A8))-1

By entering the value 3 in B1 and B2 I get my answer of 4.

Now if I have a larger range of values, with duplicates,

In column A1:A10 I have the following numbers.

1
3
6
9
6
7
3
2
4
3

anyone know how to modify this formula so I can get a correct answer
of 4 (A2 - A7) and 2 (A7 - A10)

I would like to have a formula that would work with a set of 100
(A1:A100) numbers with multiple duplicates, than- Hide quoted text -

- Show quoted text -



Hi Ken

I'd like to get the max difference in rows between any two consecutive
duplicates that I would select, in this case 3's

[[email protected]]

How to you select the threes? what would be different if you wanted 4's?


On Tuesday, July 24, 2012 11:56:52 AM UTC-4, JAgger1 wrote:
On Jul 24, 11:39*am, wrote:
> That seems like two right answers, 2 and 4. *Is the "right" answer an array of numbers? *Are you only looking for duplicate 3's? *Do you want the number of rows between consecutive duplicates? Or the max difference between any two 3's.
> Ken
>
>
>
> On Tuesday, July 24, 2012 7:56:09 AM UTC-4, JAgger1 wrote:
> > I found this formula and it works well,
>
> > =ABS(MATCH(B1,A1:A8,0)-MATCH(B2,A1:A8))-1
>
> > By entering the value 3 in B1 and B2 I get my answer of 4.
>
> > Now if I have a larger range of values, with duplicates,
>
> > In column A1:A10 I have the following numbers.
>
> > 1
> > 3
> > 6
> > 9
> > 6
> > 7
> > 3
> > 2
> > 4
> > 3
>
> > anyone know how to modify this formula so I can get a correct answer
> > of 4 (A2 - A7) and 2 (A7 - A10)
>
> > I would like to have a formula that would work with a set of 100
> > (A1:A100) numbers with multiple duplicates, than- Hide quoted text -
>
> - Show quoted text -



Hi Ken

I'd like to get the max difference in rows between any two consecutive
duplicates that I would select, in this case 3's

[JAgger1]

The way Claus has it written in his formula, B1 & B2

[Ron Rosenfeld[_2_]]

On Mon, 23 Jul 2012 10:09:14 -0700 (PDT), JAgger1 wrote:

Is there a simple formula for getting cell count beween a certain
value? Example:

In column A1:A8 I have the following numbers.

1
3
6
9
6
7
3
2

I would like a formula that can give me the cell count between
A2(number3) and A7(number 3), which would be 4.

How about a User Defined Function?

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=MaxBetweenNum(rng,Nbr)

where Rng is the cell range to search, and Nbr is the number you are searching for.
As per your example, the result is the maximum row count "between" the duplicate Nbr's (excluding from the count both the rows where Nbr actually exists).

Also, if Nbr is not a duplicate (or is non-existent), the function will return a #NUM! error.

===============================================
Option Explicit
Function MaxBetweenNum(rg As Range, Nbr As Long) As Variant
Dim c As Range
Dim sFirstAddress As String
Dim bFirstRun As Boolean
Dim i As Long, j As Long
With rg
Set c = .Find(what:=Nbr, after:=rg(.Rows.Count), _
LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext)
If Not c Is Nothing Then
i = 0
j = c.Row
sFirstAddress = c.Address

Do 'FindNext doesn't seem to work in a Function
Set c = .Find(what:=Nbr, after:=c)
If c.Address < sFirstAddress Then
i = WorksheetFunction.Max(i, c.Row - j)
j = c.Row
End If
Loop Until c.Address = sFirstAddress
End If
End With

MaxBetweenNum = i - 1
If i = 0 Then MaxBetweenNum = CVErr(xlErrNum)

End Function
========================================

[JAgger1]

Hi Ron

I created the UDF as you suggested, but I keep getting #NAME? error
when I try to use it?

[JAgger1]

Cancel that, I got it, I had included the line "Option Explicit" and
once I removed it the UDF worked perfect. Thanks for you help.
"

[Ron Rosenfeld[_2_]]

On Wed, 25 Jul 2012 09:15:34 -0700 (PDT), JAgger1 wrote:

Cancel that, I got it, I had included the line "Option Explicit" and
once I removed it the UDF worked perfect. Thanks for you help.
"

That line SHOULD be there. Maybe you had it there twice?