Inventory

Inventory is necessary but always dull (whatever kind of inventory you do). Since some time ago and I was a member of the Inventory Commission, this experience has served as an inspiration to write an article about a good way of using collaboration tools in Excel (Shared tables) to facilitate inventory process. This is also a good way to use Windows tablets that your company have bought. Most of the time they are only used for show off, and now they could have a real purpose. 

For a very long time Excel possesses an option that is seldom used. Its aim is to merge data from more workbooks. I have to admit, if I did not have to take the exam MOS Excel Expert Part Two (77-428), it is likely I would not know it. Thus, I learned something new and now I can share it with you! The option I mentioned is not activated after installation, you must manually add it. The best place is Quick Access Toolbar. Right click on a File tab and choose option Customize Quick Access Toolbar. Dialog box opens, whose central part consists of two lists. Left list presents the options available in Excel, and the right ones that are currently part of the Quick Access Toolbar. Change the value of a Choose Commands From combo to All Commands and left list will be populated with all available Excel options. Amongst them find one that is called Compare and Merge Workbooks and add it to the right list by clicking Add button. A new option, represented with green circle, will appear in Quick Access Toolbar.

Our goal is to perform an inventory of items that are distributed through tree offices. First, we’ll create an inventory list. This is a table with 6 columns, and its header values are: S/N, INVENTORY NUMBER, ITEM NAME, OFFICE 1, OFFICE 2 and OFFICE 3. We’ll save the worksheet as „InventoryList.xlsx“. Then we’ll share the document by going to Review ribbon and choosing an option Share Workbook. After the dialog box appears select a check box that allows sharing of a document and click OK button to share it. Further it is necessary to save three copies of the same document (O1.xlsx, O2.xlsx and O3.xlsx) and distribute them to inventory participants. Each one of them can copy a file to a tablet, or maybe a file repository like OneDrive.

Inventory participants visit offices and count the items included in the Inventory list. Each one of them fills one column: OFFICE1, OFFICE2 or OFFICE3.  After the inventory is completed they save the document and store it in a folder with InventoryList.xlsx. Further Inventory manager opens this file and runs option Compare and Merge Workbooks. A dialog box appears in which he should choose O1.xlsx, O2.xlsx and O3.xlsx (the procedure is performed separately for each file). After he starts the merging procedure values from these files should appear in apropriate columns. After the changes are being added it is only necessary to perform procedure for accepting or rejecting changes, just like in any shared document.

As I previously mentioned all the documents could be saved on OneDrive. Also we could had modified Inventory list. Maybe, include a column with barcode, and then perform inventory with barcode scanner. Barcode is (usually) a sequence of 13 digits, and connected to PC computer or a tablet device it behaves like a keyboard, so there are no technical issues to use them with Excel. I hope that Inventories will, further on, be less boring!