I have a dataset (please find a sterilized example copy attached) that has a SKU and its associated BOM for each record. I derived this by using the 'Text to Columns' feature on comma-separated lists of SKU BOMs. I would like to stack all of the individual BOM items in one column. Naturally, there are a varying amount of BOM items in each BOM, such that the number of rows to add in under each SKU varies accordingly. I'm hoping someone can help me find the best way (macro, formula, or otherwise) to:
- Insert the appropriate number of rows underneath the desired SKU
- Transpose the BOM items to a column such that all BOM items are stacked one on top of the other.
- *Bonus Points* Copy the original SKU into the first column of the newly inserted rows so that each BOM item is still associated with its SKU.
Doing this process manually isn't too bad, but I've got nearly 10,000 SKUs to work on :/
Please let me know if my question requires any additional clarification! Thank you in advance for your time and consideration.