Transforming 1level coded product tree to few level product tree

Hi there,

the solution I was trying was multiple vlookup function. But it seems quite complicated and still it requires a lot of effort and time. Oracle DB gives me an Excel file with all the products in one column and its parts in second column. It could seem that there is only one level bills of materials (BOMs), but actually our products could have many levels with many parts inside. So, Oracle DB has an unique ability to code (hide) information from me by giving just one level product tree. And what I need is to transform the data to normal BOM. I do not know if I expressed clearly, so I attach the Excel file from which you can have better view on my problem. Oracle product tree transformation.xlsx


I need a way to transform data to the table format I provide because later I could use pivot table.

Hi,

Attached is a VBA solution which should work in Excel 2003 and Excel 2007:

Please Login or Register  to view this content.

If you choose to use JB's solution the following macro in an ordinary code module should allow JB's formula to work in Excel 2003. Coincidentally, the following code was created by JB several years ago. Thanks again JB.

Please Login or Register  to view this content.

Lewis

I've just now spotted solution. When I opened it I've just started to laugh out of joy. Incredible! LJMetzger is genius! THAN YOU VERY MUCH!