Hello! I have a very specific question regarding excel and im hoping someone can help me out! I want to be able to scan a UPC bar code and then have excel find that UPC number in the sheet and automatically put a 1 in another cell. Is this possible?
Thanks
Well, not without some kind of macro at the least, & its mere inclusion in the file will set off all the usual LOOKOUT! IT'S GOT A GUN!! type alarms. It may even require Visual Basic, I'm not completely sure. You may need to start the process with some kind of button press to signal "Begin search" or the barcode scanner will just enter the scanned value into whatever cell is currently in focus.
Barcode scanners read the bars and convert them according to some encoding standard or other (EAN13, CODE39, etc). Most codes are just alpha-num values, some are functions to be executed, such as changing a setting in the scanner like turning off the audible beep. Usually the code that has been read is followed by an end of line, or hard carriage return ("Enter" key).
So if you were to do something like attach a BRUB (Big Red USB Button) and program it to enter "Ctrl+F" you could easily push the button with the scanner then point & shoot to read the code in and hit Enter for you. You'd still have move to the correct column & enter 1 though.
The sequence of events is:
init search > enter value & submit > move to offset > enter value
I'm sure you can see why that's hard to carry out with a single button press. Maybe there's a way to automate along these lines:
awaiting search is the initial state > scan barcode enters & submits value with a single button push > move to offset (eg: col +1) > enter value 1 > [initial state]
You'd still need some macro or other to do this. Also consider that you may scan the same barcode more than once, so you probably want to increment count +1, not just enter a 1, otherwise scanning 13 of a particular code will only be recorded as 1, maybe this is what you want?
A quick search reveals a few solutions where people just scan a barcode into a cell, such as A1, and then a VLOOKUP or Worksheet_CHange event based macro is performed to retrieve/mod data:
https://www.reddit.com/r/excel/comments/2p1xuf/locating_a_cell_or_item_using_a_bar_code_scanner/
http://www.mrexcel.com/forum/excel-questions/746420-scanning-barcode-have-check-off-excel.html
Take a look at this mrexcel.com thread containing a pre-made Excel spreadsheet setup with macros for inventory purposes, it may suit your needs, credit to the author brucef2112: http://www.mrexcel.com/forum/excel-questions/476078-counting-inventory-barcode-scanner-5.html
Thank you kindly for the In depth reply I will look at the links! This helps a lot!
You could possibly also ghetto rig it and have the scanner enter every barcode, then use a COUNT function or whatever to return how many of each barcode there are.
Simple, but requires more work on the worker's end.
COUNTIF, matching from an array or list of unique values, run against a range containing all the scanned barcodes, yep.
Or just turn on autofiltering on the header row after collecting them all and work out totals from that.
