Spreadsheet for doing stocktake

Hi all,

I’m trying to create a spreadsheet that I can use to complete my stocktakes.

I'm hoping someone can give me advice in regards to what formulas I need to learn etc. I can do a few basic things in excel but my knowledge isn't currently at the level where I can create what I'm trying to on my own.

What I’m trying to achieve is being able to scan a barcode, it matches it with a model number and description, and outputs a page giving me the totals for each product.

For example

Barcode 12121212 = product code: “AAAA” and description “item a”
Barcode 34343434 = product code: “BBBB” and description “item b”

If I scan 12121212 3 times and scan 34343434 6 times it will give me a result of

AAAA | Item a | 3
BBBB | Item b | 6

I’d like the result to be sorted by the product code in alphabetical order.

I figure I’ll need 3 sheets –
1 with all the data: x barcode = y product = z description
1 where I use a barcode scanner to scan all the products
1 which will output the final information

As identical products aren't all grouped together I can't enter a total quantity for each item in one go. I need it to count how many times I have scanned it.

If anyone can point me in the right direction it would be much appreciated!

G'day Marc,

The approach you suggest is pretty much spot on except for the sheet capturing the stocktake scan. How will this data be captured? I assume you have a scanner which will input the code onto a spreadsheet. I have a few questions:
My main concern is that Excel has a finite number of rows, so you don't want to use one row for each scan.

Is it possible to scan a doodlebug, count how many on the shelf, and key the quantity?
Does your scanner software write to a new row every time?
Are you scanning directly to your computer (ie your Excel worksheet) or is the information being stored in the scanner and then downloaded?

Please let us know a little more about your processes.

Regards, David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by using the Quote
feature or mention their name.
If we have been of assistance, please let us know. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hello Marc,

I have attached a simple example of what you can do. Basically, the Stock List is also being used to give you the Inventory.

The formula in column D is =COUNTIF(scan, A2)- this is because I have a "Named Range" on Column A of the Scan Input sheet. If you want to avoid that complication, it could just as easily be =COUNTIF('Scan Input'!A1:A100000,A2) (allowing for a maximum of 100,000 items).

Marc_au Stocktake.xlsm