# Create a list of bought-out items for various assemblies

#### aimeelou2006

##### New Member
I'm trying to create a list of bough-out items for various assemblies I'm making from week to week. These assemblies are called AA, AB, AC, etc through to ZZ, so as you can imagine there are quite alot. I do not use the same assemblies each week, so no week is the same. Each assembly has between 1 & 12 bough-out items per assembly, some of which are common to other assemblies. So what I'm trying to do is type into an excel spreadsheet which assemblies I want next week inorder to get a full list of bough-out items for that week. I did originally think of doing it by filters, but I think it could get a bit messy and lead to mistakes when ticking which assemblies, so what I though was; if I physically type each assembly into a spreadsheet with corresponding quantities this should reduce the chance of making mistakes. End result...... a list of bought-out items with quantities

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I think you need a sheet with all the assemblies listed down column A and all the items listed across in row 1 starting in column C (Leave B blank for now). On the each assembly row enter the quantity required of each item ( if zero then can leave it blank).

After setting up the matrix described above use sumproduct to calculate the number of each item.

There are lots of ways of setting up the weekly input for you to enter the planned production, of each item required.

The easiest way is to use column B to enter your planned production numbers. Under the static data in the matrix leave a blank row then use sumproduct to show the number of each item required that week.

Last edited:
Thank you for your comments, although after researching the tutorials on sumproduct I can't see that this is the way forward, unless I missing something.
I do agree with you in that I will have to create the matrix first, which will take a lot of time so I need to get it right on paper before I proceed. I don't think I have explained myself properly, the resulting bought-out list needs to consist of "Item", "Quantity" & "Description" columns with the various item numbers proceeding down the sheet under the "Item" title. I do not need to reference the assembies on the final bought-out list. My initial thoughts were creating 3 sheets, firstly the matrix, secondly the input of what assemblies I require from week to week and then finally the resulting bought-out list which would be generated using formaulas. There are alot of assemblies so scrolling through filters or cells looking for the correct assemblies isn't a good option from my point of view, this is why I thought of inputting manually the assembly codes and the quantities to generate a list, rather than using filters. Please note- the assembly codes are not AA, AB, AC, etc, I have had to use these as an example for security reasons. Any help would be appreciated.

Here is a small demo. Three sheets. MATRIX sheet serves two purposes, There is a lot of static data specifying all the Items in each Assembly. Once set up that part only changes as your products change. In column B each week you enter the planned production quantities of each assembly.
Sheet ITEM LIST is simply a list od all items you need to buy.
Sheet summary has only 5 rows, you will need to different items required in any week. I put in a warning mechanism.
Excel Workbook
ABCDEFG
1**Item components of each assembly****
2AssemblyPlanned ProductionItem1Item2Item3Item4etc
3AA111*2**
4AB122*1**
5AC8***1*
6etc**7***
7*******
8*******
9control row1*1023*
10control row2*Item1*Item3Item4*
11Total Quantity Req'd*350348*
matrix

Excel Workbook
ABC
1ITEMDESCRIPTIONCOST each
2Item1Apple5
3Item2Banana5
4Item3vegetable4
5Item4Tomato3
6etcstrawberry2
Item List

Excel Workbook
ABCDEF
1Number of different ITEM types required this week****3
2******
3*Item CodeDescriptionCost eachQtytotal cost
41Item1Apple535175
52Item3vegetable434136
63Item4Tomato3824
74*****
85*****
9ok*****
Summary

Edit: On Summary! the description in A1 refers to data in F1. Jeanie played around with my formatting.

Last edited:
Thank you very much. I've set myself up with a dozen or so assemblies so far and it works great.
I'm understanding much better now how the formulas work too.
I can't thank you enough.

Thanks for the feedback. The best time to understand the formulas is now, while the whole project is fresh in your mind. At some time in the future some aspect of your business will change and you will need to adapt the spread sheet. That will be much easier because you understand this one.

Replies
8
Views
199
Replies
1
Views
444
Replies
0
Views
201
Replies
3
Views
272
Replies
0
Views
340

1,203,461
Messages
6,055,559
Members
444,798
Latest member
PAO1609

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back