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!

 

[hfzshahid]

Sir this inventory code works for me, i am working on an automated grocery store and have conveyors for different products.
Now i need to interface my Macro program with arduino, for example if i have barcode "123" for pepsi and "456" for coke than i want a specific byte send to arduino via my macro whenever pepsi or coke are selected, so that i can turn on an led on the conveyor and let the employee know which conveyor belongs to pepsi and which one belongs to coke, any help in this regard would be appreciated.
Can you please alter this code accordingly?

 

[hfzshahid]

here's a link to the code of interfacing arduino with VBA
Arduino serial connection to Excel VBA, no third party software needed

 

[Asevens]

Wow this is an amazing bit of work. I wonder however if this can be adapted to a few changes?

1. Having a running count inventory (ie updated every time stock # is changed)
2. Having a Inv Rec page
3. Having a way to identify User who is scanning in the Time Stamp page (ie have a user scan a badge prior to item)


If these things were able to be incorporated this would make an AWESOME consumables inventory sheet.

Any ideas? (My VBA skills are seriously lacking and hence why i am on this thread)

 

[markeroakparker]

I've been using this to count inventory for my store and I love it. I've adapted it to work with our Barcodes and inventory, but I'm wondering if someone can help me tweak it further.

Our POS system produces a spreadsheet which lists what our system believes we have in our inventory. There are some specific columns in the spreadsheet they send us which list the quantity the system believes we have, a column for entering the counted quantity and a -/+ column showing overages/shortages. There are also columns that show the variant codes associated with that specific barcode. We count the items and then plug these count numbers into a column on the spreadsheet.

I have set up a spreadsheet (using the tips and macros on this thread) which totals everything we scan. We've then been manually keying in the totals from the spreadsheet of the scan to the original POS spreadsheet that is then sent back to our accounting dept to update our POS system.

What I'd like, if it is possible, is to add these other columns that our system is requiring so that I can simply email the results with all of the systems required columns and skip the manual entry.

Can anyone assist me with this?

 

[brucef2112]

.....Our POS system produces a spreadsheet which lists what our system believes we have in our inventory. There are some specific columns in the spreadsheet they send us which list the quantity the system believes we have, a column for entering the counted quantity and a -/+ column showing overages/shortages........ We count the items and then plug these count numbers into a column on the spreadsheet.
I have set up a spreadsheet (using the tips and macros on this thread) which totals everything we scan. We've then been manually keying in the totals from the spreadsheet of the scan to the original POS spreadsheet that is then sent back to our accounting dept......
What I'd like, if it is possible, is to add these other columns that our system is requiring so that I can simply email the results with all of the systems required columns and skip the manual entry......

Here is how I would do it without modifying my TakeInventory tool.
The TakeInventory spreadsheet is a tool for doing inventory. Leave it as it is.
Do not copy the columns from the original POS spreadsheet into the TakeInventory spreadsheet and plan on sending this back to accounting.
They will not know what all the extra stuff is. They will reject your report and in turn this will make your phone ring.
(Remember, accountants have a joyless dead-end job. And rejecting your report will give them the illusion of power)

Continue to use the POS sSheet as you have it, but instead of manually entering the numbers from the TakeInventory sSheet use the VLookup function in the POS sSheet to fill in the CountedQuantity (CQ) column.
This will allow you to automatically have the POS sSheet lookup the number in the TakeInventory sSheet and show it in the CQ column.
Next step (is very important to 'break the link' between the 2 sSheets) is to select the CQ column and do Clt+C to Copy the column. With the column still selected Edit > Paste Special... and under the Paste choices select Values.
This will place the hard numbers in the POS sSheet and 'break the link' between the two spreadsheets. So now you can just send them their POS sSheet.

Here's a sample to look at. it has 2 sSheets representing the POS sSheet and the TakeInventory sSheet.
pahandmahjongg.com/dump/VlookupSample.zip

The references to the column addresses and the column number would obviously need to change for your real sSheets names and ranges.
I have Excel 2002 to work with. if you have something newer I think there are new functions added to Excel that will allow the expression to be shorter in length . IDK.

Bruce

 

[rhighsmith]

Here's my take for the macro:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Item As String
Dim SearchRange As Range
Dim rFound As Range

'Don't run the macro if:
'Target is not a single cell:
If Target.Cells.Count > 1 Then Exit Sub
'or Target belongs to the A1.CurrentRegion:
If Not Intersect(Target, Range("A1").CurrentRegion) Is Nothing Then Exit Sub

'Avoid the endless loop:
Application.EnableEvents = False

'Looks for matches from the here first:
Set SearchRange = Range("A1:A" & Range("A1").CurrentRegion.Rows.Count)
    
Item = Target.Value

'Clears the Target:
Target.Value = ""

If Application.WorksheetFunction.CountIf(SearchRange, Item) > 0 Then
'There's a match already:
    Set rFound = Columns(1).Find(What:=Item, After:=Cells(1, 1) _
            , LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows _
            , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'Adds one to the Quantity:
        rFound.Offset(0, 2).Value = rFound.Offset(0, 2).Value + 1

Else

'Writes the value for the Barcode-list:
Range("A" & SearchRange.Rows.Count + 1).Value = Item

'Looks for the match from sheet "Inventory" column A
    With Sheets("Inventory")
        Set rFound = .Columns(1).Find(What:=Item, After:=.Cells(1, 1) _
                , LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows _
                , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
        On Error GoTo 0
    
            If Not rFound Is Nothing Then
'Writes the Product Name and puts 1 to the Quantity column:
                Range("B" & SearchRange.Rows.Count + 1).Value = rFound.Offset(0, 1).Value
                Range("C" & SearchRange.Rows.Count + 1).Value = 1
            End If
    End With
End If

'Enable the Events again:
Application.EnableEvents = True

End Sub

Place it in the Worksheet module of the sheet you want to be working on. Use the same sheet layout as you have on your first post (Range("A1:C1").value=Array("Barcode","Product Name","Quantity"). Make sure you have the Headers on the sheet before you place the macro to the module.

Also, the macro looks for matches from Column A on the sheet named "Inventory".

With very little editing you could use the same macro on the Inventory sheet as well to use the sheet not only as a Product Name list but as a real inventory as well: All you need to do is make the macro take one from the inventory every time you scan a product on the Shipping sheet + add a product every time you scan the barcode on the Inventory sheet.

Could it be possible to scan a barcode and it bring up a prompt that ask what the new quantity is and then I would enter the new number and hit enter and it update the quantity. So instead of scanning 2 seperate barcodes for in and out just having one barcode and entering my new count when prompt comes up.

 

[Misca]

Wouldn't it be better if you just activated the Inventory sheet and selected the quantity cell right after adding one to the scanned quantity? Just add "go back to scanning sheet / cell" macro on the inventory sheet on that sheet's Selection Change -event macro and you can either change the number on selected cell or just hit enter to get back to your original sheet.

 

[ariez]

Here is how I would do it without modifying my TakeInventory tool.
The TakeInventory spreadsheet is a tool for doing inventory. Leave it as it is.
Do not copy the columns from the original POS spreadsheet into the TakeInventory spreadsheet and plan on sending this back to accounting.
They will not know what all the extra stuff is. They will reject your report and in turn this will make your phone ring.
(Remember, accountants have a joyless dead-end job. And rejecting your report will give them the illusion of power)

Continue to use the POS sSheet as you have it, but instead of manually entering the numbers from the TakeInventory sSheet use the VLookup function in the POS sSheet to fill in the CountedQuantity (CQ) column.
This will allow you to automatically have the POS sSheet lookup the number in the TakeInventory sSheet and show it in the CQ column.
Next step (is very important to 'break the link' between the 2 sSheets) is to select the CQ column and do Clt+C to Copy the column. With the column still selected Edit > Paste Special... and under the Paste choices select Values.
This will place the hard numbers in the POS sSheet and 'break the link' between the two spreadsheets. So now you can just send them their POS sSheet.

Here's a sample to look at. it has 2 sSheets representing the POS sSheet and the TakeInventory sSheet.
pahandmahjongg.com/dump/VlookupSample.zip

The references to the column addresses and the column number would obviously need to change for your real sSheets names and ranges.
I have Excel 2002 to work with. if you have something newer I think there are new functions added to Excel that will allow the expression to be shorter in length . IDK.

Bruce

Hello Mr Brucef2112

i am from indonesia and need formula that can help counting my inventory , but not with serial number .. my inventory description is GIFT units .. so they only using BATCH as a identification .

Example

Article	Description	Batch	Barcode	Inventory Quantity	Scanner Count
BAG	NOTEBOOK BAG	201710-O1	BAG*201710-O1	5
CELLPHONE	SMARTPHONE	210509-K1	CELLPHONE*210509-K1	8
EARPHONE	WIRELESS HEADSET	201607-K1	EARPHONE*201607-K1	10
BATTERY	POWERBANK	201704-O1	BATTERY*201704-O1	6

<tbody>
</tbody>

So my barcode SCANNER will read example BAG*201710-O1 and looking for total 5 Units of Inventory Quantity .

I think you already know what i mean to . and really hope you can help me to get excel formula or else that can make easier for me taking count inventory

thanks before for your help

Warm Regards
Ariez - indonesia

 

[jagmeister]

this is a very good post

 

[scooper5569]

Hi brucef2112
I've just found this post via google and the spreadsheet is something i'm looking to use to create a inventory report.
I would like to scan multiple bar codes into excel and at the end of the scan end up with a list of codes scanned and the qty of each.
At the moment the sheet scans each bar code in row 4 and counts them but when I scan a different code it clears the first one.
Is it possible to add the next item on the next blank row? And is this something that I could change in the code.
Sorry for dragging up an old post but it looks like it could do the job I would like it to.
thanks
Shaun