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!

 

[Tejaashree]

My application is very similar to this and I am trying to maintain track of how much quantity of the parts are available in the warehouse. My idea is that, when one box for a particular part number has been made, it will be scanned before being taken into the warehouse. And when the same box is taken out of the warehouse, it will be scanned again. I have a sheet which has pre-determined barcodes corresponding to different part numbers and no two barcodes have the same value( even if it is for the same part number but a different box ) - each barcode is unique.

I have populated the part numbers and the quantities once the barcode is scanned. Now, I want to run a code, which will check the entire column of part number and barcode every time a part number is added to the column( i.e, everytime a scan is done ) . If the added part number matches with any cell in the column, but the barcode is different from the rest column, then the number of boxes for that part number will increase by one ( indicating addition of one box into the warehouse ). If the part number and the barcode have matches with cells in their respective columns, then the number of boxes for that part number will decrease by one ( indicating removal of one box from the warehouse ). There will be another sheet ( output sheet ) which has only unique part numbers after the addition or subtraction and the corresponding quantity in the warehouse.

This should give me the total number of boxes for each part number that are present in the warehouse.

Below is the sample of how my excel sheet would look.

Barcode | Part number | Qty/box

Another sheet will have pre generated barcodes corresponding to part numbers and the Qty/box which will be used as reference to fill the above

The output sheet will look like this

Part Number | Number of boxes in WHS | Qty/box | Tot.Qty in WHS

If someone has an idea of how to implement this, please reply!!

Thanks so much..

 

[Yoruzoku]

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

This one works out easy enough and not a messy job and did not have to mess around with macros at all.

I've needed this for stock picking for our warehouse, so that the staff don't have to double check the whole list again after they have completed picking the items. By adding conditional formatting to cross reference the QTY item ordered vs QTY item picked, the sheet can highlight with a green fill when the right amount of item has been scanned through.

 

[jeblin4]

Howdy, sorry to bring up an older thread but I am actually quite excited that I got this far...
I used the original code:

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 = ""
ActiveCell.Offset(-1).Select

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


With the exception of figuring out how to move the cursor back to the original position I'm lost. I'd really like to use this code to do a total inventory, use it again to do a line by line with the date stamp of each entry. Have them all on separate sheets, but read from a single entry on the first sheet, then I can go in, pull the sheets once a week and start over... Is this doable?

Thanks,

Joe

 

[brucef2112]

I'd really like to use this code to do a total inventory, use it again to do a line by line with the date stamp of each entry. Have them all on separate sheets, but read from a single entry on the first sheet, then I can go in, pull the sheets once a week and start over... Is this doable?
Thanks,
Joe

Your first request; "I'd really like to use this code to do a total inventory...",
A:The TakeInventorySample.xlsspreadsheet does exactly this. On to #2.

Your second request;

"....use it again to do a line by line with the date stamp of each entry.....each on a separate sheet."

A: (or maybe more of my question) Do you mean you want on a separate worksheet, every scan to show as a row with a date stamp next to it?
including duplicates? ie if there are 35 of item #12345678 (and you scanned each one of the 35) each will show on its own row with a date stamp?
If this how you want it then you wouldn't really need a sheet for each day of the week. Because the scan will include a date stamp, you could then just use Excel's Auto Filter to show the rows based on the date you choose.

This would be manageable if you you did one spreadsheet for the week and then started over, as you mentioned.

