+ Reply to Thread
Results 1 to 4 of 4

UDF to return string of values of all rows with cell matching reference cell

  1. #1
    Registered User
    Join Date
    04-20-2012
    Location
    MI
    MS-Off Ver
    Excel 2007
    Posts
    2

    UDF to return string of values of all rows with cell matching reference cell

    Hello all,

    I need assistance in creating an user-defined function. The inputs to the function will be a reference cell, a comparison column, and a data column. The function will then compare the reference cell to all of the cells in the comparison column- for each row in the comparison column that matches the reference cell, that row’s cell in the data column should be concatenated onto a string. That should be repeated until the string has concatenated all of the values (text or number) in the relevant data column cells. The function then returns that string.

    I have included a simplified example of what I’m trying to do. The actual table I’m working with is much larger and utilizes names rather than static references. I just tried to simplify everything to show all possible issues.

    Users will input an unique Part #. The Part # is the “data column”. Users will then select the part family type- they will select either “None”, “Head”, “Member”, or “Name”. Unfortunately, I’m sure that some users will skip identifying the Family Type and thus a blank cell should be the equivalent of selecting “None”. Finally, the users will input the Family ID. The Family ID is it the “comparison column”. The function will then be used in the Family Members cell to return all of the part #s that have the same family ID. The family ID in each row is that row’s “reference cell”.

    Therefore, the macro only needs to use the Part # column, the Family ID column, and the Family ID cell for each row.

    Effectively, this will be like performing N number of vlookups on each row’s Family ID cell and concatenating all of those vlookups into 1 string.

    Unfortunately, I have no idea how to approach this problem- thus any assistance that can be rendered would be extremely helpful.

    Currently, I auto-generating a table which:
    1. Lists all of the unique Family IDs
    2. Looks up all of the Part #s with that family ID
    3. Concatenates all of the Part #s into 1 string
    4. The original table performs a vlookup on the Family ID to get the generated string of Part #s

    The problem is that this solution is extremely processor intense and isn’t guaranteed to scale properly (if there’s not enough columns grabbing Part #s for each Family ID or if there aren’t enough rows generating the final strings, then the solution won’t work).

    If any other information is needed in order to help me, I can try to provide that.

    Thank you very much for any help you can give. I know that this isn’t the easiest problem to understand so please tell me if anything is confusing or contradictory.
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,167

    Re: UDF to return string of values of all rows with cell matching reference cell

    This should get you started

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,743

    Re: UDF to return string of values of all rows with cell matching reference cell

    =VLookUps(C2,$C$2:$C$18,$A$2:$A$18)
    then filldown.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-20-2012
    Location
    MI
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: UDF to return string of values of all rows with cell matching reference cell

    jindon, thank you very much. That worked perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1