Extract Low level parts from a Hierarchial Bill of Materials

Dear svijikumar

Creating a macro for this will help automate the extraction process across multiple files.

Step-by-Step Solution
Select the Folder: Prompt the user to select a folder containing all Bill of Materials files.

Process Each File: Loop through each Excel file in the folder to extract the lowest-level parts based on the hierarchical structure.

Identify Lowest-Level Parts: Parse the hierarchy level in each file (".1" for level 1, "..2" for level 2, etc.).

Check if a part has a child level. If it doesn’t, mark it as a lowest-level part.

Extract and Save Unique Parts: Copy each lowest-level part to a new "Child Part" sheet within the same file.

Only keep unique parts in columns F and G
.
Consolidate Results: Combine all extracted unique lowest-level parts into a single Excel file.

Please Login or Register  to view this content.

@ Sudbhavani...

I would appreciate if you removed MY SIGNATURE...From Your profile...Please have a bit of integrity to come up with your own...

Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!

macro which can extract lowest level parts from the Bill of Materials that are in a folder.

My take on this...
Change below red snippet to your Path that houses the files...
And push the button...

Excel.png

Please Login or Register  to view this content.

Originally Posted by svijikumar

Hi Sudbhavani,
Thank you very much for the help on the macro.

Below are the observations
1. Child parts are not identified and created in each File
2. Column G of the input data is considered for the analysis. Attached is the output file after running the macro. Column D (Component Number) Should be considered

Each BOM should have its child part lists in the same excel
The identified child parts from each BOM should be summarized in the output excel along with the source BOM # for traceability

Consider Column D (Component Number): The macro currently focuses on extracting unique parts based on values from Columns F and G. To align with your requirement, the macro must reference Column D instead of Column G for the unique parts identification and extraction.

Child Parts Identification: It seems that the child parts are not being properly identified and created. This could be due to incorrect handling of hierarchy logic. Let's refine the logic to correctly detect child parts.

Please Login or Register  to view this content.

The macro currently focuses on extracting unique parts based on values from Columns F and G

As I understand it, the sheet "Child Part" has the required result extracted in A & B and then F & G is only the unique based on the extracted A & B...

If this is the case then...@svijikumar...Code in post 4 does this directly to F & G bypassing the need for A & B extraction first...