You may also try this as a solution of inserting a new column to the left of column 'C' before you scan for that day. (from Misca's earlier post)

If you want to keep a record of how many of each items have been shipped on each day, add a date to your Quantity header and every day add a new column C (=moving the previous column C one step to the right) and you'll get the shipping history automatically. Just make sure the cell you're scanning the data into is not touching the A1 CurrentRegion and your code should work as it is.

If #2 A is all in the wrong direction, then please give us more details of what your looking for and more details of the end result you are looking for.

 

[jeblin4]

Thank you for the lightning quick response! Sorry, had a rough shift last night or I would have responded quicker. With #1 I love the way it works and have only one request: It currently searches any cell on the page, I just outlined a cell, leave the cursor in it, and added the -1 to move back into that position. Is there a way to change the search settings so I can specify a cell for number entry? (I want to have the first sheet of the workbook be shaded out except for the "Enter Barcode Here" cell, and have the rest of the workbook reference that cell for entry) The next page is the inventory page that the code searches and that I can add items and info to, the third sheet is the current code that creates a total of items scanned (I'm using it to have a weekly total of items that were used so I can reorder). I'd like to have a fourth page record every entry on an individual line, with a time stamp (for tracking and security purposes). I'd like to go in, pull the sheets once a week, clear it and let it keep running. Does this make it any clearer?

Thanks

 

[brucef2112]

...I love the way it works and have only one request: It currently searches any cell on the page, I just outlined a cell, leave the cursor in it, and added the -1 to move back into that position. Is there a way to change the search settings so I can specify a cell for number entry? (I want to have the first sheet of the workbook be shaded out except for the "Enter Barcode Here" cell...

jeblin4 says:'It currently searches any cell on the page,...'
The search should be limited to the barcodes in column A.
Technically, it first searches the 'Inv Taken' page column A for a matching barcode. If it doesn't find a match it then looks on your Inventory sheet column A, to find a match. This method allows for faster performance than always looking at the inventory sheet. If you only have hundreds of barcodes in your inventory data, you wouldn't see the difference. However if there were 50 thousand barcodes it would take a performance hit to always go to the 50k list compared to checking the relative short 'Inv Taken' list.

I'm not sure what you mean by you added -1 to something for choosing an entry cell?!?!? If your 'scan here' cell is not 'F1' cell (because you may have inserted some columns or something, then this will break the original code and the entry cell will have moved but the code still expects the scan to be in 'F1' cell. However I modified this new version. You CAN insert columns in between column C and F for your own use and the 'Scan Here' cell will of course move further right and it will still work as it should. It is no longer tied to the explicit address of 'F1'. I made the entry cell a Named Range so it could technically be in any column after column D (first row only just becuase of the 'freeze pane' feature that allows the scrolling of the inventory data to the currently scanned item.

You requested to move the 'Scan Here' to its own sheet. In order for all to enjoy, I modified the code to allow you to 'hide' columns A thru D (select the columns, right click, select hide). This will effectively show only the 'Scan Here' cell visible on the first sheet. Hiding columns was not possible on my original version without breaking it. So this will meet your needs and still allow 10's of others to continue using it as they have.

Just my personal preference, but while testing this with only the scan cell visible, it seems a bit unnerving to be scanning barcodes and to not really see any feed back from the program. ie I like to see something happen on screen when i do some data entry. The original method of having the columns visible gave me a warm and fuzzy feeling that what I did was in fact doing 'data entry' and it would allow me to see the last item I scanned. I'm just funny that way.

..The next page is the inventory page that the code searches and that I can add items and info to,...

This is the same as the original, so nothing to change. I like requests that involve me doing nothing. They are my favorite! *Bruce likes doing nothing*
You can add columns as needed for your use but the barcode and description must remain in columns A and B.

..the third sheet is the current code that creates a total of items scanned (I'm using it to have a weekly total of items that were used so I can reorder)....

This is basicly the original first data entry sheet. I'm gonna do more of nothing on this one. The best would be to do the scans and once completed you could (unHide columns A thru D and) copy paste the inventory summary into another sheet. It can be in the same workbook without affecting the program. This is in the spirit of keeping things as flexible for all the masses.

...I'd like to have a fourth page record every entry on an individual line, with a time stamp (for tracking and security purposes). ..

This is now a new feature to the program. As you scan, it now will make an entry on the last sheet each time a barcode is scanned. So if you scan barcode '12345600' 10 different times, it will show on this sheet with each scan on a separate line. It also shows a date and time stamp (date and time are in separate columns). Although the info may be generally useful for tracking, it really has no security as anyone could manually change the data after the fact. So be warned but still enjoy the added feature.
This is in addition to the summary of item counts on the 'Inv Taken' sheet (with unique barcodes) as before.
NOTE: the new sheet with all scans will be limited to the max rows of Excel (65,556 for older ver of excel). So if you pull the trigger on that many barcodes you'll have to regroup with some plan for it. I don't think you'll reach this limit, but I just mention it as a limit of Excel. Not to be confused with 'Inv Taken' sheet which will keep count of each unique item and therefore a relatively short list.

The original version did not allow you to change the names of the worksheet without breaking the program. I have changed the code to allow you to rename the 3 worksheets if you want.
There are some other minor changes but nobody would be interested in.

I changed the name of the spreadsheet so I won't get confused with the old vs new.
So here is the new version: TakeInventoryWithRunningScanSheet.xls

 

[mturner262]

Can anyone tell me what version of excel i need to use this spreadsheet? I have 2008 and it doesn't seem to work. I would like to go ahead and upgrade to 2016 if it is compatible. Please let me know as this would cut my inventory counting time drastically. Thanks!

 

[brucef2112]

Can anyone tell me what version of excel i need to use this spreadsheet? I have 2008 and it doesn't seem to work. I would like to go ahead and upgrade to 2016 if it is compatible. Please let me know as this would cut my inventory counting time drastically. Thanks!

This was created with Excel 2003. So your 2008 should be fine. In my opinion, when it comes to M$ products, newer is rarely better.
There was an earlier version. don't know what you DL but here is the newer version: TakeInventoryWithRunningScanSheet.xls

It would be helpful if you can be more descriptive about what problem you are having running the program.

 

[mturner262]

Hi thanks for the response. From what I understand, 2008 is not compatible with macros. After more reading I believe 2011 is but not totally sure. I've read some things about 2016 and from what I understand, it may not be compatible either.

 

[brucef2112]

Hi thanks for the response. From what I understand, 2008 is not compatible with macros. After more reading I believe 2011 is but not totally sure. I've read some things about 2016 and from what I understand, it may not be compatible either.

When you open any xls (97-2003) workbook in Excel 2007+, it is opened in compatibility mode. You must save it in one of the new formats to work with the workbook outside of compatibility mode.

In 2007, there are three workbook types, as opposed to one workbook type in 2003 and earlier. One of the new formats in 2007 is xlsx, which does not allow any VBA code to be stored in the workbook.
If you save a file as an xlsx, Excel should warn you that the code will be deleted and give you a chance to cancel the operation.

However you can save to one of two other file formats that will allow macros.
.XLSM or .XLSB are the Excel 2007 format that allow you to save VBA code with the workbook.
he differences between these two is the way the workbook is stored.
In short: .XLSB is the binary format (equivalent to .XLS for 2007+ version) whereas .XLSM is the OOXML format.

I suggest you use the .XLSB
Open the .xls file then save as .xlsb. It will preserve the macros.