Results 1 to 10 of 10

20041125, 19:39 #1
 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

20041125, 20:06 #2
 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 stepbystep example of what you want the result to be?

20041126, 18:53 #3
 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

20041126, 19:28 #4
 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.

20041126, 22:13 #5
 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

20041126, 22:42 #6
 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.

20041208, 16:20 #7
 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

20041208, 19:33 #8
 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

20041209, 14:09 #9
 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 23 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 nonblanks 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?

20041209, 14:35 #10
 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