Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    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.
    Attached Files Attached Files
    thanks
    christine

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift-enter):
    =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

  3. #3
    2 Star Lounger
    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

  4. #4
    Plutonium Lounger
    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?

  5. #5
    Plutonium Lounger
    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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    2 Star Lounger
    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    2 Star Lounger
    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?
    Attached Files Attached Files
    thanks
    christine

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •