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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
you can easily find the corresponding value using a Vlookup. Do you want the difference between the two, or just the "new" value?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top