Solution need in Excel

[Dijoml14]

Hi,

I requested you to kindly help for resolve the problem.

I’m facing one issues in excel. I need one equation for resolving this issue.

Please find the attachment. I have attached one three picture's.

First picture name is “Bin Details” and second picture name is “Pullout Qty” and third picture is Output (My requirement)

First sheet contained Bin, Item Barcode and Qty.

• Item Barcode will be showing in different places because of item binned in different bin location with different qty.
• Same Item barcode not repeating in same bin but different item Barcode will be showing in the same bin.

Second sheet contained Item barcode and Pullout Qty.

Item Barcode and Qty not repeating in the sheet.

Requirement is :-

Pullout Qty sheet mentioned item Barcode against qty need to add in Bin Details sheet in separate column.

Example : In the Bin Details sheet Item Barcode “1111” showing in three different bins with different qty and total qty is 12 (Bin A1 qty is 1, Bin F1 qty is 6 and Bin K1 qty is 5). Customer given Pullout Qty 10.I need to pick 10 qty from the different bins because ten qty not available in one bin. Please check the Output sheet.

 

[Marcelo Branco]

Welcome to Mr Excel Forum

A picture is not helpful - is not possible to copy/paste for testing purposes
Try xl2bb

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Anyway, assuming the sheet name that contains Pullout Qty is Sheet2 (adjust to suit), maybe

D2 copied down
=MAX(0,MIN(C2,VLOOKUP(B2,Sheet2!A:B,2,0)-SUMIF(B$2:B2,B2,C$2:C2)+C2))

Hope this helps

M.

 

[Dijoml14]

Welcome to Mr Excel Forum

A picture is not helpful - is not possible to copy/paste for testing purposes
Try xl2bb

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Anyway, assuming the sheet name that contains Pullout Qty is Sheet2 (adjust to suit), maybe

D2 copied down
=MAX(0,MIN(C2,VLOOKUP(B2,Sheet2!A:B,2,0)-SUMIF(B$2:B2,B2,C$2:C2)+C2))

Hope this helps

M.

Hi Marcelo Branco,

Hope you are doing well. Sorry for the late response
As you provided excel equation formula is working properly and we saved lot of time .It's very useful for my works . Thanks for your support and immediate response.

Regards,
Dijo ml