Stock file update; Distribute the items used

[deenoseban]

Hi All,
struggling with a huge file, which needs to be updated everyday.

The below is the sample data, column A is item number and B is stock in hand.
Column M & N is the used data, If I use a xlookup it will repeat the values (column C) , so I am manually distributing the values(column D)

Is there a smart way to automate this task , either thru VBA or some advanced formula.

 

[ETAF]

i assume M & N are all out of order
not sure if Xlookup v SUMIF , is different

i may not quite understand the purpose and why distribution or fully understand the examples

DST10105 = on hand total 95 , Used 45 - Required ??? why 45 , 25 +25

BUT maybe a sumif would help
SUMIF($M$2:$M$200000 , A2, $N$2:$N$200000)
rather than an lookup - only know if you try it and see if helps

 

[p45cal]

Try this:
In cell C3:

=IFERROR(MIN(VLOOKUP($A3,$M$2:$N$15,2,0)-SUMIF($A$2:$A2,$A3,$C$2:$C2),$B3),0)

and copy down to C23
In cell C2 only:

=MIN(VLOOKUP($A2,$M$2:$N$15,2,0),$B2)

(you could wrap that in IFERROR(…,0))

There is the assupmtion that there are no repeat stock items in column M (although it wouldn't be too difficult to adjust the formula to cater for that).

What version of Excel are you using?



It really would help us help you if you were to attach the file that you got the picture from rather than the picture:
a) It's easier for you (and you've done it before)
b) We won't make mistakes in copying the picture's values to a sheet
c) It saves us time
d) You're more likely to get a response

 

[deenoseban]

Hi All,
struggling with a huge file, which needs to be updated everyday.

The below is the sample data, column A is item number and B is stock in hand.
Column M & N is the used data, If I use a xlookup it will repeat the values (column C) , so I am manually distributing the values(column D)

Is there a smart way to automate this task , either thru VBA or some advanced formula.

View attachment 81529

 

[deenoseban]

Try this:
In cell C3:

=IFERROR(MIN(VLOOKUP($A3,$M$2:$N$15,2,0)-SUMIF($A$2:$A2,$A3,$C$2:$C2),$B3),0)

and copy down to C23
In cell C2 only:

=MIN(VLOOKUP($A2,$M$2:$N$15,2,0),$B2)

(you could wrap that in IFERROR(…,0))

There is the assupmtion that there are no repeat stock items in column M (although it wouldn't be too difficult to adjust the formula to cater for that).

What version of Excel are you using?

View attachment 81537

It really would help us help you if you were to attach the file that you got the picture from rather than the picture:
a) It's easier for you (and you've done it before)
b) We won't make mistakes in copying the picture's values to a sheet
c) It saves us time
d) You're more likely to get a response

Hi, Thank you for the feedback, I attached the excel file.

 

[p45cal]

Hi, Thank you for the feedback, I attached the excel file.

Yep, just tried it, it seems to work.

 

[deenoseban]

Yep, just tried it, it seems to work.

Thank you so much, it worked

 

[deenoseban]

i assume M & N are all out of order
not sure if Xlookup v SUMIF , is different

i may not quite understand the purpose and why distribution or fully understand the examples

DST10105 = on hand total 95 , Used 45 - Required ??? why 45 , 25 +25

BUT maybe a sumif would help
SUMIF($M$2:$M$200000 , A2, $N$2:$N$200000)
rather than an lookup - only know if you try it and see if helps

Basically this is stock master , column A & B is items received in store and M & N are the utilization report. So we keep on receiving the item and the utilization continues. We follow the FIFO method and we need to the status of items remaining.
Hope this clarifies .

Thank you.