Converting the structure of raw data into a usable format

Hi,

I am looking to transform a set of data into a usable structure as shown in the attached sample Data file. As of now, I did everything manually, but I would like to know if there is a faster and efficient way of doing this using formulas and functions. In the sample data, the data to the left side of the arrow is the raw data I have and the data to the right is the output I am trying to get.

Any help appreciated.

Thanks in advance

VJ

Can you provide a more comprehensive example as I am sure the current file is a simplified version of a real BOM.

Hi John,

Thanks for your response. I really appreciate it. Actually, the data in the attached file is how the actual data looks like except that there are more than 100 rows in the actual data. I only have replaced the Part numbers with ABC, BC, B, C, etc. To put this in other words, level 0 is the top level part (parent part/final product), level 1 goes in to level 0 parts to make them a final product. Level 2 goes into level 1 before they can go into level 0. So, if you imagine from bottom-up Level 2 are raw materials that are needed to make level 1 parts. Then the level 1 parts combined will make the end product. To make the final product ABC, B and C should be first put together to make BC and then BC is used to make ABC.

Basically trying to convert the Bill of Materials that I have in one format to another so that I can use them in another report.

I hope I explained it better.

VJ

Hi JeteMc

I really appreciate you taking the time to help me find a solution. It totally worked. Thank you so much!

VJ