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.

Please post a workbook with a good sample of data, rather than a picture.

Here's a sample of my inventory list because obviously, I don't want all of my company's stuff all over the internet.

I could easily do something like B=Ax/2 but it's not that simple. If a line disappears because we're back order on that product or if that product is discontinued (because only items that are in stock are generated by our smartlist), then if whatever formula was cell specific (instead of column specific), then it wouldn't work because there's a rotation of at least 50 items every week.

Thanks in advance!

OK, that's a start, but what now? What is supposed to happen, I don't follow - where do the values in B and C come from?

B's are the number of each items we have in stock. They're generated automaticaly by Dynamics GP 10.0, according to what we received from our distributors and what we sold to our customers.

C's are like a category that I'd like to add after exporting my report (it can't be managed by Dynamics right now for a few reasons). There are about 15 categories in total. Once the structure for what I want to do, I could change the "searched values" of A and the range for the random quantities.

I have to "hide" my real quantities for different purposes so instead of showing let's say 1374 of X item, it shows as 73.

Thank you for your time.

I might be being slow - entirely possible - but what do you want from a macro? Cols A and B are brought in from outside so do you just want to add col C? If so, what are the rules?

OK, yes, I think we're finally there! Will come back tomorrow.

Basically you need a couple of look up tables for cols B and C - I have added something on second sheet of attachment.

Please Login or Register  to view this content.

It's in the tables in the second sheet - col B is the lower limit, col C the upper. Unless you can import it from somewhere else, you'll just have to add items to the tables manually. If there is any rule or logic to it you may be able to automate part of it but if changes to the list are arbitrary I don't think so.

I think I understand now... what you call look up table is in sheet 1.

I love you!

I'd still like 2 macros because in some of our lists, we don't have any quantities. I know I didn't mention that before but when I realised what I would be able to do, I knew I'd that macro at other places!

Thanks a bunch

Run the macro and see what it does. If it's not in the list nothing is put in B and/or C.

Is there a way to do "Randomize, between 50 & 60, the quantity of X item if it's higher than 120"

120 is a fictionnal number.

Also, would there be a way to search "begins with" for let's the HDD thing?

Thanks a lot, sir.

Hang on, I'm losing you again.

For the HDD thing, one could use the LIKE operator, viz LIKE "HDD*" - do you mean it should look for "begins with" rather than "includes"?

Yes, begins with. We're changing stuff in our system so I'll have to change the nomenclature in a few days. I'll work my things around "begins with" so my items start with HARD DRIVE, or what ever I need.

Thanks a lot.

Please Login or Register  to view this content.

I didn't understand this:

"Randomize, between 50 & 60, the quantity of X item if it's higher than 120"

Bump!

Still looking for "begins with" and "random number between X & Y if cell value > Z"

Thanks everyone

Bump...

I really need that macro. It's "half" done and I've already cut my work time in two. When it's fully done, It'll take me a quarter of the time it used to take me.


Thanks a lot,

Eddie