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

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

eachrow & column, you would no room for data.Do you see my confusion?

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.

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.

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.

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History