Hi,
I have a problem I am not sure how to solve and will give it a shot here on the forum. Thankful for any help I can get!
I have a table which looks like the following:
| Product Name | Standard Part | Optional Part |
|---|---|---|
| A | A001 | B001 |
| Info1 | A002 | B002 |
| Info2 | A003 | B003 |
| A004 | B004 | |
| B005 | ||
| B | C005 | D006 |
| Info3 | C006 | D007 |
| Info4 | C007 | D008 |
| Info5 | C008 | D009 |
| C009 | D010 | |
| C010 |
I want to manipulate this table into two tables (Table 1 and Table 2). For Table 1 I want to combine the columns Standard Part and Optional Part into one and describe from which column they came from in a new column. Then I want to fill down the product name, in this case A and B, so that I can use it later on in the new format as Table 1. Therefore everything below “A” and “B” in this case should be removed and not filled down. How can I do this?
For Table 2 I want to use the additional info below “A” and “B” to map out these characteristics to keep in a separate table. Is this possible?
My dataset is around a million rows and it contains blank cells below each product (until the next one comes) and depending on if there are more standards parts compared to optional parts (and vice versa) there might be a space until the next product as well (visible in the initial table above).
Table 1
| Product Name | Part Number | Part Type |
|---|---|---|
| A | A001 | Standard |
| A | A002 | Standard |
| A | A003 | Standard |
| A | A004 | Standard |
| A | B001 | Optional |
| A | B002 | Optional |
| A | B003 | Optional |
| A | B004 | Optional |
| A | B005 | Optional |
| B | C005 | Standard |
| B | C006 | Standard |
| B | C007 | Standard |
| B | C008 | Standard |
| B | C009 | Standard |
| B | C010 | Standard |
| B | D006 | Optional |
| B | D007 | Optional |
| B | D008 | Optional |
| B | D009 | Optional |
| B | D010 | Optional |
Table 2
| Product Name | Info |
|---|---|
| A | Info1 |
| A | Info2 |
| B | Info3 |
| B | Info4 |
| B | Info5 |