Vlookup multiple Values then Concatenate all values in One cell

Hi,

Can one of the Excel gurus please help me with two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)

I need to create two formulas:

1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.

2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.

I would really appreciate your help. Thank you in advance!

=vlookup(h4,e:f,2,false)

Hi mohamedJamsheer,

Thanks for the reply! However, your formula is not what I am looking for.

I need to create a formula for the output in Table 2 > "Linked Product" Column. The formula would process the data in Table 1 and return comma delimited values for column F in table 2. Table 3 then depicts the data processed from Table 2. You provided me with the vlookup formula for the results in Table 3, but I need the formula for Table 2 - column F.

Can you help me with it?

Thanks so much!

you can use below macro program to resolve

Sub lookup()
Dim A, b, c As Integer
A = 4
b = 4
c = 1
Range("F4:F999").Select
Selection.ClearContents
Do While Cells(b, 5).Value <> ""
Do While Cells(A, 1).Value <> ""
If Cells(b, 5).Value = Cells(A, 1).Value Then
If c = 1 Then
Cells(b, 6).Value = Cells(A, 2).Value
c = c + 1
GoTo line1:
End If
Cells(b, 6).Value = Cells(b, 6).Value & "," & Cells(A, 2).Value
A = A + 1
Else
line1:
A = A + 1
End If
Loop
b = b + 1
A = 4
c = 1
Loop
End Sub


thanks

Hi mohamedJamsheer,

Thank you so much for your help!

Got the solution...

I have used array formula..
So use ctrl + shift + enter to enter the formula..
Don't use only enter..


Thanks to Tigar Avatar for providing that amazing UDF...

Don't forget to click *

Hi Vikas_Gautam,

I really appreciate you taken time to help me with the solution!

Thank you!

thsnks FD for your valuable information.....