UDF for information on multiple rows from 1 input

**omegacaesar** · 05-02-2012, 10:42 AM

I have a table with an ever expanding number of rows but a static number of columns. Within the table (called “FamilyTable” in the example worksheet) there is an “Effective Name” in each row. Next to the Effective Name are a variable number of “Members” of each name. The number of members can range from 1 to 18. The members will mostly be strings but some may be numbers. Each Member cell will only contain 1 member.

I need a user-defined function that accepts as input a string or number and then outputs the Effective Name of ALL the rows that the input string/number appears in. Previously, I was able to create a formula that could find the effective name for the last row that a member appeared in* (although I’m not sure why it returned the last name instead of the first name). But, now that members can appear in multiple rows, my formula is no longer adequate.

Ideally, if a member appeared in the same row multiple times then the UDF would be able to only show that row’s effective name once. If that greatly complicates the UDF, however, I can probably find a way to limit the users’ abilities to repeat a member in a given row.

The “Required Output” sheet shows examples of various inputs and outputs. I’ve also included my formula which is now inadequate.

Any help that you can provide would be greatly appreciated. Please tell me if anything requires additional explanation.

Thank you.

*I modified a formula that I found on the internet but cannot seem to find again.

**tigeravatar** · 05-02-2012, 10:59 AM

omegacaesar,

Welcome to the forum!
Attached is a modified version of your example workbook. It contains a UDF with the following code:

Public Function GetNames(ByVal strLookupValue As String, _
                         ByVal rngNames As Range, _
                         ByVal rngMembers As Range) As String
    
    Dim rIndex As Long
    Dim rngFound As Range
    
    For rIndex = rngMembers.Row To rngMembers.Row + rngMembers.Rows.Count - 1
        Set rngFound = rngMembers.Resize(1).Offset(rIndex - rngMembers.Row).Find(strLookupValue, , , xlWhole)
        If Not rngFound Is Nothing Then
            GetNames = GetNames & "; " & rngNames.Resize(1, 1).Offset(rIndex - rngMembers.Row).Value
            Set rngFound = Nothing
        End If
    Next rIndex
    GetNames = Mid(GetNames, 3)
    
End Function

In the 'Required output' sheet, cell B2 and copied down is this formula:
=getnames(A2,FamilyTable[Effective Name],FamilyTable[[Member 1]:[Member 18]])

Basically, you use it in this format:
=getnames(LookupValue,NameRange,MemberRange)

**omegacaesar** · 05-02-2012, 11:43 AM

tigeravatar- thank you very much for your help with this.

If I understand your code correctly, the macro goes row-by-row and compares each member cell to the lookupvalue string. If the cell matches then the relevant effective name is added to the string that will eventually be output.

You solved the problem of the same value appearing multiple times in one row by simply having the macro stop the row comparison as soon as it found a match and then start again at the next row.

Assuming that all that is true, the only thing I don’t understand is why you have to have the mid before the output. I see that without the mid the deliminator (i.e. “; “) is the first thing that is displayed, but I don’t know how “; “ came to be before everything else.

Finally, how could the UDF be modified so that if the lookupvalue is blank then the UDF just outputs “N/A”? Currently, if the lookupvalue is blank, it returns the names of all the rows which have a blank- which is every row. This would be useful in case the user deletes the lookupvalue (which they shouldn’t but I could envision it happening).

Again, thank you for the help with this.

**tigeravatar** · 05-02-2012, 11:51 AM

omegacaesar,

You are correct on everything you've stated. As for how the semicolon got displayed first, that is in this line:

GetNames = GetNames & "; " & rngNames.Resize(1, 1).Offset(rIndex - rngMembers.Row).Value

Because GetNames is blank to begin with, the first time it is provided a value will look like this:
GetNames = GetNames & "; " & rngNames.Resize(1, 1).Offset(rIndex - rngMembers.Row).Value ->
GetNames = "" & "; " & "Name1" ->
GetNames = "; Name1"

So it will always start with "; " and the Mid at the end removes that.

As for how to make it return a "N/A" when a blank lookup value is input, that doesn't have to be part of the UDF, it would be better to make it part of the formula:
=IF(A2="","N/A",getnames(A2,FamilyTable[Effective Name],FamilyTable[[Member 1]:[Member 18]]))