reverse FINDSERIES (v2003 sp2)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

I don’t understand. Could you provide a sample workbook that illustrates better what you want, and try to explain it more clearly?

Reply | Quote

BTW, what is FINDSERIES? It’s not a native Excel function as far as I know.

Reply | Quote

You can add this function to a module:

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

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

Reply | Quote

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))))}

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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 to get the result to show after copying the formula.
Steve

Reply | Quote