Add new inventory list to existing list
 

I have an existing inventory with the item # and qty listed. When I add new
items to the inventory, I need to have a way to check for duplicates (besides
the Find/Replace functions), and if there are duplicates, add the quantities
together, otherwise add it as a new entry. HELP!

It can be done with the very useful SUMPRODUCT function. My example will need
some refinement, but illustrates the principle. I based it on only 20 entries.
I named a few ranges:
items: cells A2 to A21
quantities: cells B2 to B21
ranks: cells C2 to C21
A1 contains the heading "Item #" and B1 contains the heading "Quantity".
Enter the item number and quantity as you get the information.
C1 contains the heading "Rank". Cell C2 has the following formula:
=IF(ISBLANK(A2)=FALSE,RANK(A2,items,5),0)
This is copied down to C21.
Cells E1 to E21 are numbered from 1 to 20.
Cell F1 contains the heading "Item #" and cell G1 contains the heading
"Quantity".
F2: =IF(I2<0,H2/I2,"")
G2: =SUMPRODUCT(quantities,--(ranks=E2))
H2: =SUMPRODUCT(items,--(ranks=E2))
I2: =SUMPRODUCT(--(ranks=E2))
Copy F2:I2 to F2:I21.

"jweasl" wrote:

I have an existing inventory with the item # and qty listed. When I add new
items to the inventory, I need to have a way to check for duplicates (besides
the Find/Replace functions), and if there are duplicates, add the quantities
together, otherwise add it as a new entry. HELP!