Thread: Superformula required for looking up Duplicates in a range

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

here's a udf. You haven't said HOW you want the results, this gives a string...

Option Explicit

Function GetDuplicates(text As String, target As Range) As String
Dim index As Long
Dim result As String
For index = 1 To target.Count
If target(index) = text Then
result = result & "," & index
End If
Next
GetDuplicates = Mid(result, 2)
End Function

"hjopertham@yahoo.co.uk" 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
>
>

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.

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

--
Regards,
Tom Ogilvy

<hjopertham@yahoo.co.uk> wrote in message
news:1115381053.345337.181100@g14g2000cwa.googlegroups.com...
> 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
>

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

"hjopertham@yahoo.co.uk" 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
>
>

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" <PatrickMolloy@discussions.microsoft.com> wrote in message
news:1A794AB9-333B-4138-8490-1B18060133F8@microsoft.com...
> 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
>
> "hjopertham@yahoo.co.uk" 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
> >
> >

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

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


<hjopertham@yahoo.co.uk> wrote in message
news:1115386982.056469.213430@g14g2000cwa.googlegroups.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
>

Dear Tom Ogilvy,

Thank you very much for providing a VBA solution. I will test it later
this evening, and let you know tomorrow if it serves my purposes.

Regards

James.

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

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%23QA8ojjUFHA.2664@TK2MSFTNGP15.phx.gbl...
> 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" <PatrickMolloy@discussions.microsoft.com> wrote in
> message
> news:1A794AB9-333B-4138-8490-1B18060133F8@microsoft.com...
>> 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
>>
>> "hjopertham@yahoo.co.uk" 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
>> >
>> >
>
>

Tom & Patrick

I wish to express my deepest gratitude to both of you.

Tom, I will be using the 2nd VBA UDF since I only need to enter the
formula into a single cell. It's a fantastic function, and the ease of
use was beyond my expectations.

Patrick, thank you for your assistance, your 1st UDF is a tasty little
function and I will be archiving it.

All the Best.

Regards

James.

hjopertham@yahoo.co.uk wrote:
> Dear Tom Ogilvy,
>
> Thank you very much for providing a VBA solution. I will test it later
> this evening, and let you know tomorrow if it serves my purposes.
>
> Regards
>
> James.
>

As another alternative if the functions in the freely downloadable file
at http:/home.pacbell.net/beban were available to your workbook, you
could enter the following formula into a cell in Column A and fill
across as far as necessary; it assumes your lookup value is in Cell A1:

=INDEX(ArrayUniques(IF(Tbl3=$A1,ROW(Tbl3),""),,"1horiz"),1,COLUMN(A1))-IF(COLUMN()=1,0,INDEX(ArrayUniques(IF(Tbl3=$A1,ROW(Tbl3),""),,"1horiz"),1,COLUMN(A1)-1))

Alan Beban

<hjopertham@yahoo.co.uk> wrote...
....
>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.
....
>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 you really want a single long, complicated, obnoxious formula, you could
use the array formula

=IF(ROW(Data)-CELL("Row",Data)<COUNTIF(Data,"2_34a"),
SMALL(IF(Data="2_34a",ROW(Data),""),
ROW(INDIRECT("1:"&COUNTIF(Data,"2_34a"))))
-IF(ROW(INDIRECT("1:"&COUNTIF(Data,"2_34a")))>1,
SMALL(IF(Data="2_34a",ROW(Data),""),
ROW(INDIRECT("1:"&COUNTIF(Data,"2_34a")))-1),
CELL("Row",Data)-1),"")

If your results started in cell F3 and followed in subsequent rows, it'd be
more elegant and efficient to use

F3:
=MATCH("2_34a",Data,0)

F4:
=IF(ROW()-ROW($F$3)<COUNTIF(Data,"2_34a"),
MATCH("2_34a",OFFSET(Data,SUM($F$3:$F3),0),0),"")

Fill F4 down as needed. This would also be more recalc speed efficient than
VBA due to the unavoidable Excel/VBA interface.