Dynamic sum of each inventory product divided into lots

vladimiratanasiu

Active Member
Joined
Dec 17, 2010
Messages
348
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hello!

I poste this message again, as I didn’t receive any answer to the previous one since the last week.


On my job, I have to make daily inventories of the raw materials / manufactured products from our company. They are carried out based on some inventory lists, generated automatically by the ERP application. Taking into account that the stocks include products acquired / manufactured at different dates, they appear in reports divided in specific lots, as it’s shown into the linked document https://www.dropbox.com/s/nkc77mal4u9lwxe/Stocks%20on%2029.10.2021-%20logistic.xlsx?dl=0. Giving the large amount of products with their subsequent lots, the lists are very large and vary considerably every day. I meet problems when one / more product(s) and is / are repeated in the list, and the whole quantity is divided depending on the lots (e.g. articles PF_00040, PF_00050, PF_00067, PF_00098 etc.). I ask you if is any way to reduce the iterations of a product name and its article code only to single line, summarizing at the same time the quantities of all lots in a final value, as it’s illustrated in the red text. At the same time, I mention that the content from the present list is only indicative. Its content is permanently changed by including other new products or removing some existing ones, with their corresponding values. In this sense I wish, if it’s possible, the solution be working also with some new elements, no matter how many iterations could have a product in terms of codes and names with quantities.

Thank you!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Looking at your raw data and desired output, I recommend you use a pivot table. This is standard functionality in Excel and you can read about it here.

Also, if the number of rows of data is changing each day, then I recommend you hold the raw data in a table, and you build the pivot table on the table. That way the range will auto update itself each day you paste in the new data. You can read about Excel tables here.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top