Concat text if first item is Parent item - Issue

[desibouy]

Hi,


I'm trying to automate Part# on a inventory spreadsheet but struggling. Below is my spreadsheet and the layout.
The Part # for the Parent Item should be the concatenate of the child items comma separated. The Part # for the child should be the same as the Item Number like below.

I've used formula

=IF(B2="Child Matrix Item",A2,CONCATENATE(D2,",",D3,",",D4,",",D4))

for the Parent Part # to but then If item only has 2 child items then it obviously it won't work and having to constantly amending the formula or having to do it manually.

FYI the first Parent Matrix Item, should show 123, 456, 789 -- notice the last code doesn't have a a comma at the end.
See Last table.


please can someone help?

Item Number	Type	Sub item of	Part #
Test 1	Parent Matrix Item
123	Child Matrix Item	Test 1	123
456	Child Matrix Item	Test 1	456
789	Child Matrix Item	Test 1	789
Test 2	Parent Matrix Item
987	Child Matrix Item	Test 2	987
654	Child Matrix Item	Test 2	654
Item Number	Type	Sub item of	Part #
Test 1	Parent Matrix Item		123, 456, 789
123	Child Matrix Item	Test 1	123
456	Child Matrix Item	Test 1	456
789	Child Matrix Item	Test 1	789
Test 2	Parent Matrix Item		987, 654
987	Child Matrix Item	Test 2	987
654	Child Matrix Item	Test 2	654

<tbody>
</tbody>

 

[desibouy]

Bump*

Please anyone?

 

[gaz_chops]

Rather than using Parent Child can you show the actual data?

 

[gaz_chops]

Bump*

Answer......

 

[desibouy]

Sorry Gaz, It didn't notify me of your answer so couldn't respond.

Here is the actual Data.

Item Name/Number	Matrix Type	Sub Item Of	Part #
Diabetes Care Pocket® Blood Glucose Meter	Parent Matrix Item		OP050300K,OP050301P,OP050302B,OP050303G,OP070802B
OP050302B	Child Matrix Item	Diabetes Care Pocket® Blood Glucose Meter	OP050302B
OP050300K	Child Matrix Item	Diabetes Care Pocket® Blood Glucose Meter	OP050300K
OP050301P	Child Matrix Item	Diabetes Care Pocket® Blood Glucose Meter	OP050301P
OP050303G	Child Matrix Item	Diabetes Care Pocket® Blood Glucose Meter	OP050303G
OP070802B	Child Matrix Item	Diabetes Care Pocket® Blood Glucose Meter	OP070802B

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[gaz_chops]

So, if Sub Item Of = Item Name then concatenate?

 

[desibouy]

So, if Sub Item Of = Item Name then concatenate?

Only the Parent to concat, the sub items (child items) should only be the normal item codes.

I have another issue too, but I will mention it later,

Thanks