2

I am creating a cycle count sheet. Sheet 1 will be user input where the material and quantity found will be put. Sheet 2 is a snapshot of inventory at the time of the count. I want the quantity of the material found to be distributed among the quantities on sheet 2 until the sheet 1 quantity is exhausted. It would also be helpful to distribute the quantity in order of the newest batch(day code) to the oldest batch(FIFO).

I can make this work when only looking at one material at a time with MIN MAX functions. The trouble I am having is when I add criteria. I have attached a screen shot of the desired results. As you can see, an ideal formula in D11 Found that 30,000 of P9919617 was available on 'sheet 1' and distributed that amount through inventory on 'sheet 2' until the 30,000 was exhausted leaving an adjustment of 10,584 to the 2278US9602 Batch.sheet 1 where user inputs data sheet 2 where inventory snapshot is pasted and adjustments are calculated I have attached a link to the sheet below as well. https://docs.google.com/spreadsheets/d/1RsmNMZS6yy7ayWs0cdYJlWyqTCgdieNXLB9sancsg0Y/edit?usp=sharing

player0
  • 124,011
  • 12
  • 67
  • 124
Adam Oaks
  • 23
  • 5

1 Answers1

0

try:

=INDEX(LAMBDA(Q, A, S, IFERROR(IF((COUNTIFS(A, A, ROW(A), ">="&ROW(A))=1)*(VLOOKUP(A, S, 2, )<>""), 
 VLOOKUP(A, QUERY({Q, VLOOKUP(INDEX(Q,,1), S, 2, )}, "select Col1,Col2-Col3"), 2, ), 0)))
 (QUERY(A2:C, "select A,sum(C) group by A"), A2:A, Sheet1!A:B))

enter image description here

UPDATE:

=INDEX(LAMBDA(p, LAMBDA(r, LAMBDA(u, LAMBDA(s, t, w, 
 IFNA(IF(s<>"", s, IF(t<>"", t, w))))
 (IF(r<0, 0, ), IF(COUNTIFS(p, p, r, ">0", ROW(p), "<="&ROW(p))=1, r, ), 
 u-ARRAY_CONSTRAIN({""; u}, ROWS(u), 1)))
 (IF(COUNTIFS(p, p, r, ">0", ROW(p), "<="&ROW(p))>0, r, )))
 (MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(p))>=
 ROW(p))*(p=TRANSPOSE(p)), OFFSET(p,,2), 0)), ROW(p)^0)-VLOOKUP(p, Sheet1!A:B, 2, )))
 (A2:INDEX(A:A, MAX(ROW(A:A)*(A:A<>"")))))

enter image description here

step-by-step formula explanation

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    This is perfect so far in testing. Thank you very much. You are a wizard! – Adam Oaks Nov 09 '22 at 20:33
  • 2
    So this does work perfectly. Instead of having this solution given to me Id like to learn how such a formula works. IF you have free time Id love to collaborate or an explanation of the formula would be amazing. – Adam Oaks Nov 10 '22 at 12:04
  • Update, I have encountered an issue I did not test or expect. If quantity of the material being distributed from 'sheet1' does not meet the requirement of the first instance of the material on 'sheet2' a 0 adjustment is being shown. The final instance of the material on 'sheet2' is then absorbing the full adjustment for all instances of the material. I have updated the sheet to better visualize this. – Adam Oaks Nov 10 '22 at 20:27
  • @AdamOaks your update is quite challenging sadly proposed solution no longer stands and it will need a complete redesign of the logic to accommodate your update. I played with it for 2h and have not found a solution yet. I shall let you know if I find the right spell in my grimoire. – player0 Nov 10 '22 at 23:46