How to automatically reconcile an inventory scan
March 18, 2019 9:48 AM   Subscribe

I am looking for a program (not web-based) that will let me upload a .csv with different SKUs and expected quantities. Then I want to be able to scan barcodes and have the match tracked against the uploaded data. After the scan, I want to see a list of everything not scanned, including if I am missing 1 of 5 expected items. I don't know what to start searching for. Or if this is something I can do with Excel, I'd like to know how to do that, too.
posted by apex_ to Computers & Internet (2 answers total)
I have an ancient custom program that functions on top of an Access database that I used to help a retail company do this with. I'd happily share it with you if you'd like to give it a spin. Shoot me a message and I can hook you up with it and explain how it goes if you want.
posted by msbutah at 10:38 AM on March 18 [2 favorites]

Here's an outline of kind of a brute-force way: open the csv and save it as an xls. This will give you a "Should Have" tab. Now scan everything in inventory into another tab, in one column. Create a pivot table out of that column to give you a two column table where one column is the SKU and one column is the "Count Of" those SKUs. This is your "Do Have" tab of actual inventory. For a data format perspective it should look like the Should Have tab. With the Should Have tab having SKUs in Col A and Expected Quantities in Col B; put a VLookup in Col C that finds the SKU from Should Have in Do Have and puts the quantity found back in Col C of Should Have. Then put a formula in Col D that subtracts Col C from Col B. This will make Col D your Discrepancy Quantity. Then you can just sort the whole mess on Col D to see which SKUs are off.
posted by achrise at 12:31 PM on March 18 [3 favorites]

« Older Films by French filmmakers about America?   |   Where to live in NYC if I’m remote? Newer »

You are not logged in, either login or create an account to post comments