Forum Discussion
Lookup with 3 Criterias and return next available Product Code from another sheet
I have two sheets Sales <tblSales> Inventory<tblInventory> In sheet inventory I have14 columns data. But I am interested in column Code. It is auto generated. Like WT-B-1 for same...
Like WT-B-1 for same product but if same new stock purchase code auto assigned will be WT-B-2.
Do you mean WT-B is the unique product code and suffix -1,-2 are purchase batch？
Do you mean WT-B is the unique product code and suffix -1,-2 are purchase batch？
- Yes, Suffix extracted from product and names
Than add countifs and add no. - Yes dear,
In sheet inventory, there is a list of products with unique Id<Codes> which is also auto calculated. For ease, I am attaching file link so you can analyze yourself.
https://1drv.ms/x/s!AtF-LIDedF0Hxi3bRybMzmukwz84?e=UqAUGE - Or
First-in, first-out (FIFO)？- I replied before,
Currently I have this part,
INDEX(Inventory!$B$2:$B$13,MATCH(1,(Inventory!$C$2:$C$13=B2)*(Inventory!$N$2:$N$13=C2),0),1)
But third part is quite logical which I didn't know how to apply,
Logic is simple,
Sum total Product with names in sales, from 1st to last<current> row. Like $A$1:$A1. Then check how much Units purchased. Subtract Sales total from purchased Units. If it is less the code is for running stock, if unit sold = purchased Units, then go-to next code for same product and name with different codes. Hope it makes sense to you.
