How to create a cell of IDs of related products (in the row of an original product)?

Hi All,

I would like to create a product feed for my company, where each row represents a product. Sometimes a product has some additional feature, which acts as a separate new product (in a new row).

Example:

Main product -> ID:1
Main product with feature #1 -> ID: 1.1
Main product with feature #2 -> ID: 1.2
Main product with feature #3 -> ID: 1.3

After all this, in a certain product row I would like to display the other 3 products as "related items" in a separate column. So If the visitor is on the product page of the "Main product" he/she should see Main product with feature #1, Main product with feature #2 and Main product with feature #3 as related products.
Therefore I would like to display always the other productsī product IDs in a single cell for a certain product.

Example:
In the row of my Main product (ID: 1) I would like to see a column (related products) with "1.1, 1.2, 1.3".

OR

In the row of my Main product with feature #1 (ID: 1.1) I would like to see a column (related products) with "1, 1.2, 1.3".


I hope this makes sense. Thank you very much for your help in advance!

Can you upload sample file, please.
Check yellow banner at the top.

Try into C3:

Formula:

=IF(A3<>"",SUBSTITUTE(TEXTJOIN("",1,IF(IFERROR(FIND(LEFT(A3,7),$A$3:$A$18),0)<>0,$A$3:$A$18&", ","")),A3&", ",""),"")

accept with Ctrl+Shift+Enter (as it is array formula).
Then drag it down as long as needed.

	A	B	C
1	ID	name	related_products
2		PALACE
3	801_005	Climbing Frame for Children - Jungle Palace	801_005 - 850_176, 801_005 - 850_177, 801_005 - 850_230,
4	801_005 - 850_176	Climbing Frame with Swing - Palace 1-Swing	801_005, 801_005 - 850_177, 801_005 - 850_230,
5	801_005 - 850_177	Playhouse with Slide and Swing - Palace 2-Swing	801_005, 801_005 - 850_176, 801_005 - 850_230,
6	801_005 - 850_230	Playtower with Climbing Wall - Palace Climb	801_005, 801_005 - 850_176, 801_005 - 850_177,
7

Nevermind! Found the information about the arrayformula. Now it works fine, thank you!

In relation to attachment. Why C6:C9 should be blank?

Because I only want to display the related product IDs in column C if the original ID ends on one of these 4:

- regular 7 digit IDs (for example: 801_005)
- 850_176 (for example: 801_005 - 850_176)
- 850_177 (for example: 801_005 - 850_177)
- 850_230 (for example: 801_005 - 850_230)

You can see that my examples are the same products because all of them start with 801_005 only they are different variations. And ultimately I would only want to display the related products in column C of items that end on those above mentioned 3 numbers + the original regular ID (801_005)

EDIT: Maybe itīs a solution to exclude in the function somehow products that have IDs ending on 240, 247, 261 and 74?

See if the following array entered formula, which is just a modification of KOKOSEK's formula, does what you want:

Formula:

=IF(OR(LEN(A2)=7,RIGHT(A2,3)="176",RIGHT(A2,3)="177",RIGHT(A2,3)="230"),SUBSTITUTE(TEXTJOIN("",1,IF(IFERROR(FIND(LEFT(A2,7),$A$2:$A$236)*((RIGHT(A$2:A$236,3)="176")+(RIGHT(A$2:A$236,3)="177")+(RIGHT(A$2:A$236,3)="230")+(LEN(A$2:A$236)=7)),0)<>0,$A$2:$A$236&", ","")),A2&", ",""),"")

Let us know if you have any questions.