Superformula required for looking up Duplicates in a range

  • Thread starter Thread starter hjopertham
  • Start date Start date
H

hjopertham

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
 
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?
 
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
 
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.
 
Tom said:
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.
Click to expand...

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.
Click to expand...

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?
Click to expand...

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.
 
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.
 
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.
 
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.
Click to expand...

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
 
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.
Click to expand...

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.
 
Back
Top