Counting Inventory with Barcode Scanner

[RapidFireGT]

Windows 7
Excel 2007
Wasp WWS800 Wireless Barcode Scanner

First off, I'm not even sure if this is possible.

I've searched through 12 pages of "barcode" results and didn't find much, so I'll ask in this thread. Now, what I am about to ask it most likely too heavy for a complete answer, so even if you can just point me in the right direction, I'd appreciate it greatly.

What I will be doing
We are a wholesaler distributor and send out 2-4 pallets every day on each route, and we have about 5 routes everyday. At the end of each day before pallets are loaded onto the trucks, we count the product on the pallets to make sure the orders have been filled accurately. We would like to use a barcode scanner to scan each product and then populate a list of what products and what quantities are on the pallets.

What I would like Excel to do
I would like to set up a template with 3 columns: barcode, product name, and quantity. I would like to scan an item, the barcode scanner then inputs the barcode number into the "Barcode" column, Excel then cross references this number to match it with its corresponding product name which would then be inserted into the "Product Name" column, and then Excel would increase the quantity by 1 in the "Quantity" column.

Basically, I want insert the product number into excel, which then matches it with a product name and starts counting the number of times I scan that particular item.

Here is a sample spreadsheet of what I imagine such spreadsheet would appear as:

Again, I'm not even sure if this is possible in Excel, and I assume it is too much work for you guys just to spit out the formulas to me to accomplish this. With that said, if you can even point me in the right direction to achieving this, I would appreciate it.

Thanks guys!

 

[brucef2112]

The program performs exactly what you are looking for.
Your experience of the previous scan going away with the current one may be a misunderstanding of the user interface.
When you scan a barcode to add, the spreadsheet will move to the line with that barcode (scrolling up/down to current line.) and add to the inventory column.
So your previous scan is accounted for but the sheet is scrolled to show the current scanned item at the top row.
Double check to see if this is what you're seeing. otherwise check back here with questions.

<Sorry for dragging up an old post but ...>
This is the type of post which doesn't get old. A lot of reads and downloads with a question now and then.
b

 

[bolsson95]

Hey Bruce,

Thank you for all the work you've done on this spreadsheet. I am able to use this now to conduct inventory counts for my medical cannabis dispensary, however, there are a few tweaks I was wondering if you could help me make?

There are 4 pieces of information I need when conducting inventory audits - A barcode (readable by our POS system), A package ID (state issued uniquely identifying number), variation (either each based or weight based), and product name.

Variation is either each (ex. 1 pack of gummies) or weight (ex. 3 grams of actual marijuana)

The piece of information I'd like to focus on and display in the report is the Package ID and its' quantity, not necessarily the barcode quantity. Some Package ID have multiple barcodes (for example we may get 400 total grams of cannabis tied to one package ID, but one barcode is for the 3.7g packages, one barcode is for the 7.3 g packages, so on..

If I scan a barcode for a 3.7 gram variation and then a different barcode with a 7.3 gram variation but same package ID as before , I'd like the quantity to display 11 total grams for that package ID. Hopefully I have not lost you.
Basically, I'd like to increase the Quantity Value of a Package ID by the numeric value of the variation of each barcode, per scan. Essentially rebuilding package ID quantity.


Best Regards,

Ben

P.S. the price field is not applicable in my case and would be great if you could remove that and also the prompt to add price for an unknown item.

 

[bolsson95]

Additional Info:

Here is an example of sample reference data i can pull from my POS

If I scan Barcode #131 (10) times and then barcode #515 (6) times, I'd like the result to display as

Package Id	Product Name	Quantity on Hand
A	Silly Sunshine Flower	33.2

Thanks,

Ben

 

[shina67]

This is the type of post which doesn't get old. A lot of reads and downloads with a question now and then.
b

Bravo to Brucef2112 for this post.

This post came up on a recent search I did for this sort of workbook. It made an excellent starting point for what I want to achieve.

I have added a goods in, goods out and balance sheet to the workbook so I have full visibility of the movement of stock.

I have removed the price userform on entry of a new product as its not relevant to my requirements. I have also added in a piece of vba that goes straight to the scan barcode cell so that no other info can be entered onto a sheet.

What I have had problems with is trying to give the user the facility to enter more than the automatic 1 entry per item which would only be required on the goods in sheet. We can receipt in anything from a single item to 100's in a single transaction. At the moment the user is having to scan an item potentially 100+ times to make the quantities correct.

Any help on this sticking point would be appreciated. Thanks in advance.

 

[brucef2112]

Im not able to check right now. but if i remember correctly, after scanning bc it shows the row with its data. So if you have 100 widgets, you can then click in the cell and enter in the number to add to the total.
Of course if you only have 3-4 widgets its just quicker to scan the bc multi times.

 

[shadowpatriot]

I don't want to beat a dead horse here with "can you make it do x,y,z", but I do not know enough about the programming VBA side of things and I was hoping a quick easy edit would give me exactly what I am looking for...

Great job btw putting this together. It functions beautifully. The only thing that I need is, another column for serial number, with an added prompt to key in said serial number after the new item is scanned in, much like it already does for price, etc. We sell products that are tracked by their serial numbers for warranty purposes, and rather than just having a description and quantity, adding a serial number column would give us everything that we need in an easy to use package.

Only other question I have is, is there a way to generate a barcode for adding +1 and a barcode for subtracting +1, for real-time tracking of existing inventory... For instance, if it is going INTO stock, scan a barcode that tells it to +1, then scan the item. If using something from stock, scan a different barcode that tells it to subtract from stock. Easy in, easy out, real-time quantity tracking. Any ideas?

 

[shina67]

if you have 100 widgets, you can then click in the cell and enter in the number to add to the total.

That would be a quick easy answer. Unfortunately due to user error I have protected the sheets and the only cell available to edit by the user is the cell where the Barcode is scanned in. The relevant sheets automatically go straight to the cell to scan in.