Stock count using barcode scanner

Hi, I have been trying to work out how to use excel and my barcode scanner to create a CSV file I can use to import into our EPOS system. I need to end up with column A being the barcode of a product and column B being the number of that product we have.

The tricky bit is that I want to be able to do this by just scanning our products one after the other and not having to type in the quantity of the products manually. I was thinking I would make column A of Sheet 1 a list of all the bar codes as they come straight out of the scanner. The same code will be repeated in the list each time I scan another one of the same product.

My issue is then what formula I need to use to count how many times the same barcode appears in the list and transpose that into Sheet 2 as barcode and quantity I can save as a CSV file for the EPOS system.

I thought I’d use COUNTIF but we have over 6000 barcodes and it looks to me like I would have to specify each one in its own row to use COUNTIF and would end up with a lot of zero counts in Sheet 2

Is there another formula I should be looking at? When I set out to do this it seemed simple but maybe it’s jot..........

Check out the attached spreadsheet.
On sheet2 column A I have a formula which looks at sheet1 column A and eliminates all duplicate bar codes. Column B of sheet2 is then the simple COUNTIF that you were already hoping to use.

Is this close to what you are looking for?

Hi

You only need a pivot table.

See the file (based @GeoffW283 )

Thanks for the pointers. I’ll admit I’m new to piviot tables but will investigate and see what I can do with the doc atttached. Wil report back when I’ve done a bit of research on pivot tables.