Excel Spread sheet help

[Hoggslayer]

I can usually do pretty good in Excel but this one is stumping me.

I'm trying to set up a Bill of material (BOM) for a whole project. Piece parts, fasteners, and misc. material. My CAD software will give me a BOM for each sub assembly of which I can put into a Excel spread sheet. Now I want to combine all the like components in the spread sheet and sort the spread sheet to give me a total BOM of the whole project.
In the example you'll see that the top-level assembly is 1000-301 and I plan on building 1
The BOM for 1000-301 is: (all components in dark yellow)
1000-001 - 2ea
1000-003 - 2ea
1000-005 - 2ea
2000-701 - 2ea
2000-702 - 2ea
The BOM for 2000-701 is: (all components in red text)
2000-001 - 1ea
2000-003 - 3ea
The BOM for 2000-702 is: (all components in blue text)
2000-001 - 1ea
2000-004 - 3ea
Notice in the example that 2000-001 is used in both the 2000-701 and the 2000-702 sub assy. Because 2 of those sub-assembly are required that brings the total needed of the 2000-001 to 4.
The 2000-003 and 2000-004 totals are 6 each because 3 were needed in each of their assemblies and 2 of those assemblies were needed in the top level.

The example shows the items totaled and sorted (row 14 thru 23). Sorted according to columns B then C then D.
When looking for like items, all columns need to be evaluated because the only difference could be just one letter or number anywhere in them.
I’ve tried pivot tables, lookup formulas and everything. I’m about 40 hours in to the YouTube rabbit hole and I’m still stumped.

Any help would be greatly appreciated. This Project has 400+ drawings with about 1000 different components that I'm trying to total up.

[Bag Limit]

I initially thought that you'd just need to combine Columns B & C and then total from that. However, are you showing that line 6 breaks down into lines 7 & 8, correct? If so, then the quantity of 2 needs to multiply toward the sub-total of 1 (line 7) and 3 (line 8) to total 2, 2000-001 and 6, 2000-003? Correct? If so, how do you know lines 7 & 8 are a subset of line 6, is that defined?

[Hoggslayer]

I initially thought that you'd just need to combine Columns B & C and then total from that. However, are you showing that line 6 breaks down into lines 7 & 8, correct? If so, then the quantity of 2 needs to multiply toward the sub-total of 1 (line 7) and 3 (line 8) to total 2, 2000-001 and 6, 2000-003? Correct? If so, how do you know lines 7 & 8 are a subset of line 6, is that defined?

Yes it is defined in the quantity column. The CAD software I use identifies the subsets by indenting the Qnty.

Example:

[Wits_End]

Is cell color the only way to tell between a part and a subassembly?

Sent from my SM-G965U1 using Tapatalk

[Hoggslayer]

Is cell color the only way to tell between a part and a subassembly?

Sent from my SM-G965U1 using Tapatalk

No, I changed the color to make it more clear. The only way to tell in the real one is the indent in the quantity column and it is really hard to see.
They are separated by just spaces in front of the numbers making the cell formatted as Text, which makes it even harder.

1
^2
^2
^2
^2
^^1
^^3
^2
^^1
^^3

The TBH text box doesn't recognize the space in front of the numbers so I used a ^^^ for the space.

[banzai]

No, I changed the color to make it more clear. The only way to tell in the real one is the indent in the quantity column and it is really hard to see.
They are separated by just spaces in front of the numbers making the cell formatted as Text, which makes it even harder.

1
^2
^2
^2
^2
^^1
^^3
^2
^^1
^^3

The TBH text box doesn't recognize the space in front of the numbers so I used a ^^^ for the space.

The way to make these indented numbers unique based on the number of spaces is to break them into three or more columns using the "text to columns" function, delineating them by "spaces." Then you can sort by those newly created columns to group identical components. After your identical components are grouped together, you can sub-total those. Once all of your components are sub-totalled, copy the entire sheet and paste it on to another new sheet as text. Then sort that sheet so that all of the sub-total rows get grouped together and delete the non-sub-total rows. What is left should be a list of the unique components for the job with the total quantity for each.

I'll PM you my number and I can walk you through this over the phone if you want.

[Bag Limit]

Is there another method to export from your CAD software? I don't work with CAD, so I'm not aware. If it provides you with export options, ideally, it would provide a detailed breakout of all parts (duplicated) without the subassembly. That's the issue. You need to clearly identify the assemblies that drill down into the parts or separate parts only from the initial report. I don't think the text spaces will be enough of an identifier.

[Bag Limit]

Yep, what Banzai said. You could use text to columns. Great point!

[Wits_End]

Once you figure out a way to identify the subassembly levels, it's cake.
Saw those numbers and thought they were cell aligned left, center, and right and that was going to require vba.
Just like they said above, text to column using space to delineate. Then create a few extra columns on the left to act as multiplier columns. Then back to your pivot table.

Sent from my SM-G965U1 using Tapatalk

[TeamAmerica]

The way to make these indented numbers unique based on the number of spaces is to break them into three or more columns using the "text to columns" function, delineating them by "spaces." Then you can sort by those newly created columns to group identical components. After your identical components are grouped together, you can sub-total those. Once all of your components are sub-totalled, copy the entire sheet and paste it on to another new sheet as text. Then sort that sheet so that all of the sub-total rows get grouped together and delete the non-sub-total rows. What is left should be a list of the unique components for the job with the total quantity for each.



I'll PM you my number and I can walk you through this over the phone if you want.

This is what I would do using text to columns. But I would then go straight to pivot table rather than subtotal function.