Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions,microsoft.public.excel
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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 " 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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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 wrote in message oups.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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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 |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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 |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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. |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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 |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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. |
#11
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
|
#12
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions,microsoft.public.excel
|
|||
|
|||
Superformula required for looking up Duplicates in a range
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help required with complex Removing of Duplicates | Excel Worksheet Functions | |||
Look for duplicates within a range | Excel Worksheet Functions | |||
Fill range/series help required please | Excel Discussion (Misc queries) | |||
IF statement value range required | Excel Discussion (Misc queries) | |||
Set range based on cell contents - help required | Excel Programming |