What items are being counted on my Inventory Count Templates?

Here are the steps to determine if your items are appearing on your Inventory 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 Smart Ops, then selecting 'Items (classic)' in the 'Food Admin' section.

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 an Inventory Count

Navigate to your Inventory Counts and open your 'Full Inventory' Inventory Count Template. 

If there are several Inventory Count Templates that encompass your full inventory, this process will need to be completed for each template to capture all of your items on your various Inventory Count Templates.

Once the Inventory Count Template is open, hover over the menu icon , then select 'Use Template'. This will create a new Inventory Count.

Once the page has loaded, hover over the menu icon 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: VLOOKUP in Excel.

First open the Item export from Step 1. Ignore everything in the file except 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 do not have any data overlapping Column B, as show below:

Next open the Inventory 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 Inventory 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:

=VLOOKUP(A2,B:B,1,FALSE)

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 Inventory 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 Inventory Count Template. It would then need to be added.