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!

 

[RapidFireGT]

Also forgot to add a couple things:

#1: I'm not very informed when it comes to Excel, so the simpler the terms/instructions, the better

#2: In my searches, I did find this post in another thread

*disclaimer: I have a limited (2-3 projects) experience with barcodes, and have only used Symbol so far

Simplest first: formatting -- the barcode reader should allow for some processing, e.g. if your upc is 027434011297, you should be able to program it to send to Excel upon a successful scan:
- a single quote ('), then the value scanned (027434011297) resulting in a label (vs. a numeric value)
- other combinations transforming the upc (e.g. 027-434-0011_297), including conditional logic based on characters present within the scanned data)

As far as how to decide on incrementing - based on the scenario you present, I would probably use a VBA/dialog which would accept the scanned UPC value into a textbox, then hit an accelerator key (tied to the VBA code, e.g., Alt-U for Update_Sheet or whatever you like). This logic would then do your bidding:
- if UPC value exists in list, increment it (or prompt to increment it)
else
- add UPC value to list and start its quantity at 1 (or prompt accordingly)

Again, the scanner can be programmed to send the upc value itself, then pause, then send a keystroke mapped to the accelerator on your form, or Tab/Enter, or whatever you can imagine.

If you want to get fancy and set time thresholds, you could certainly catch the date/time of the scan and make decisions based on the date/time of the last time the same UPC was scanned, recorded on the worksheet.

The key is understanding the scanner capabilities and combining with the the Excel automation you prefer.

Hope this helped.

Credits to jdesi1

 

[doublej_jj]

Okay - So I don't have the time to detail it, but start with this idea.

1. Your scanner populates a temporary colomn with each scan (even if duplicate) on a separate row

Barcode 1
Barcode 1
Barcode 2
Barcode 3
Barcode 3
Barcode 3
etc.

2. Write formulas / code that looks over that list for unique barcodes and place them in your final "Barcode" column.

3. Use lookup to cross-reference your barcode to "Product Name"

4. Use a countif or sumproduct to count the number of times each "Barcode" appears in our temp list to populate "Quantity".

I am a novice compared to the heavy hitters here, but the concept is good. Maybe someone will run with it or I'll look at it more tonight.

Jeff

 

[RapidFireGT]

Okay - So I don't have the time to detail it, but start with this idea.

1. Your scanner populates a temporary colomn with each scan (even if duplicate) on a separate row

Barcode 1
Barcode 1
Barcode 2
Barcode 3
Barcode 3
Barcode 3
etc.

2. Write formulas / code that looks over that list for unique barcodes and place them in your final "Barcode" column.

3. Use lookup to cross-reference your barcode to "Product Name"

4. Use a countif or sumproduct to count the number of times each "Barcode" appears in our temp list to populate "Quantity".

I am a novice compared to the heavy hitters here, but the concept is good. Maybe someone will run with it or I'll look at it more tonight.

Jeff

Hm, I like where you're going with this. It may not be the neatest way of going about it, but it works, and I'm going for functionality over appearance at this point anyways

Unfortunately, I don't know any of these formulas, but I'll do some digging around in Excel and see what I can find.

 

[RapidFireGT]

Alright, after googling a vlookup tutorial, I've got the name referencing set up. Now, I just need to figure out formulas for

• populating the column of unique barcodes
• counting the number of times each barcode is listed

 

[RapidFireGT]

Update: After attempting to create a macro for populating a list of unique barcodes, I ran into a minor error: the filter is populating duplicate entries.

Before running macro:

Macro:

Sub Populate_Unique()
'
' Populate_Unique Macro
' Populate the list of unique items
'
' Keyboard Shortcut: Ctrl+b
'
    Range("A2:A1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "B2:B1000"), Unique:=True
End Sub

After running macro (highlighted errors):

 

[Armando Montes]

Hi, change this:

Range("A2:A1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B2:B1000"), Unique:=True

To this:

Range("A1:A1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B2"), Unique:=True

 

[Misca]

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.

 

[SaladProblems]

Just a thought - If you want to simplify this, couldn't you just scan them in and then use a pivot table for the addition part?

 

[RapidFireGT]

Hi, change this:

Range("A2:A1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B2:B1000"), Unique:=True

To this:

Range("A1:A1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B2"), Unique:=True

Thanks! That cleared up the error.

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.

Thank you for that code , although could you elaborate a bit more on what exactly it does, and how I implement it? As I said, I'm not too informed when it comes to Excel; I'm learning as I go here.

I set up another spreadsheet like the one from my first post (Barcode, Product Names, & Quantity) and I created a macro with the code that you posted. For some reason, after I created the macro with your code, I couldn't find it. I'll keep playing around with it though and try to get it to work. Any additional direction would be great.

Thanks again I'll keep updating with my progress.