Automate distribution in excel spreadsheet

[marreco]

hi

Every day I get more than 50,000 items in the array to be distributed to the branch, but it is becoming increasingly difficult, because the list is growing.
I do not have even a little knowledge of VBA and macro to automate this routine, I sent it to a link to download the file in Excel called "planning", within that file contains three worksheets, the first of which is "ORDER OF THE MATRIX" the second is called "PASSING tHE BRANCH" and the third is "result_manual" which is only demonstration of how they would do manually.
Just let little item, because it would be difficult to download the spreadsheet at the link, but I want it to do is this:

1 - In the spreadsheet ORDER OF THE MATRIX, he located the items and quantities that distributes this column "Quantity Supplied" corresponding worksheet in "ORDER OF THE MATRIX" in worksheet "PASSING THE BRANCH", according to this column that the "quantity to be supplied "the worksheet" PASSING tHE BRANCH "so that it does not exceed this amount, the result of the distribution should put it in the column" amount distributed "(worksheet" a BRANCH TRANSFER ").

2 - If there is any amount that exceeds the amount to be provided in spreadsheet "PASSING THE BRANCH" he put in the column next to a warning as "disagreement", "surplus" and so on.

3 - If after the distribution, and had completed the amount specified by the matrix, but no balance left to complete the remainder of the item located at zero and it completed in the next column show "Missing" (in "result_manual", the items are so populated with the number zero).


thank you

[Charles]

Hi,

Your example needs to show what you refer to as "Missing" and "Disagreement".
Please provide a better example.

[marreco]

Hi.
if you crunch the numbers 0 (ZERO) in column "C" tab "result_manual" that will see the intens flatam value to complete and are marked with ZERO

[Charles]

Marreco,

Can you provide a step by step example?
THe only thing I see is that you just copy from

"QUANTIDADE A FORNECER" from Final to Result.
But the Data in result


QUANTDADE DISTRIBUIDA
You do not tell use how you determine it.

[LalitPandey87]

Please find solution at below mentioned link:

planning.xlsm

[Charles]

LalitPandey87,

Nice bit of code.(I'm not use to "Scripting")

But what if the data in column "C" of the REPASSE A FILIAL sheet does not = 0?
I think the user needs to provide a better explanation as to the achieve the desired result.

[marreco]

HI. LalitPandey87
thanks for replying

Charles must be right, I'm not explaining right.
I did the tests, but it is not, for example, what I realize is that it thus:

I'll take the example of "Motor EK" on worksheet "Ordem da Matriz", he gave 1500, but the spreadsheet "Repasse a filial" have the "Motor EK" with quantities of 2,500, 2,600, 21,000 and 45,000, what he has to do is get the amount defined in the worksheet "Ordem da Matriz" and fill the quantity respecting the limit of the column "Quantidade a Fornecer" until reset.

He would perform as well as the item RESISTENCIA 4KT example, the worksheet Ordem da Matriz", received 890 units and should distribute to affiliates as follows if done manually up to the 890, which remain at zero balance or you will be sent next consignment:

MATERIAL QUANTIDADE A FORNECER QUANTIDADE DISTRIBUÍDA RESISTENCIA 4KT 1,00 1,00 RESISTENCIA 4KT 5,00 5,00 RESISTENCIA 4KT 6,00 6,00 RESISTENCIA 4KT 10,00 10,00 RESISTENCIA 4KT 18,00 18,00 RESISTENCIA 4KT 20,00 20,00 RESISTENCIA 4KT 30,00 30,00 RESISTENCIA 4KT 500,00 500,00 RESISTENCIA 4KT 10000,00 300,00

TOTAL 10.590,00 890,00

Could perform the same action on the worksheet "Repasse a filial" in order to remain easy viewing.

[Charles]

marreco,

In your sample you mentioned "Motor EK" but you then referred to "Resistencia".
You should keep to the same Item.
Aside from that I still do not understand your request.
Please try again with a step by step as to what you look at
and then what you would do in each step.