Changing values based on existing data

Hello everyone!

I’m pretty sure it’s something that can be done and I’ll try to explain this as clearly as I can.

I have to generate an inventory list of various products from Dynamics GP. I can export my smartlist into an excel file so that part is easy. I have, in column A, the list of items in my inventory (see picture 1). In that same file, I also have column B, which are the quantities of each items. I want to run this “formula” so that it does a search for specific items and modifies the quantity for that said item. I have about two hundred lines but some quantities can’t be known by our customers (some are in stock at over a thousand).

Picture 1 has to become Picture 2 in a few mouse clicks.

Here’s the best way to explain all of this:

If cell Ax (x being the line for that value) contains “500Gb Hard Drive”, then cell Bx = random number between 50 & 60.

I have about 70 items I need to change the quantities for but they’re always the same.


Also, even though I’m 99% sure the answer to my previous question will be the same for this one, I’ll just make sure I’m on the right path.

After all my quanties have been changed, I’d like to add values to C.

If you find “Hard Drive” somewhere in A, add HDD to C.

If I could have the body of the “formula” so that I could just replace the value for each of the 20-ish items for every line, it would be great. I think that would probably require a VB module but I’m not sure. As long as I can paste only the values, I don’t care how I get there. It has to be automated in some way, meaning that I would just have to run the “module”.


Thanks a lot for your help, I’ve been doing those changes manually for the last 2 years and it would be of great help.