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 product but if same new stock purchase code auto assigned will be WT-B-2.
In sheet Sales
I have multiple columns.
I have to choose product then name <It is tabular data>
Sales B2 is, Product: Water Tank <WT>
Sale B3 is Name, Bash <B>
Sale C3 is my problem.
Problem:
I want to auto pick Code assigned to that stock in Sheet Inventory.
Lets suppose,
If WT-B-1 purchased 100 Units.
Then Code WT-B-1 will auto extracted to Sale C3 until stock becomes 0 for WT-B-1.
Then for next Sale, Sale C3 will shift to WT-B-2 <It represents same product, new stock>.
Data validation, if old stock is only 1 unit but user enter 2 units, it will not be allowed. Instead it suggests that only 1 unit left. Split in 2 sales entry. Choose 1 for old stock and 2nd for new stock.
I tried but it always return 1st code of product. Never goes to 2nd then 3rd and so on.
Thanks
7 Replies
In this case generally , we need an accounting double entry journal and G/L system to summary balance sheet and income statement if you need to track cash or AR.
Now,you only want cost and profit ,so simplize the joural as below.
I guess it will be easier for further profit/stock in hand calculation if you consolidate inventory and sales journal in one sheet rather than two work sheets.
Because you need calculate stock in hand qty of each batch inventory before you sale out whole or partial of specific in_stock batch goods.
I have checked your formulars which occupy many cells with some complex expressions and may slow down your Excel running when data grows.
If I were you who need check out specific inventory for sale each time, I would query the current stock on hand of sepecif code (e.g. like WT-B) with sql:
select code,sum(qty) from Transaction_Journal where Code like 'WT-B%' group by code;
Then I can insert the journal with proper products Code.
Additional,if too many rows are applied data validations,running slowly will occour.
So it is necessory to setup an input form for data validation and appending new record rather than data validation in many cells.
TransNo Date Code qty memo amount type
p_01 2023/1/1 WT-B-1 100 purchase 200 stock p_02 2023/1/5 WT-B-2 20 purchase 60 stock s_01 2023/1/7 WT-B-1 -100 sales-cost -200 stock,profit s_01 2023/1/7 WT-B-2 -2 sales-cost -6 stock,profit s_01 2023/1/7 WT-B-1 sales-income 400 profit s_01 2023/1/7 WT-B-2 sales-income 8 profit s_02 2023/1/8 WT-B-2 -2 sales-cost -6 stock,profit s_02 2023/1/8 WT-B-2 sales-income 8 profit Code sum(qty)
WT-B-1 0 WT-B-2 16 - 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？- 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.
- Is it too hard for you? For it is too.
Some logical explanation required:
1. Sum of total <Product+Name> sold in Sales till current row like $A$1:$A1. & pick code from Inventory Codes.
2. Lookup for Inventory <Product+Name>.
Now,
Inventory <Product+Name> - Sales <Product+Name>
Let's say,
Inventory <Product+Name> = 100
Sales <Product+Name> = 99
Code continues unless it becomes equal to 100. Then it will be Over Stocked or Sold out.
3. Again do the same process but also check is <Product+Name> available or sold out, if sold out then go-to next code.
Note; Previous entries with previous codes must not be destroyed.
How can I translate this in Microsoft Excel? Need an expert
