Print Friendly
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

 

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: 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:

 

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

Was this article helpful to you?

Comments are closed.