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


kmfBe940t4YNzRzFMAFTNWip3GRcTO-hxA.png 


Once the list is loaded click the export button in the top right corner of the window to export the full list of items:

 


rcoAZMu-Mbr00upXkyfWjFMZ_Tp4HoRKVA.png


 


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.

 


FtWttfJQrvmL535KIPXxI1SCGGUsMpMPjw.png 


Once the page has loaded, hover over 'Action' once more and select 'Export Count Sheet'. 


41WEXgORCNmdTrbF1tcrnR1RvDWNxhcUXQ.png 


This will produce a .csv file that contains all of your items and their storage locations (along with other sorting information).

 


mqUTxL7Kk_qIZfAs-F_77YHmX9N3cdnOqQ.png 


 

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 accept for Column A, 'Name'.  The contents of this list are all of your Items:

 


krQ5XnkZ1nN4rbzc7KTwkDMM4-LcBmacgw.png 


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:


QQx1DCjgR2IE1TTrdPeL5-eAZpYU3pTgOw.png 


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'.


jLbDfUGy71xS9qz3b_pxdqqYyjtumFOrOw.png 


Copy the entire contents of Column C to Column B in our new sheet (where we previously pasted Column A with 'Name').


lkxEfdG41Zl5Pf0tEzPxRsEBjlb4ytb2OA.png 


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


dyMOpw42yoOcjn1vhdzkDaP30TS1hGOgKA.png


 


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:


NACj-J3BLMy-tZ7hOrCYc2PySCpdLVlntA.png 


Change the default selection to 'Continue with the current selection' and click 'Remove Duplicates...'


NulOon0NHcSmZycp7W7DJk4dedMvSWekOA.png


 

Column B will be the only selected.  Click 'OK' 


-gLww8LlqgqCH9Fyuj1FRO-rJDc_pve2UA.png 


Excel will then tell you how many duplicate values it found and removed:


UkIMaBSY3wwk8LvgC9GvYUAb0zgXAauAQQ.png


 

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)


0Quh60qzPuzN_1xli4tWEsxZfLMERI8tcw.png 


This function will lookup the value in Cell A2 in Column B.


8otIGCPhFdmAsK6UKuX0yPbbUtI-OUT4FQ.png


 


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).


cOxQBeCN9ouQli5NDviz_3AFdv5ptD2z4A.png 


JCN4b94_tQGOIWCZL5ywa1n7mnI-uj_tJw.png


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!


mt7BoQ3veL6t287VgnhgUhUoZae9-vjqtw.png



 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.


 

Tt_wKosRC8ABUQYPxVRTGb7K3K3NZWHemg.png