Starting with this Single Level BOM table (Windows 7 Pro, Excel 2013). "Qty Per" refers to the quantity of the child needed in the Parent:
<tbody>
</tbody>
I need (a Macro) to generate the exploded (or stacked) table below. As an example doing this manually for the Grand Child, the process went like this:
1. For each single level BOM Parent/Child row (R), find # grand children rows (N)
2. Copy entire row (R) and insert N-1 copies below row R (Note: if N=1, no copy/insert is required)
3. Find grand child(ren) for each child and corresponding Qty Per in the single level BOM Table and copy/paste into Grand Child / Grand Child Qty per columns
4. Repeat steps 1-3 for Great Grand Children and Great Great Grand Children
<tbody>
</tbody>
| Parent | Child | Qty Per |
| PART-A | PART-A1 | 1 |
| PART-A | PART-A1121 | 5 |
| PART-A | PART-A2 | 2 |
| PART-A | PART-A222 | 4 |
| PART-A1 | PART-A11 | 2 |
| PART-A11 | PART-A111 | 1 |
| PART-A11 | PART-A112 | 2 |
| PART-A112 | PART-A1121 | 1 |
| PART-A112 | PART-A1122 | 2 |
| PART-A2 | PART-A21 | 1 |
| PART-A2 | PART-A22 | 2 |
| PART-A2 | PART-A221 | 3 |
| PART-A22 | PART-A112 | 4 |
| PART-A22 | PART-A221 | 1 |
| PART-A22 | PART-A222 | 3 |
| PART-B | PART-A1 | 2 |
| PART-B | PART-A21 | 3 |
<tbody>
</tbody>
I need (a Macro) to generate the exploded (or stacked) table below. As an example doing this manually for the Grand Child, the process went like this:
1. For each single level BOM Parent/Child row (R), find # grand children rows (N)
2. Copy entire row (R) and insert N-1 copies below row R (Note: if N=1, no copy/insert is required)
3. Find grand child(ren) for each child and corresponding Qty Per in the single level BOM Table and copy/paste into Grand Child / Grand Child Qty per columns
4. Repeat steps 1-3 for Great Grand Children and Great Great Grand Children
| Parent | Child | Child Qty Per | Grand Child | Grand Child Qty per | Great Grand Child | Great Grand Child Qty Per | Great Great Grand Child | Great Great Grand Child Qty Per |
| PART-A | PART-A1 | 1 | PART-A11 | 2 | PART-A111 | 1 | ||
| PART-A | PART-A1 | 1 | PART-A11 | 2 | PART-A112 | 2 | PART-A1121 | 1 |
| PART-A | PART-A1 | 1 | PART-A11 | 2 | PART-A112 | 2 | PART-A1122 | 2 |
| PART-A | PART-A1121 | 5 | ||||||
| PART-A | PART-A2 | 2 | PART-A21 | 1 | ||||
| PART-A | PART-A2 | 2 | PART-A22 | 2 | ||||
| PART-A | PART-A2 | 2 | PART-A22 | 2 | PART-A112 | 4 | PART-A1121 | 1 |
| PART-A | PART-A2 | 2 | PART-A22 | 2 | PART-A112 | 4 | PART-A1122 | 2 |
| PART-A | PART-A2 | 2 | PART-A22 | 2 | PART-A221 | 1 | ||
| PART-A | PART-A2 | 2 | PART-A22 | 2 | PART-A222 | 3 | ||
| PART-A | PART-A2 | 2 | PART-A221 | 3 | ||||
| PART-A | PART-A222 | 4 | ||||||
| PART-A1 | PART-A11 | 2 | PART-A111 | 1 | ||||
| PART-A1 | PART-A11 | 2 | PART-A112 | 2 | PART-A1121 | 1 | ||
| PART-A1 | PART-A11 | 2 | PART-A112 | 2 | PART-A1122 | 2 | ||
| PART-A11 | PART-A111 | 1 | ||||||
| PART-A11 | PART-A112 | 2 | PART-A1121 | 1 | ||||
| PART-A11 | PART-A112 | 2 | PART-A1122 | 2 | ||||
| PART-A112 | PART-A1121 | 1 | ||||||
| PART-A112 | PART-A1122 | 2 | ||||||
| PART-A2 | PART-A21 | 1 | ||||||
| PART-A2 | PART-A22 | 2 | PART-A112 | 4 | PART-A1121 | 1 | ||
| PART-A2 | PART-A22 | 2 | PART-A112 | 4 | PART-A1122 | 2 | ||
| PART-A2 | PART-A22 | 2 | PART-A221 | 1 | ||||
| PART-A2 | PART-A22 | 2 | PART-A222 | 3 | ||||
| PART-A2 | PART-A221 | 3 | ||||||
| PART-A22 | PART-A112 | 4 | PART-A1121 | 1 | ||||
| PART-A22 | PART-A112 | 4 | PART-A1122 | 2 | ||||
| PART-A22 | PART-A221 | 1 | ||||||
| PART-A22 | PART-A222 | 3 | ||||||
| PART-B | PART-A1 | 2 | PART-A11 | 2 | PART-A111 | 1 | ||
| PART-B | PART-A1 | 2 | PART-A11 | 2 | PART-A112 | 2 | PART-A1121 | 1 |
| PART-B | PART-A1 | 2 | PART-A11 | 2 | PART-A112 | 2 | PART-A1122 | 2 |
| PART-B | PART-A21 | 3 |
<tbody>
</tbody>
