Stock inventory

The inventory of goods is a boring but necessary activity that must be carried out, at least once a year, by all those who deal with sales. I have written before how to make an inventory of fixed assets. The inventory of goods is done in a similar way, but this time I will show how this is achieved by applying the technique of data consolidation. Excel has had this option for at least 10 years, so I believe it will be useful for each user.

At the beginning, we have three worksheets with the names of the workers who conducted the inventory (Petar, Marko, Ana) and our goal is to unite them into a single inventory list. In each of the lists, the names of items (wines), the quantities of which we record in rows, may differ. Also, each inventory table can have more or less columns denoting a warehouse (MAGACIN) or retail facilities (P1-P4). In the image below, for simplicity, I have shown the initial tables next to each other even though they are in three separate worksheets.

In order to consolidate the inventory lists, we will perform consolidation according to data categories. To do this, you need to create a new worksheet, enter the Data ribbon, and then select the Consolidate option, which will open the window of the same name.

The Consolidate option first offers us to select the function that will be used for data aggregation. Let it remain the SUM function. Then, in the References field, we add the ranges we want to consolidate by selecting them and clicking Add. The entire inventory table should be selected, along with the headers. If we have added a range by mistake, we can select it in the list and click Delete. The addition process is repeated until we have added all the tables.

Then select the Top Row and Left Column check boxes, and further click OK. As a result, you get a consolidated inventory list.