Counting a set of contiguous similar values

[AkaranaJack]

How can I locate the boundary where a run of identical numbers in a
spreadsheet column ends, or to put it another way, how do I count a set of
contiguous similar values?

Here is a series, with the values starting in cell A1, and the desired
results to be autofilled down column B:
1 3
1 2
1 1
2 2
2 1
3 1
1 2
1 1

The values in column A are regular, rising by the same increment, then
reverting to 1 and starting out again.

MATCH(A1+1,INDEX(A1:A100,,1),0)-1 correctly counts as far as the first,
second and third boundaries, but in the seventh row it cannot cope with the
termination of the column, returning #NA.
COUNTIF(A1:A100,A1) counts all the instances of 1 in the column, thus
reaching an undesired result (5) in the first row.

This is part of a more complex set of functions in a database in Excel 97.

AJ

 

[Frank Kabel]

Hi
one workaround
=IF(ISNA(MATCH(...)),101,MATCH(...))

 

[AkaranaJack]

Thanks. I've tried that:
IF(ISNA(MATCH(A1+1,INDEX(A1:A101,,1),0)-1),COUNTIF(A1:A101,A1),MATCH
(A1+1,INDEX(A1:A101,,1),0)-1)
But in the following series it yields the wrong result (2) in the fourth row:
1 2
1 1
2 1
3 1

1 1
2 1

What I was looking for was some way to integrate the boundary formula (a
cell followed by either the next highest number or a blank) directly into a
function.

The workaround I am using is to create an extra column that contains a flag
for every boundary like this: IF(OR(A2-A1=1,A2-A1<0),"flag",""). I can then
use MATCH to find the "flag". But it would be so much betterif I could run
down the array using an integrated MATCH function. Past posts suggest you
cannot feed OR values to a MATCH, but perhaps someone has other ideas?

AJ

 

[Aladin Akyurek]

Let A1:A8 house the sample you provided. Two options...

[1]

In A9 enter: 9.99999999999999E+307

In B1 enter & copy down:

=MATCH(TRUE,INDEX(A1:$A$9<>A1,0,1),0)-1

[2] If filling in the cell after the last used cell with
9.99999999999999E+307 is too inconvenient, add the following code to your
workbook:

Option Explicit

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: http://makeashorterlink.com/?P20022174
' Mod: Nov 3, 2003, to reduce number of ReDim Preserve calls.
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
ReDim TempUnion(1 To UBound(Arg) - LBound(Arg) + 1) As Variant
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
If Ctr > UBound(TempUnion) Then
ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As
Variant
End If
'ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
If Ctr > UBound(TempUnion) Then
ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As
Variant
End If
'ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
If Ctr < UBound(TempUnion) Then
ReDim Preserve TempUnion(1 To Ctr) As Variant
End If
ArrayUnion = TempUnion
End Function

Now, in B1 type:

=MATCH(TRUE,arrayunion(A1:$A$8,{9.99999999999999E+307})<>A1,0)-1

which you confirm with control+shift+enter (instead of just with enter) and
copy down.

I'd urge you to opt for [1].