Vlookup/matching help needed! for inventory management....

[jack]

List A:
Project Code Component Code Description Quantity for Project
123 456 abcd 1

List B:
Component Code Description Quantity in Inventory
456 abcd 500


I have two lists A &B, in the above format. I am trying to create a
function that will calculate total units on hand by subtracting "qty
for project" from "quantity in inventory" by "component code", and
keep a continual total as more projects are added.

For example, after putting project 123 into production, which calls
for 1 of component 456, I want to create a column that will subtract
that 1 from 500 = 499 units on hand of component 456.

I've tried a few different vlookups/matches...but have not yet found
one that works...please help!


Many thanks in advance!

 

[Guest]

Try a sumif formula. This will enable you to sum all of the components used
from your project list and total those units. Then a simple formula can
determine your remaining balance.

Example:
Beginning Balance Used Used
Remaining Balance
500 14 (determined by Sumif)
486

 

[jack]

Try a sumif formula. This will enable you to sum all of the components used
from your project list and total those units. Then a simple formula can
determine your remaining balance.

Example:
Beginning Balance Used Used
Remaining Balance
500 14 (determined by Sumif)
486










- Show quoted text -

How then do I link to the component #? Do I need a vlookup as well?

 

[Herbert Seidenberg]

Try Pivot Table.
No complicated formulas required.
Assume table A look like this:

Project P/N Qty_Rqd
AGC D151 32
B298 28
A375 37
K566 18
J726 35
E332 36
TRA G297 16
B847 15
K566 34
B298 14

Table B:
P/N Qty_Inv
D151 149
B298 446
A375 112
K566 623
J726 249
E332 232
G297 323
B847 126

Data > Pivot Table > Multiple Consolidation Ranges
Define first Range as the last 2 columns of table A.
The second Range is table B.
The header of the first column of the two Ranges
should always be the same (i.e. P/N)
Include extra rows for future expansion.
Uncheck/Hide Grand Totals, Subtotals
The resulting PT might look like this:

Sum of Value Quantity
P/N Qty_Inv Qty_Rqd Qty_Hnd
A375 112 37 75
B298 446 42 404
B847 126 15 111
D151 149 32 117
E332 232 36 196
G297 323 16 307
J726 249 35 214
K566 623 52 571
(blank) 0

The Qty_Hnd is an added field.
Click on Qty_Rqd, and from the PT tool bar
Formulas > Calculated Item
=Qty_Inv-Qty_Rqd
After you add more projects, refresh the PT.

 

[jack]

Try Pivot Table.
No complicated formulas required.
Assume table A look like this:

Project P/N Qty_Rqd
AGC D151 32
B298 28
A375 37
K566 18
J726 35
E332 36
TRA G297 16
B847 15
K566 34
B298 14

Table B:
P/N Qty_Inv
D151 149
B298 446
A375 112
K566 623
J726 249
E332 232
G297 323
B847 126

Data > Pivot Table > Multiple Consolidation Ranges
Define first Range as the last 2 columns of table A.
The second Range is table B.
The header of the first column of the two Ranges
should always be the same (i.e. P/N)
Include extra rows for future expansion.
Uncheck/Hide Grand Totals, Subtotals
The resulting PT might look like this:

Sum of Value Quantity
P/N Qty_Inv Qty_Rqd Qty_Hnd
A375 112 37 75
B298 446 42 404
B847 126 15 111
D151 149 32 117
E332 232 36 196
G297 323 16 307
J726 249 35 214
K566 623 52 571
(blank) 0

The Qty_Hnd is an added field.
Click on Qty_Rqd, and from the PT tool bar
Formulas > Calculated Item
=Qty_Inv-Qty_Rqd
After you add more projects, refresh the PT.

thanks again...

having a bit of difficulty still....the pivot table works well, but
there are some parts that are not consolidating. for example the
pivot table will show

Sum of Value Quantity