Fist off this is first time using this blog and secondly this post is a long one.
I am running Office 2000 but can have access to Office 2010.
I am the inventory coordinator @ a large food and beverage facility.
We do not have an inventory management system. At the end of every month a physical inventory count has to be done. This is very laborious, there is about 500 items, the items are listed in a excel workbook (Master Inventory List), these items need to have a average purchase price (over the previous month) associated with them, said pricing is sent to me from my vendor in a second excel spread sheet (Monthly velocity Report).
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>So every price from the Monthly Velocity Report (MVL) needs to be inserted into the Master Inventory List (MIL) matching up with the corresponding Item. Each item has its own unique number. It is here where I need help.
<o
></o>First I have to address every item by the way it’s to be counted (Units) For instance. I buy tomato juice buy the case and I am charged by the case, there is 6 in a case>When I count them I count them by the unit, not the case. Next I need to figure out the unit cost that is done by dividing the case price by the number of units.
<o
></o>This info contained on the MVL, but the data itself needs to be manipulated. This is where I want to use Find and Replace function. One of the columns is ladled “SIZE” and it reflects what it says, IE – 6KG or 10LB or 5EA or 15L.
I need to remove all the “ALFA” data and leave all the “NUMBERS” in the column; these “numbers” will be used in a formula to determine the average price. Can I some how highlight the column in question and run a find and replace, based on the above perimeters? <o
></o><o
></o>From here I take the formula from above, apply it to the whole workbook and hopefully have an average price for all the items contained in the workbook. Then form their can I run a find and replace for the MVL workbook to the MIL workbook, based on the unique item number, replacing the old prices on the MIL, with the newly formulated average prices form the MVL workbook?<o
></o><o
></o>
