Sum of components used

Microsoft Excel 2003 (full product)
January 12, 2010 at 02:01:39
Specs: Windows XP
I have an excel workbook containing 2 lists:
1. An array of components for each assembly configuration (essentially a bill of materials). Components are in rows. Assembly configurations are in columns. At the intersection of each cell is the number of each component used in the respective assembly configuration.

2. A second array with dates in columns and assembly configurations in rows. Assemblies may be repeated in several rows (based on order numbers). At the intersection of each row & column is the quantity of assemblies built.

I would like to track in another array (or extension of the first) the quantity of components used by date, based on assemblies built.

Any idea how I can do so? I tried using conditional sum but it gets really complex as the number of assembly configurations(columns) can increase and the number of assemblies built changes based on orders.

Thanks


See More: Sum of components used

Report •


#1
January 12, 2010 at 08:05:38
I've read your post a few times and I'm having trouble grasping your layout.

I think I understand "components in rows" and "configurations in columns", but some of your other words are unclear, at least to me.

In Part 1 you said: "At the intersection of each cell is the number of each component used in the respective assembly configuration."

The intersection of each cell? Individual cells don't really have intersections. A cell could be at the intersection of a row and a column, but I'm not sure what you mean by "At the intersection of each cell".

In Part 2 you said: "At the intersection of each row & column is the quantity of assemblies built."

OK, so yes, you can have an intersection of a row and a column, but if you have a quantity at the intersection of each row & column, you would no room for data.

Do you see my confusion?


Report •

#2
January 12, 2010 at 14:39:13
Thank you for your response, and sorry for the confusion. I will try to clarify.

The first table contains data which is the quantity of each component used in each assembly. So components are listed in rows (for example A2:A11 would have components 1-10). Assemblies are listed in columns (for example B1:F1 would have assemblies A-E). By intersection I meant each cell within the table is an intersection of row & column, and the number in that cell would be the quantity of that component for that assembly. For example the number in cell B2 would contain the quantity of component 1 used in assembly A.

In part 2, this is a second table. Listed in rows are the assemblies to be built (for example each cell A20:A30 would contain one assembly from the list of assembly A to assembly E. The same assembly may be repeated depending on each order). Listed in columns are just build dates (for example B19:Z19 would contain just dates). The data in this table is the quantity of each listed assembly built on each date. For example the number in cell B20 would be the quantity of the assembly in A20 that is built on date in B19.

So the goal is to have a 3rd table (currently an extension to the right side of table 1) which lists for each of the components the total component usage by build date.

I hope this clarifies the intent. I'm not sure how to upload a sample excel sheet to show the above, which would make it easier to understand. Thanks for your help.


Report •

#3
January 12, 2010 at 15:03:03
Thought I could try to clarify what I meant with tables below.

Table 1:
__________AssyA_____AssyB____AssyC ...
Comp1 ___qty1inA____qty1inB____qty1inC
Comp2 ___qty2inA____qty2inB____qty2inC
Comp3 ___qty3inA____qty3inB____qty3inC
...

Table2:
_______________Date1_____Date2____Date3...
AssyA (order1)__qtyAbuilt__qtyAbuilt__qtyAbuilt
AssyC (order1)__qtyCbuilt__qtyCbuilt__qtyCbuilt
AssyA (order2)__qtyAbuilt__qtyAbuilt__qtyAbuilt
AssyB (order3)__qtyBbuilt__qtyBbuilt__qtyBbuilt
AssyB (order4)__qtyBbuilt__qtyBbuilt__qtyBbuilt
.....

Table of sums wanted (3rd table or extension of table1):
__________Date1______Date2______Date3 ....
Comp1___qty1used___qty1used___qty1used
Comp2___qty2used___qty2used___qty2used
Comp3___qty3used___qty3used___qty3used
...

Thanks.


Report •

Related Solutions

#4
January 12, 2010 at 17:14:58
I'll play with this a bit, but somehow this seems like something a Pivot Table might be used for.

Unfortunately, I don't know too much about Pivot Tables, so I could be wrong.


Report •


Ask Question