PDA

View Full Version : Filter quantity of the commodity code from total data and minus decesing ???



mrsunftu
01-17-2018, 12:01 AM
Hi all,
Please help me about this situtation.
I have much orders which are have some kind of goods is the same. It's on the data_need_find sheet.
The inventory have the total data = data_source sheet.
And the question is, how I could make a macro to minus decesing the quantity of each kind of goods by each order, like this:

21366

the data_source:

21367

Thanks you so much.

p45cal
01-17-2018, 04:09 AM
You can do this without macros.
In the attached is a formula in cell L3 of sheet data_need_find:
=VLOOKUP($B3,data_source!$B$1:$C$75,2,0)-C3-SUMIF($B$2:$B2,$B3,$C$2:$C2)
which can be copied down.
The formulae in columns E to G are simple formulae all referring to column L
The formulae in cells shaded light green can be deleted because there's nothing in column A for those rows.
You should not delete the formulae in cells shaded red because this signals to you that the commodity in column A has not been found on sheet data_source.

p45cal
01-17-2018, 04:46 AM
Oh groan.
Now I see that I've completely wasted my time because you already had a solution elsewhere!
Read the effing rules!
You've cross posted this at:
https://www.mrexcel.com/forum/excel-questions/1039248-vba-filter-quantity-commodity-code-total-data-minus-decesing.html
and:
https://www.excelforum.com/excel-programming-vba-macros/1215868-vba-filter-quantity-of-the-commodity-code-from-total-data-and-minus-decesing.html
Both these sites have the same rules.

What's it all about? Read this: http://www.excelguru.ca/content.php?184
We're humans, not machines.