Need to extract the stock count in different WS as per Stock Ageing Analysis Bucket

Hello VBA Experts,

Greetings!!

I came to this platform after a long absence to request help from VBA coding experts for a report. The report mostly concerns stock ageing days. I need to use the Latest operation date column to extract the stock count (IMEI Count) using the FIFO approach. The 3 sheets will be created using VBA coding from the raw stock dump.

1. Model-Wise Ageing

2. MD Wise-Stock Ageing

3. Dealer Wise-Stock Ageing

Stock ageing buckets might vary depending on analysis, but in this case I've chosen the following buckets: <45 Days, 45 to 90 Days, 90 to 180 Days, and >180 Days.

Sheet1. I just want to extract the stock count for the Zone and Product model at the level of MD/Market stock level according to the stock ageing analysis bucket. (In the dump, I have two stock levels: MD (Distributor) and Market.

Sheet2. I want to extract the stock count for MD wise (Distributor wise) with Product Model & Color according to the stock ageing analysis bucket.

Sheet3. In accordance with the stock ageing analysis bucket for Retailer-Specific with Product Model & Color, I want to extract the stock count.

After each unique piece of data as per the sheets, I need to add the grand total at the end of the count.

I'm new to VBA coding and am still learning it. In my office, one of my coworkers who is quite skilled in VBA has refused to assist me. Help me with this data, please. You have my gratitude.

I've included a sample sheet of data with the final results that I personally created and took a lot of effort. With the assistance of this platform's experts, I now wish to use VBA coding to automate some of my repetitious tasks.

@Jindon, Sir Could you please help me on this post?

@VBA Master, Guru, Expert!!

Can you please help me on this challenging post?

There have been 124 views and NO offers of help. This means one of two things:

1. Your request is not clear.
2. You are asking too much.

Looking at your post, it does seem to read rather like a brief rather than a request for help.

Just bear these things in mind.

Thank you for the advice, ma'am. I'll attempt a more thorough explanation. I'm hoping that someone can then assist me with this post.

Not very hard to create each sheet from "Raw Stock Dump", but I don't know how to classify stock into aging analysis.
Let's work with a specific model, "V23 Pro(8+128G)"
With sheet "Raw", I filtered column F and got 20 rows of "MD" and 11 rows of "Market"
For the "Market" I can see date-time stamp in column I , from 27 to 29-Jul
With sheet "Dealer", I found the same list of the "Market", but the Qty was splitted into aging columns
The question is: How to split into each aging column? I could not find any date in "Raw" sheet , accept date-time stamp in column I (just few days age)
Or aging column is given data?

Hello Everyone,

I'm going to explain my post again in the hopes that you will understand.

I am trying to make a stock ageing report on the basis of a raw stock dump. The goal of this analysis is to determine how much inventory from the market and the distributor warehouse is holding up in terms of age. To maintain the stock age, they either adhere to or do not adhere to the FIFO approach (First In First Out). Because there is no price protection available for the quantity of old stock after six months. To do this, the distributor and market must keep the stock's age below 180 days. Retailers and distributors will work on this and remove the outdated goods first from the market and adjust the distributor warehouse as necessary if we give the stock ageing data.

If a company reduces the price of any model, it will only consider the most recent six months' quantity of stock data when calculating the price protection amount(if any model was billed on old price, now reduced the price of that model the different amount will be credited to the retailer and distributor accordingly by the company side.)



The stock ageing ranges is <45 Days, 45 to 90 Days, 90 to 180 Days and >180 Days. The ageing of the stock will be calculated on the latest operation date column which is available in the raw stock dump as per attached sample data. I have prepared a manual report in 3 different sheets. Model wise, MD Wise (distributor Wise) and Dealer Wise stock ageing. All these 3 sheets have some different columns and criteria.

Sheet1. I've added the product model and stock levels (the stock has two levels, the first being market and the second being MD, or market distributor), which are in accordance with raw stock dump and stock ageing bucket as previously described. I need to show the stock count (IMEIs Count) by product, stock level, and stock ageing, together with the total for each product model and, finally, the grand total for all models.


Sheet2. I've included MD Names (Distributors Name), their total quantity of stock grouped by ageing bucket, the total for each MD's product model, and finally the sum of all MD's grand total stock grouped by ageing bucket.

Sheet3. I have included MD names, retailer names, product models with colours in stock, total quantities of stock that are ageing wise, and the sum of all retailer totals.

Originally Posted by bebo021999

The question is: How to split into each aging column? I could not find any date in "Raw" sheet , accept date-time stamp in column I (just few days age)
Or aging column is given data?

First of all thank you so much for responding on my post. Yes, the most latest operation time column has a specific Date-Time format. I added 3 columns, first extract the date, second calculate the age, and then apply the appropriate ageing condition (if formula). According to the sample data, that is how I created the report. I removed the manually calculated columns in dump after creating the report. Because I need to use VBA coding to achieve results dynamically in order to reduce some manual work.

Sorry Bebo! Please find the new raw stock dump for making the stock ageing analysis report. The format will be same as per the sample data sheets.

@bebo021999

You correctly identified the stock's right date in my sample data file and I provided the new original raw stock dump with you in my post. Actually, I manually adjusted the date to track the stock ageing in accordance with the criteria and created the report. However, after doing so, I mistakenly removed the accurate date that I had set in accordance with stock ageing.

I only need the stock count as of the most recent stock dump in those 3 sheets according to the specified format. Please, could you assist me with this?

I am uploading a new small sample file with less rows to understand it much better in compare to previous uploaded file.

I have contained total 20 rows of raw data this time in the raw stock dump sheet. On the basis of this I need to extract stock count as per stock ageing in 3 different worksheets called as Model Wise, MD Wise, and Retailer Wise.

In Model wise I need to extract stock count as per stock ageing based on Model and Stock Level with their subtotal and grand total.

In MD Wise I need to extract stock count as per stock ageing based on MD Name, Model, Color with their subtotal and grand total.

In Retailer wise I need to extract stock count as per stock ageing based on Retailer Name, Product Name, Color with their subtotal and grand total.

I have spent 2 days working on it with no luck. It seems beyond my knowdledge!
Will come back asa having good news.

Try this.
Macro code for "Model Wise-Stock Ageing" sheet.

Please Login or Register  to view this content.

Macro code for "Model Wise-Stock Ageing" sheet.

Thank you for replying with the solution.

I tested it and added a couple of extra lines in the raw stock dump just to make sure. The information is only available for one level, whether it is Market or MD, hence there is only one problem with the records updating. If a level has no stock, the other levels will be updated with 0 instead of blank if the information is not available and is currently shown as blank.

Try

Please Login or Register  to view this content.

KVS, you're the best! You took a very original approach, as this presented a significant challenge to the other VBA experts on this forum. Although my post has received more than 300 views, only two VBA experts have responded.

I tested your most recent code, and it functions flawlessly with the model-specific stock ageing sheet. Would you kindly create two additional sheets for MD- and retailer-specific stock ageing? Please also change the subtotal format to blue for the grand total as headers and bold text for both the subtotal and the grand total.

Try

Please Login or Register  to view this content.

Great Work! KVS, you're the best. The first of the three sheets is successfully extracting the stock as I needed based on ageing automatically.

Result in sheet MD Wise-Stock Ageing is not correct.
"Dancing Waves" is not there in raw sheet.

Ohh..Yes, I made mistake while making this report manually. Sorry for this. Could we also add the Stock Level for MD Wise Stock ageing if possible?

Since all result sheets need to calc the aging. Is it accepted , in sheet "Raw Stock Dump", employ few helper columns:
Column L to O: ageing from 45 - 90 -180 - over 180
Column P: running ageing sum per product with MD/Market
Column Q: running ageing sum per MD with product
Column Q: running ageing sum per Market with product

Then the code should become much more simple

Also, beside Zone 7, are there other zones, i.e, zone 1,2,...,8

Originally Posted by bebo021999

Also, beside Zone 7, are there other zones, i.e, zone 1,2,...,8

Yes, it can be if stock available of other zone.

And, how about the helper columns said in #21, does it allowed?
Like this:

Attachment 790381

Originally Posted by bebo021999

And, how about the helper columns said in #21, does it allowed?
Like this:

Attachment 790381

The attachment didn't open; an invalid link message appears. Also, if you use the helper columns, you can create simple coding. So go ahead and create; it is allowed.

Macro code for MD Wise-Stock Ageing sheet

Please Login or Register  to view this content.

Thank you KVS! Yes, it perfectly works with the MD Wise stock ageing sheet as well. But, I receive the following error when I test it using fresh raw stock data. How to fix it please guide.

Column I is text. Convert it to date using Data--> text to columns.

Originally Posted by kvsrinivasamurthy

Column I is text. Convert it to date using Data--> text to columns.

Yes, it is now working flawlessly. To achieve the results of the MD-wise stock ageing sheet, we have to first sorted the MD names in A to Z format, according to your coding.

Code for sheet Retailer Wise-Stock Ageing.

Please Login or Register  to view this content.

Thank you so much KVS! I have tested the Retailer wise Stock ageing sheet code, and it is working good.

@KVS,

Can we also include the Stock Level for the Zone 7 Grand Total in the MD-wise Stock Ageing Sheet? in order for us to understand how many stocks, classified by age, are available at Market & MD Warehouse. Also could you please repeat the zone name & Product Model in the first two column of Model wise stock ageing report and "All" for Stock level wise grand total ? As I've already marked this as solved, this is the last query I have in this thread.

As per file the shown belongs to sheet Model Wise-Stock Ageing. You are asking Grand Total for MD Wise-Stock Ageing. The shown sheet is already having Grand Total. Clarify. Upload sample file.

Hello KVS! Greetings.

Thank you for correcting me once again. I have updated #32. Please see the sample snap shot for the stock level of grand total.

Code for MD-wise Stock Ageing Sheet

Please Login or Register  to view this content.

Hello, KVS!

Greetings.

I checked your most recent code. The first two column zone name and product model is not repeating and the total is appearing as zero in the ageing sum of all for market and the md, so I guess the code needs to be modified.

Upload file.

Originally Posted by kvsrinivasamurthy

Upload file.

Hello KVS!

Greetings.

I uploaded the sample file with all 3 of your VBA Codes in different modules. I'm having problems with MD wise and retailer wise sheet as well. If I don't sort the data, the subtotal of MDs doesn't get fetched correctly, and if I do sort the data, retailer wise module doesn't run.

On which column data is sorted.

Originally Posted by kvsrinivasamurthy

On which column data is sorted.

I sorted the retailer names first, and then I sorted the MD names from A to Z. The MD wise Stock ageing code then gives the desired outcome. However, the retailer-wise stock ageing is giving me error. I have also included the screen shot of that highlighted yellow line in the attached file in #38.

Does this file file contain original data or the sorted data. If sorted What is field 1 ,field2 ,field 3.

Originally Posted by kvsrinivasamurthy

Does this file file contain original data or the sorted data. If sorted What is field 1 ,field2 ,field 3.

The data is the same as the sample data file that I uploaded in post #11. I've just sorted alphabetically two columns, Col. K (Retailer Name) and Col. B (MD-SMD Name).

Pl see file. All three macro are there.
I have incorporated conversion of text to dates for Column I. Required sorting is also included in Macros.
Don't change anything in your original file. Run the the macros on a copy of your original file.

Thank you so much, KVS! You are super expert. Now, all the macros are running smoothly but the Zone name and product name is not repeating in first sheet of Model wise stock ageing.

Revised code.

Please Login or Register  to view this content.

This is exactly what I needed. Thank you so so much, KVS.