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?


[TABLE="width: 888"]
<tbody>[TR]
[TD]Item Number[/TD]
[TD]Type[/TD]
[TD]Sub item of[/TD]
[TD]Part #[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]Parent Matrix Item[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 1[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 1[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 1[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]Parent Matrix Item[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]987[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 2[/TD]
[TD]987[/TD]
[/TR]
[TR]
[TD]654[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 2[/TD]
[TD]654[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]





[/TD]
[/TR]
[TR]
[TD]Item Number[/TD]
[TD]Type[/TD]
[TD]Sub item of[/TD]
[TD]Part #[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]Parent Matrix Item[/TD]
[TD][/TD]
[TD]123, 456, 789[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 1[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 1[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 1[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]Parent Matrix Item[/TD]
[TD][/TD]
[TD]987, 654[/TD]
[/TR]
[TR]
[TD]987[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 2[/TD]
[TD]987[/TD]
[/TR]
[TR]
[TD]654[/TD]
[TD]Child Matrix Item[/TD]
[TD]Test 2[/TD]
[TD]654[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="width: 1069"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Item Name/Number[/TD]
[TD]Matrix Type[/TD]
[TD]Sub Item Of[/TD]
[TD]Part #[/TD]
[/TR]
[TR]
[TD]Diabetes Care Pocket® Blood Glucose Meter[/TD]
[TD]Parent Matrix Item[/TD]
[TD][/TD]
[TD]OP050300K,OP050301P,OP050302B,OP050303G,OP070802B[/TD]
[/TR]
[TR]
[TD]OP050302B[/TD]
[TD]Child Matrix Item[/TD]
[TD]Diabetes Care Pocket® Blood Glucose Meter[/TD]
[TD]OP050302B[/TD]
[/TR]
[TR]
[TD]OP050300K[/TD]
[TD]Child Matrix Item[/TD]
[TD]Diabetes Care Pocket® Blood Glucose Meter[/TD]
[TD]OP050300K[/TD]
[/TR]
[TR]
[TD]OP050301P[/TD]
[TD]Child Matrix Item[/TD]
[TD]Diabetes Care Pocket® Blood Glucose Meter[/TD]
[TD]OP050301P[/TD]
[/TR]
[TR]
[TD]OP050303G[/TD]
[TD]Child Matrix Item[/TD]
[TD]Diabetes Care Pocket® Blood Glucose Meter[/TD]
[TD]OP050303G[/TD]
[/TR]
[TR]
[TD]OP070802B[/TD]
[TD]Child Matrix Item[/TD]
[TD]Diabetes Care Pocket® Blood Glucose Meter[/TD]
[TD]OP070802B[/TD]
[/TR]
</tbody>[/TABLE]

 

[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