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.

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

Please Login or Register  to view this content.

jindon, thank you very much. That worked perfectly.