Knowledge Base: What Items Are Being Counted on My Stock Counts?
Here are the steps to determine if your Items are appearing on your Stock Count Templates (or rather how to determine if you are counting all of your inventory)
Step 1: Export your Items list
This can be accomplished by navigating to your Items list in the Inventory Section of the Operations module
Once the list is loaded click the export button in the top right corner of the window to export the full list of items:
This will produce a .csv file with all of your items.
Step 2: Export a count sheet from a Stock Count
Navigate to your stock counts and open your Full Inventory Stock count (if you have several stock count templates that encompass your full inventory, you’ll need to do this process for each template to capture all of your items on your various stock count templates)
Once the Stock Count Template is open, hover over the ‘Action’ menu and select ‘Use’. This will open a Stock Count in the current tab.
Once the page has loaded, hover over ‘Action’ once more and select ‘Export Count Sheet’.
This will produce a .csv file that contains all of your items and their storage locations (along with other sorting information).
Step 3: Prepare the exported Data for comparison
We will now use an excel function called VLOOKUP (or Vertical Lookup). To learn more about VLOOKUPs you can visit this link: https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1?ui=en-US&rs=en-US&ad=US&fromAR=1
First open the Item export from Step 1. Ignore everything in the file accept for Column A, ‘Name’. The contents of this list are all of your Items:
Create a new excel file (or open a new tab in the current items list) and paste the entire contents of Column A in Column A of the new file (conversely you could delete all other columns from the current file, either option works). Space out the column so you don’t have any data overlapping Column B as show below:
Next open the Stock Count export .csv file. Similar to the Item export, we will ignore most of the data on this file. Ignore Columns A – B, and D – F. We are only interested in the values in Column C, ‘Item’.
Copy the entire contents of Column C to Column B in our new sheet (where we previously pasted Column A with ‘Name’).
The last step in data preparation is removing the duplicates (if any) from your Stock Count list. Click on the header of Column B and type the following (holding each key until all three are pressed):
Ctrl + Shift + Down Arrow
This will select all data in the column. Conversely you can manually highlight all the data with your mouse.
With the data in Column B selected, navigate to the Data tab on the Excel ribbon and click the ‘Remove Duplicates’ button:
Change the default selection to ‘Continue with the current selection’ and click ‘Remove Duplicates…’
Column B will be the only selected. Click ‘OK’
Excel will then tell you how many duplicate values it found and removed:
The data is now ready to be compared
Step 4: Compare the Data using VLOOKUP function in Excel
In cell C2 type the following:
This function will lookup the value in Cell A2 in Column B.
To copy the function down to all values, highlight the cell of the function and then double click the bottom right corner of the function (on the small dot).
If the Item Name is found, it will return the Item Name value in Column C. This means that the item is currently on your Stock Count Template!
If the Item Name is NOT found, it will return a ‘#N/A’ value in Column C. This means that the item is NOT currently on your stock count template. It would then need to be added.