cheers Tom...'fraid I only get a narrow time-slot these days.

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Patrick Molloy" wrote in
message
...
this method returns an array formula, and the first item is the count of
the
number of times the item appears
Function GetDuplicates(text As String, target As Range) As Variant
Dim index As Long
Dim result() As Long
Dim count As Long
For index = 1 To target.count
If target(index) = text Then
count = count + 1
ReDim Preserve result(0 To count)
result(count) = index
End If
Next
result(0) = count
GetDuplicates = result
End Function

" wrote:

Hi

I've been trying to construct a Duplicate entries/Lookup superformula
all to no avail.

Column A is a helper range (optional). Column D contains strings of
numbers and/or characters.

I wish to search only Column D for the occurrences of "2_34a". What I
would like is a formula to list how many cells back each duplicate
occurred from each other.

My worksheet setup:

A2 B2 C2 D2 E2

1 2_115
2 __dd3
3 2_34a
4 x_21_
5 _1xx_
6 54321
7 _4_1_
8 54321
9 2_34a
10 54321
11 54321
12 54321
13 2_34a
14 2_34a
15 2_115
16 54321
17 54321
18 54321
19 54321
20 54321


In this case "2_34a" last appeared 3, 6, 4, 1 cells away. So the
formula would return 3 6 4 1.

I can achieve the above using 4 clunky formulas, but is there 1
superformula that could do this. Since I wish to use the formula on
numerous worksheets/workbooks.

(If the formula can not recognize the underscores, I can replace this
with a letter)

Any help to find a solution will be most appreciated.

Regards

James