Selected Answer
Hasson
"Repeated" items in sheet Sh are NOT subtracted because of the line which writes the value of the dictionary d (extracted here, with my comment):
'this next line overwrites the value of the dictionary item
d(Join(Array(CurrItm, a(i, 5), a(i, 6), a(i, 7)), ";")) = OrdClnt & " " & a(i, 8)
So for the combined item "
AA-1000;CLOT-MM;LL1;BR", the value is set to "ORD-1;CL1 5" (by line 2 of SH) then becomes "ORD-4;CL1
5". That means when you check against sheet "
Keys Out", only the 5 from ORD-4 is subtracted from the 12 in row 2 of that sheet and only ORD-4 appears in your output.
To get the result you need, I've done this to the Module1 macro in the attached revised workbook (keeping most of your code/method):
- Added new variables
Dim RptArr As Variant, RptOC As Variant 'variable to combine repeats and OrdClnt
- re-used your Variant variable s to combine Ord and Cl elements (with a comma separator) and combine the quantities of "repeated" items by replacing this single line:
d(Join(Array(CurrItm, a(i, 5), a(i, 6), a(i, 7)), ";")) = OrdClnt & " " & a(i, 8)
with the following commented code:
'create dictionary key
s = Join(Array(CurrItm, a(i, 5), a(i, 6), a(i, 7)), ";")
'check if s is not repeated in SH
If d.exists(s) Then
'd key repeated so combine OrdClnt and qty
RptArr = Split(d(s))
'combine Ord and Clnt
RptOC = Split(RptArr(0), ";")
RptOC(0) = RptOC(0) & "," & a(i, 3)
RptOC(1) = RptOC(1) & "," & a(i, 4)
RptArr(0) = Join(RptOC, ";")
' add qtys
RptArr(1) = RptArr(1) + a(i, 8)
' rewrite value
d(s) = Join(RptArr, " ")
Else
'write new key and value =qty
d(s) = OrdClnt & " " & a(i, 8)
End if
The results of subtracting from the balances in "Keys Out" are directed to a new green worksheet "RS_modified" (which is not yet formatted like RS). Your "repeated" item is row 2 now shows a correct Balance of
-4 since the Balance in "Keys Out" is 12 LESS the addtion of SH row 2 (qty 5), row 7 (qty 5) and (new, demo) row 8 (qty 6) i.e. 12 less 16 is -4.
Note that I have NOT stripped out the "ORD-" portion of repeated items in SH- the macro gives whatever you put in the Order column of SH. Make it just numbers (1,4...) and the results will match.
Hope this works for you
