Results 1 to 10 of 10
Thread: reverse FINDSERIES (v2003 sp2)

20080428, 20:52 #1
 Join Date
 Aug 2001
 Location
 Boston, Massachusetts, USA
 Posts
 167
 Thanks
 1
 Thanked 0 Times in 0 Posts
reverse FINDSERIES (v2003 sp2)
I have a situation where I need to find/lookup values buried in a field. I was thinking that a reverse of FINDSERIES would work great here, but I'm not sure how to pull it off. In the attachment I've provided a example and a detailed description of what i want to do. Take a look and let me know if you can offer any insight.
thanks
christine

20080428, 21:01 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: reverse FINDSERIES (v2003 sp2)
In C3 enter the array formula (confirm with ctrlshiftenter):
=INDEX(Sheet2!A:A,MIN(IF(ISNUMBER(FIND(A3,Sheet2!$ B$2:$B$6)),ROW(Sheet2!$B$2:$B$6))))
Copy C3 to c4:C23. Expand the range B2:B6 as needed...
Steve

20080428, 21:59 #3
 Join Date
 Aug 2001
 Location
 Boston, Massachusetts, USA
 Posts
 167
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: reverse FINDSERIES (v2003 sp2)
I was able to recreate the formula in my test environment using book1, but I'm still struggling with the actual application of the formula in my full production file. For some reason, it returns the header (REM#) instead of the actual value. But beyond that, I'm not sure how to accomodate this to return every iteration of where the value appears. The formula that you provided will only return the first iteration of where the event appears. What to do when it's there more than once and refers to 2 or more different tickets? thanks.
thanks
christine

20080428, 23:49 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: reverse FINDSERIES (v2003 sp2)
I don't understand. Could you provide a sample workbook that illustrates better what you want, and try to explain it more clearly?

20080428, 23:54 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: reverse FINDSERIES (v2003 sp2)
BTW, what is FINDSERIES? It's not a native Excel function as far as I know.

20080429, 00:45 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: reverse FINDSERIES (v2003 sp2)
You can add this function to a module:
<pre>Option Explicit
Function RevFindSeries(vValue, rngAll As Range, _
iCol As Integer, Optional sSep As String = ", ")
Dim rCell As Range
Dim rng As Range
On Error GoTo ErrHandler
Set rng = Intersect(rngAll, rngAll.Columns(1))
For Each rCell In rng
If InStr(rCell.Value, vValue) <> 0 Then _
RevFindSeries = RevFindSeries & sSep & _
rCell.Offset(0, iCol).Value
Next rCell
If RevFindSeries = "" Then
RevFindSeries = CVErr(xlErrNA)
Else
RevFindSeries = Right(RevFindSeries, Len(RevFindSeries)  Len(sSep))
End If
ErrHandler:
If Err.Number <> 0 Then RevFindSeries = CVErr(xlErrValue)
End Function</pre>
And then use a line like:
=revfindseries(A3,Sheet2!$B$2:$B$6,1)
It is a minor modification of my VlookupAll function from <post#=395,235>post 395,235</post#>. You can look at what the options are there as well as looking on how you could modify others. I modified it to use a "contains" rather than an exact match...
Steve

20080430, 19:28 #7
 Join Date
 Aug 2001
 Location
 Boston, Massachusetts, USA
 Posts
 167
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: reverse FINDSERIES (v2003 sp2)
Sorry for the delay in the response. I just now getting back into this to take a look at the original resolution using the array. When I use the array in the sample file, the formula works correctly. I've tried to incorporate this back to my original full file and found a strange scenario. The formula only returns the header [REM#], the first cell of the column. Does this make any sense to you?
{=INDEX(Sheet2!A:A,MIN(IF(ISNUMBER(FIND(A3,Sheet2! $B$2:$B$6)),ROW(Sheet2!$B$2:$B$6))))}
{=INDEX(REMEDY!B:B,MIN(IF(ISNUMBER(FIND(D6,REMEDY! $C$2:$C$4000)),ROW(REMEDY!$C$2:$C$4000))))}thanks
christine

20080430, 21:19 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: reverse FINDSERIES (v2003 sp2)
The formula will result in the header row if the value being searched for is not found. (Note that FIND is case sensitive, if you need it insensitive try SEARCH instead of FIND)
Steve

20080430, 21:31 #9
 Join Date
 Aug 2001
 Location
 Boston, Massachusetts, USA
 Posts
 167
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: reverse FINDSERIES (v2003 sp2)
Okay, I can see that, but the problem that I have in fully understanding this is that this shouldn't be a case issue because the fields that we are looking at contain numbers, not text. Also, I expect to see results because the number that I am looking for is there. I'm attaching a sample where I've incorporated the array and came back with the header. Do you think it might be having a problem because the field is too large?
thanks
christine

20080430, 23:34 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: reverse FINDSERIES (v2003 sp2)
In O2 try this:
=INDEX(REMEDY!B:B,MIN(IF(ISNUMBER(SEARCH(D2,REMEDY !$C$2:$C$4000)),ROW(REMEDY!$C$2:$C$4000))))
The lookup in column D should match the row you are looking up in (you had D3 in cell O2)
Also manual calculation is entered so you must hit <f9> to get the result to show after copying the formula.
Steve