Consuming stock when attending only in full requests [Prob. VBA]

SunProj3cT

New Member
Joined
Nov 2, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello, guys

I'm having a hard time finding a more intelligent (and less repeatable) way of solving a problem I have at work. I think that the only way of solving it is with VBA, but I'm totally open to suggestions

Basically, I have a database of stock by SKU and another database of requests by SKU, which also contains some informations such as: Quantity ordered, Total Value ($) per client (sum of all total value of all requests from the same client) and SLA.

The priority list of requests to be attended - if possible - is (1) from greater to lesser SLA and (2) from greater to lesser Total Value per Client.

What I need to do is to check if every SKU on the same request has an available stock equal or greater than the quantity ordered. If so, then I would need an info (such as a verification column with a number "1" on it) that this request will be attended and I would also need an updated stock with the consumed quantity. If at least one of the SKUs cannot be attended, then the entire request will not be attended, the stock will not update and the verification column would have its info as well (such as a number "0" on it).

Even though I think it doesn't seem to be a difficult problem to solve, I'm not finding a way to get rid of countless loops and ifs.

A loop to input the table into a matrix, a loop to verify if the request is still the same request, a loop to check the quantity with the available stock, a loop to update the stock, a loop to check if the full request can be attended, a loop to "re-update" the stock to the older one if the full request cannot be attended, and on and on and on...

Here are some screenshots of the database to help clarify the issue.

Thanks so much in advance!
 

Attachments

  • db01.png
    db01.png
    36.4 KB · Views: 20
  • db02.png
    db02.png
    12.9 KB · Views: 20

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,223,958
Messages
6,175,643
Members
452,663
Latest member
MEMEH

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