Excel Gurus - Help Creating a Bill of Materials?

[Curtis]

What is the easiest way to format Excel data from this kind of thing:

Into this format:

Years ago I used to use Pivot Tables to generate materials lists for telecom field engineers from AutoCAD drawings, but I only had to make a two column list (description/qty). I can't work out how to add the third column "component numbers" to show which components share the associated value. Quite predictably the Microsoft help file is of little help at all.

Anyone?

 

[Andre]

Here's some procrastination...

I made a sheet that is big and messy but does the job. It was also done in Excel 2007, which is a major pain in my side. There may be some compatibility issues with older Excel versions.

See the link:

http://members.cox.net/andremarquette/SSL9k/Parts_list.xls

In the 'Setup' tab
1. Enter the individual parts in columns B and C
2. Enter each value only once in Row 1
3. If a value in column C is not represented once in row 1, then column D will highlight in error. This will help you fill out Row 1.
4. After all is entered, switch to the 'Results' tab to see a pretty version.

 

[Curtis]

Thanks Andre,

You're right, there might some compatability issues with that Excel spreadsheet - it doesn't seem to want to update the list properly when I change some of the item quanitites (Excel 97) - but I can see how you're building that table up, so I'll have a bit of a play and see if I can get something working.

Cheers.

 

[StephenGiles]

There ought to be a way of doing this using the SUMPRODUCT function. You will almost certainly find something at
http://www.mrexcel.com/forum/forumdisplay.php?f=10

 

[Andre]

Curtis,

I updated the file to be more user friendly. Also, I found a mistake that would affect the quantity for each part. The example provided is for the SSL9k parts lists. It shows 2 errors so you can see how it works.

Good luck,
- Andre