subtraction in differnt way



I need help from experts to fix  this code. so I would  make  your  attenion about  in sheet KEYS OUT data in COL B,C,D  repeat  for  many  times  for  differnet items in COL A  so I  would  take  the  ITEM  in COL A  (AA-1000,BB-1000)  as  you  see  the  data  in COL B,C,D  are  repeated for  two  items let's move to sheet SH  as highlighted in row2,7   the  data based on COL E,F,Gfor  the  same  ITEM in COL A  should  search  in sheet KEAYS OUT  and  subtracting from it and  shows  the  result in sheet RS .  as you note in sheet RS  the  different in client  and  order  just  merge with comma  . the QTY for item  AA-1000   CLOT-MM      LL1       BR        is 12   so  it  should  beacome  2 in sheet RS  after subtract  two QTY are  existed  in sheet SH    for duplicate  data for  the  same  item (12-5-5)=2  in sheet RS  after subtracting sheet KEAYS OUT from sheet SH  short words  when  match  data  between sheets KEYS OU   based on COL B,C,D  for each item in COL A  & SH  based on COL E,F,G for the  same  ITEM in COL A  and  if  repeated  the  data  in COL E,F,Gfor  the  same  item   in sheet SH  should subtracting total  from main QTY in sheet KEAYS OUT .

thanks in advance 




It's not clear to ne what you're trying to do (even from the note in sheet "right"), sorry.

Please edit your question to show how a single repeated item is "subtracted" now and how it should be.
John_Ru (rep: 1917) Sep 14, '21 at 4:54 am
Hi  John 
see my  updating . I  hope  this  help 
Hasson (rep: 8) Sep 14, '21 at 6:25 am
Add to Discussion


Selected Answer


"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):

  1. Added new variables
    Dim RptArr As Variant, RptOC As Variant 'variable to combine repeats and OrdClnt
  2. 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, " ")

    '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



so  how  can  I  fix  this  line  . this  is  the  most important part of  question as  for  me .
Hasson (rep: 8) Sep 14, '21 at 12:10 pm
Hasson, not sure and have no more time today. Will try tomorrow 
John_Ru (rep: 1917) Sep 14, '21 at 12:26 pm
OK I  hope  to find  solution  with  you  . because  I Completely unable to solve this problem.
Hasson (rep: 8) Sep 14, '21 at 12:54 pm

Please see my revised Answer.
John_Ru (rep: 1917) Sep 15, '21 at 5:11 am
actually I no know how thank you for solve this dilemma. it  caused me  headache . thanks so much for your assistance
Hasson (rep: 8) Sep 15, '21 at 9:18 am
Thanks for selecting my answer Hasson. I'm glad it worked for you (it took me some time to understand and work with your existing macro). I'm thinking about trying to return to work so perhaps I won't be able to solve such problems in future.
John_Ru (rep: 1917) Sep 15, '21 at 9:32 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login