Printable View
So im trying to sort data from a barcode scanner. It inputs into excel as numbers, just like a keyboard would. Right now, all values come into excel in the input column I created. What Im trying to do is sort each value in its own column so I can easily count how many times that value has been inputted. This way, all values of 1012011 will fall into the 101 column and so on. Any ideas on how to do this. I will appreciate any help or suggestions. Thanks
I think my head just exploded...
Good luck with this, wish I could help. You could try searching for an answer by clicking on the help button and getting 500 results that have absolutely nothing to do with what you're looking for. [Mad]
Haha yeah I tried that. Right now im going through each formula description to see if one will work. Basically, the input column will continuously change and the number columns need to be automatically updated as more data in inputted. Now I wish i paid more attention in school when I learned this program
How does the data get from the barcode scanner to excel? Do you import into excel or does the scanner output an XL file?
Do some research on vlookup. Or you could do a simple pivot table.
Tim K, it does it automagically. the scanner in synced to a base via bluetooth. the base is then connected to my pc via a serial to USB connection. As long as i select cell A1, when a barcode is scanned, the numerical value appears in that cell. It will then continue importing down that column each time a barcode is scanned. It enters the value just like a keyboard would. I dont have to do anything for data to be imported.
Thanks jsc, ill try vlookup. Sounds like it might work
Roughly how many entries are there and how many different bar codes?
I don't know Excel well enough to tell you how to complete the task using that tool.
Are all of the numbers you are scanning in of a fixed size, ie all 7 digits, all the time every time?
H.
We will have 40 some barcodes all in the format 1xx2011, where xx ranges from 01 to 40 or so. So yes, all barcodes are in the same format and 7 digits. Im on the right path with vlookup but still learning how to use it
I can think of a way to do it manually with sorting but if you have a lot of entires it might take a while.
Sweet, I figured it out. CountIF is the formula I wanted. Thanks for the help Everyone
=countif(a1..a22,"101"*)
will give you the number of times 101xxxxx appears in the cell range a1 to a22
you might have to play with the " and decide if that's a number or text field
you might not need the " at all.
but that's what you are looking for. there's a good help on countif in excel. (and yes, used to use this a lot for doing exactly what you say)
shit
beat by one minute
Have to write a script to sort and count. Not one of their built-in functions will do this that I know of.
Edit: There you go. A function I wasn't aware of.
No more help needed. Everything is working and this is pretty awesome. Thanks Everyone. If anyone is curious, here is the formula I set up: =COUNTIF(A2:A1000,10x2011) Where x is 1 for the 101 column, 2 for the 102 column.. etc