Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Find in custom functions (2000sp3)

    Attached is a sample segment of my file. I'm trying to generate a function that will take the "number", and if it ends in a (the easy bit), search "casenum" for that same number, and return the "number" in the row with the same "casenum" and the letter F in "FamRel". Using lookups is tough here, because one is looking at values in two columns. The obvious solution was to find cells containing the appropriate casenum and inspecting the contents of FamRel on that row. Problem is one can't use the find method in functions (and I tried cheating by including most of the code in a sub which I called from a function, but this also failed).

    Any suggestions as to the best way to do this. It can't be a difficult problem.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in custom functions (2000sp3)

    Could you give a step-by-step example of what you want the result to be?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Find in custom functions (2000sp3)

    OK, so the function would fill column P. The function would return a null equivalent for rows 2 to 12 of the sample spreadsheet, because the "number" in column E doesn't end in A. In row 13, the "number" in column E is HE/DX92A, and, by my definition, column C (the casenumber) will also contain HE/DX92A. I need the function to find the row containing HE/DX92A in column C and the letter F in column O (FamRel), and return the value of column E (number) in that row. If there is no row with HE/DX92A in column E and the letter F in column O the function can return a null equivalent or an error, I don't mind which. In this case the function would return HE/DX92B1, the contents of column E in row 99 (the row containing HE/DX92A in column C and the letter F in column O).

    Is that clear?

    Thanks

    Sean

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in custom functions (2000sp3)

    I have attached a solution using formulas. To solve the problem of looking up in two columns, I inserted an intermediary column in column E; it concatenates columns P (originally O) and C. You can hide the new column E if you like. The formulas in column Q check if Number ends in "A", and if so, uses a VLOOKUP to find the desired value. The formula in Q2 is
    <pre>=IF(UPPER(RIGHT(F2,1))="A",VLOOKUP("F"&UPPER( C2),$E$2:$F$104,2,FALSE),"")
    </pre>

    Note: comparisons in Excel are case sensitive, and capitalization of the data varies, so I used the UPPER function to translate everything to upper case in the formulas.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Find in custom functions (2000sp3)

    Thanks Hans. I'd been thinking about those sorts of solutions, but was hoping there was a more generalisable solution to similar complex lookup problems. Your suggestion will work very well for everything I need to do with this spreadsheet, though.

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in custom functions (2000sp3)

    If you are willing to use array formulas, you can do without the intermediate column. For row 2, the formula is
    <code>
    =IF(RIGHT(E2,1)="A",INDEX($E$2:$E$104,MATCH("F"&C2 ,$O$2:$O$104&$C$2:$C$104,0)),"")
    </code>
    Since it is an array formula, you must confirm it with Ctrl+Shift+Enter instead of Enter. Fill down as far as needed.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Find in custom functions (2000sp3)

    Thanks Hans,

    Array formulas do my head in, which is all the more reason to use them. A good example, thanks.

    Now for the trickiest problem, although I suspect that this might not be able to be done as a function. Combinations of the casenumber and "M" or "F" are unique. Combinations of casenumber with "B" or "S" are not, and there could be up to four of them for each case num. I want to populate the 4 columns after the M and F columns with the numbers of each of the four (for any case num there could be between 0 and 4) B's or S's for that casenum. The order doesn't matter in anyway, except that positive finds shouldn't follow nulls across the columns.

    Example result attached

  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: Find in custom functions (2000sp3)

    I think this is too complicated for "standard formulas" (including arrays). I created a custom function
    Add this code to module in the workbook:
    <pre>Option Explicit
    Function LookupFamily(vValue, rngAll As Range, iCol As Integer, iCol2 As Integer, lIndex As Long)
    Dim x As Long
    Dim iSibs As Integer
    Dim lCount As Long
    Dim vArray() As Variant
    Dim sFamily(1 To 6) As String
    Dim rng As Range
    On Error GoTo errhandler
    If UCase(Right(vValue, 1)) = "A" Then
    Set rng = Intersect(rngAll, rngAll.Columns(1))
    ReDim vArray(1 To 2, 1 To rng.Rows.Count)
    lCount = 0
    For x = 1 To rng.Rows.Count
    If UCase(rng.Cells(x).Value) = UCase(vValue) Then
    lCount = lCount + 1
    vArray(1, lCount) = UCase(rng.Cells(x).Offset(0, iCol).Value)
    vArray(2, lCount) = UCase(rng.Cells(x).Offset(0, iCol2).Value)
    End If
    Next x

    ReDim Preserve vArray(1 To 2, 1 To lCount)

    If lCount = 0 Then
    LookupFamily = "" 'CVErr(xlErrNA)
    ElseIf lIndex > lCount Then
    LookupFamily = "" 'CVErr(xlErrNum)
    Else
    For x = 1 To 6
    sFamily(x) = ""
    Next
    iSibs = 0
    For x = 1 To lCount
    Select Case vArray(2, x)
    Case "F"
    sFamily(1) = vArray(1, x)
    Case "M"
    sFamily(2) = vArray(1, x)
    Case "B", "S"
    iSibs = iSibs + 1
    sFamily(2 + iSibs) = vArray(1, x)
    End Select
    Next
    LookupFamily = sFamily(lIndex)
    End If
    Else
    LookupFamily = ""
    End If
    errhandler:
    If Err.Number <> 0 Then LookupFamily = CVErr(xlErrValue)
    End Function</pre>


    It is a modified/customized version of the VLIndex routine I developed in <post#=395235>post 395235</post#> to have multiple lookups.

    Add this formula into P2
    =lookupfamily($E2,$C$2:$C$106,2,12,COLUMN()-15)
    Copy P2 to Q2 to U2
    Autofill P2:U2 down the rest of the rows.

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Find in custom functions (2000sp3)

    Thanks for that Steve. I'd been afraid to even try something like this, as I thought it would be way too slow, but I keep forgetting I'm not on my PII450 128MB old warhorse anymore, and, limiting the range to that actually filled rather than choosing $C;$C, which is my (bad) habit, this only takes 2-3 minutes, which is totally acceptable as long as calculation is set to manual.

    I've had to add an error trapping line as I was having trouble if one of the FamRel cells was #N/A (it's a lookup function itself): the function was returning an error for all cells in that row (isn't it irritating that you can't trap #N/A separately from other errors in VB).

    I've also had to bin the lIndex>lCount line as it messes up those cases where there was no father or mother but one or more siblings (or one parent and two or more siblings). I don't think it's essential otherwise as the way you fill the results array should mean there are no blanks before non-blanks in the sib fields.

    Now it's working splendidly. Thank you very much.

    Just for my education, why did you use Intersect to generate the search range?

  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: Find in custom functions (2000sp3)

    Just in case someone had entered a range in the parameter that was wider than one column: it just takes the first column.

    Some ideas (untested) to speed it up:
    1)If you are always getting all 6 values, Instead of having a row with 6 calcs you could modify the function to give the array as the output and only put in an array formula so only 1 calc is in the 6 cells. This should reduce the number of calcs (and the speed) by a factor of 6

    2) You could also create an "enormous" array which calculated all of them in 1 formula that fed a range of values to go thru and do them individually. I don't think it would speed it up more than doing #1

    3) This does a lot of checking. If you are going to set calc to manual and "force" the calc at various times, it might be faster to create a macro routine
    that (at the press of a button) just fills in all the entries. It would also use less memory since it would not have formulas in the cells but values. Also since you know you are going to lookup many things, you could enhance the coding to "search"/find to the matches more effectively and reduce the number of times that the list is gone thru. I think that extracting a sublist of the items that would be looked up and then going thru the list and checking them once thru this sublist would be faster than the current procedure.

    Just some thoughts,
    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
  •