Curtis

Excel Gurus - Help Creating a Bill of Materials?
« on: February 21, 2008, 07:13:22 PM »
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?
--------------------
http://www.thethirdending.com
--------------------


Andre

Excel Gurus - Help Creating a Bill of Materials?
« Reply #1 on: February 21, 2008, 10:18:27 PM »
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

Excel Gurus - Help Creating a Bill of Materials?
« Reply #2 on: February 24, 2008, 04:04:05 AM »
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.
--------------------
http://www.thethirdending.com
--------------------

StephenGiles

Excel Gurus - Help Creating a Bill of Materials?
« Reply #3 on: February 24, 2008, 06:00:41 AM »
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
The best place to find a helping hand is at the end of your arm!

Andre

Excel Gurus - Help Creating a Bill of Materials?
« Reply #4 on: February 24, 2008, 11:33:28 AM »
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


 

Related Topics

  Subject / Started by Replies Last post
14 Replies
3146 Views
Last post April 07, 2007, 06:35:37 PM
by lagoausente
25 Replies
7006 Views
Last post November 19, 2016, 07:59:48 AM
by mhelin
4 Replies
1214 Views
Last post November 13, 2009, 06:02:28 PM
by regularjohn
6 Replies
1582 Views
Last post May 12, 2010, 04:49:42 PM
by Mike Cleaver