Excel Gurus - Help Creating a Bill of Materials?

GroupDIY Audio Forum

Help Support GroupDIY Audio Forum:

This site may earn a commission from merchant affiliate links, including eBay, Amazon, and others.

Curtis

Well-known member
Joined
Aug 24, 2006
Messages
305
Location
Australia
What is the easiest way to format Excel data from this kind of thing:

excel1a.jpg


Into this format:

excel1b.jpg


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?
 
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.
 
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.
 
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
 

Latest posts

Back
Top