Sorry - my deepest apologies - not sure why I chose to use the word
blasted - it wasn't my intention to denigrate the fact that you cross posted
which is certainly commendable (rather than multiposting). My intent was to
say I didn't know if a VBA solution was acceptable.

Since Patrick hasn't responed, here is a modification of his code:

Function GetDuplicates(text As String, target As Range) As Variant
Dim index As Long
Dim result() As Long
Dim count As Long
ReDim result(0 To 0)
For index = 1 To target.count
If target(index).Text = text Then
count = count + 1
ReDim Preserve result(0 To count)
result(count) = index
End If
Next
For i = 0 To UBound(result) - 1
result(i) = result(i + 1) - result(i)
Next
ReDim Preserve result(0 To UBound(result) - 1)
GetDuplicates = result
End Function

You would have to select as a minimum for your example, 4 contiguous cells
in a single row and enter in the formula bar

=GetDuplicates("2_34a",A1:A20)
and exit the edit with Ctrl+Shift+enter to make it an array formula. If you
select more than 4 cells, then the ones beyond 4 will contain #N/A.

If you wanted to array enter the formula in a single column, you would
change
GetDuplicates = result
to
GetDuplicates = Application.Transpose(result)

If you wanted to return a comma separated string to a single cell you could
modify it to

Function GetDuplicates(text As String, target As Range) As Variant
Dim index As Long
Dim result() As Long
Dim count As Long
Dim sStr as String
ReDim result(0 To 0)
For index = 1 To target.count
If target(index).Text = text Then
count = count + 1
ReDim Preserve result(0 To count)
result(count) = index
End If
Next
sStr = ""
For i = 0 To UBound(result) - 1
result(i) = result(i + 1) - result(i)
sStr = sStr & result(i) & ","
Next
sStr = Left(sStr,len(sStr)-1)
ReDim Preserve result(0 To UBound(result) - 1)
' GetDuplicates = result
GetDuplicates = sStr
End Function

Then you would only need to enter the formula in a single cell.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Tom Ogilvy wrote:
Think you need to reread the specification Patrick. He doesn't want
the
index in the range, he wants the count of cells back to the previous
occurance of the value or to the start of the range.

1) Tom has interpreted my post correctly.

You could certainly build such a formula using a VBA UDF, however,
since you
blasted this to several groups, including programming, it is unclear
whether
a VBA UDF would be an acceptable solution.

2) VBA UDF would be an acceptable solution, hence the reason I
cross-posted to microsoft.public.excel.programming.
3)I don't do blasting.

You want the function to return an array of values even though this
can't be
displayed in single cell?

4) Tom that is why you are the expert, and I am the novice. The whole
point of posting, is for you to tell me if it is possible.

Regards

James