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
Open the Inventory app.
Expand Items.
Select Items.
-OR-
Select Items (classic).Click the Excel export icon.
This will produce a .csv file with all of your items.
Step 2: Export a Count Sheet from an Inventory Count
Open the Inventory app.
Expand Inventory.
Select Inventory counts.
Navigate to the Templates tab.
Open an inventory count template that is a ‘full inventory.’
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.
Click the menu icon.
Select Use Template to create a new inventory count.
From the inventory count, click the menu icon.
From the dropdown, select Export Count Sheet.
This will produce a .csv file that contains all items and their storage locations (along with other sorting information).
Step 3: Prepare the exported Data for comparison
Now, use an excel function called VLOOKUP (or Vertical Lookup). To learn more about VLOOKUPs, you can visit this link: VLOOKUP in Excel.
Click steps to expand for additional information and images.
1) 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 the items:
2) Copy the list from Column A.
3) 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.
Another option is to delete all other columns from the exported file. Either option will work. Space out the column so you do not have any data overlapping Column B, as show below:
4) 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.
Only the values in Column C, Item, are needed.
5) Copy the entire contents of Column C to Column B in our new sheet (where previously pasted Column A with 'Name').
6) Remove 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, manually highlight all the data using the mouse.
7) With the data in Column B selected, navigate to the Data tab on the Excel ribbon and click the 'Remove Duplicates' button:
8) Change the default selection to 'Continue with the current selection' and click 'Remove Duplicates...'
9) Column B will be the only selected. Click 'OK.'
10) 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.
