Lookup numbers and concatenate them in a different cell

Hi,

Can someone 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)

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

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

Table 1 Product IDs.xlsx
DATA SET
Master Product ID Linked Products
11 1
11 2
11 3
11 4
4 11
4 3
4 2
4 1
3 1
3 2
3 4
3 11
2 1
2 3
2 4
2 11
1 2
1 11
1 4
1 3
232 43
232 85
232 16
16 232
16 85
16 43
43 85
43 16
43 232
85 16
85 232
85 85
123 89
89 123


I need to


WOULD RETURN comma delimited Linked Product IDs for each corresponding product like depicted in Linked Products column in Table 2 below.

Table 2
Master Product ID Linked Products
11 1,2,3,4
4 1,2,3,11
3 1,2,4,11
2 1,3,4,11
1 2,3,4,11,
232 16,43,85
16 43,85,232
43 16,85,232
85 16,43,232
123 89
89 123

It's not simple and the concatenations will require some customization, but this gets the job done. You just need to write one concatenation equation for each number of children and copy down.

Hi hoyasaxa215,

You created two step solution:step #1 - put the data in columns by using the formula below; step #2 - concatenate the rows in step #1.

I appreciate very much you help, but I have 25 thousand rows in the data spreadsheet and it would be problematic to complete step #1 - prepare the data before concatenating it. In other words I'll not be able to put all numbers in the rows because there are 25 thousand of them and can be up to 100,000.

Is it possible to get one step formula?

Thank you!!!

Hi AlKey,
Thank you for the direction! Would you be able to help me with the VBA code which I could enter as user customized formula in the VBA module and provide me with the formula that I can then enter in Excel?

I would be very grateful if you can help!!!

Got it. Thank you for the advise!

Hi WinteE,

Unfortunately, it is not possible to sort it because the relationships between the parent master ID and it's linked products will be lost. Do you have something in mind?

Thanks so much!

Hi WinteE,

It is a very elegant solution! Good job! Unfortunately, I get #NAME? error when paste your formula: ARLOOKUP($B$1:$C$25000,B2,C$1)? How can I fix it?

Thank you! Thank you!

Hi WinteE,

It is a very elegant solution! Good job! Unfortunately, I get #NAME? error when paste your formula: ARLOOKUP($B$1:$C$25000,B2,C$1)? How can I fix it? What kind of formula ARLOOKUP is?

Thank you! Thank you!

Neyme, I just programmed "=ARLOOKUP" and it's awesome. Will do exactly what you're looking for (well, may need to trim out the spaces). Definitely worth setting up.

I can do copy & paste the code into the module, but not really familiar with the VBA. Thank you for all your help. You saved me and my team a lot of time.