Updating inventory information in Excel

ccampagna

New Member
Joined
Dec 15, 2005
Messages
3
Please help. I have an inventory spreadsheet that has partnumber in one column and quantity in another column.


Part# Quantity
1................4
2................3
3................1


Recently I did a physical count of the inventory and came up with quite a few quantity changes:

Part# Quantity
1............7
2............1
3............25


Is there an excel function or forumula that can compare these 2 sets of information and update the partnumber's with the new updated quanitities?


I have over 1000 parts and don't want to manualy make the changes.

Thank you for your help.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
you can easily find the corresponding value using a Vlookup. Do you want the difference between the two, or just the "new" value?
 

ccampagna

New Member
Joined
Dec 15, 2005
Messages
3
Vlookup... Just the new quantity

I just need the new number... if I had 2 widgets and now I have 3, I want 3 to be placed in the field.

thanks
 

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
Use a vlookup command - it will give you the latest value.

The command goes something like this:
=VLOOKUP(D4,[Book1.xls]Sheet1!$A:$B,2,FALSE)

In the formula above, it is assumed that you want to find the value in D4 (I just picked a random cell on an open workbook) in a range of cells (columns A and B from Book1.xls), and return the value in the second column relative thereto. The false means that it will only return a value if it matches exactly.

A couple of things to point out -
First, if the value is not found, it will return #N/A. You can set up some if statements to get rid of that if you need to.
Second, column A&B can be on another worksheet and/or workbook. It is easiest to build this formula from scratch, but you can just modify mine if you feel more comfortable doing so.
Third, the value that it is searching for needs to be in the 1st column. If you want to return the corresponding value in the 10th column, then the $A:$B needs to be $A:$J, and the value just before the false needs to be 10. Something like this:

=VLOOKUP(D4,[Book1.xls]Sheet1!$A:$J,10,FALSE)

HTH,
colbymack
 

ccampagna

New Member
Joined
Dec 15, 2005
Messages
3
Vlookup is way to cool!

Thanks! It works like a charm... I though Data Advanced Filter was handy... but this function might be just as good. Thanks again... Happy Holidays from Texas...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,080
Messages
5,570,085
Members
412,310
Latest member
mark884
Top