“There has to be an easier way!”

Great things start with that phrase. Have you found yourself saying that statement when counting computers, office equipment, or other serial numbered assets?

There is an easier way. Especially if the asset has a barcode. Even if it doesn’t, take the time to make a barcode and make it easier on yourself the next time.

The tools for the easier way are simple.

1. A spreadsheet software (Excel, Numbers, Google Sheets)
2. Barcode asset tags
3. Scan to Spreadsheet from Berry Wing

We have a great video demonstration that covers this much better than we can write this blog post. We encourage you to watch it all the way through. The first half covers the barcode scanning and how to catch out of place items. The second half of the video shows how to use Google Sheets to show what is on hand and what assets are missing.

Using Scan to Spreadsheet is 3 basic steps.

1. Import your asset list in to Scan to Spreadsheet

Using this imported list, we can confirm as we go along. Checking if a barcode scanned was supposed to be in our list.

Set validation to “Error when NOT in list”

Now if you scan a barcode that is not a member of your master list, a message will appear so you can remove that item or save it to handle later.

How to import a spreadsheet:

2. Start scanning your assets

This is easy, scan all the barcodes. Scan to Spreadsheet will collect the asset tags in a list to compare later.

3. Compare barcode scans to Asset List

In your Master Asset list you can use VLOOKUP to match the list of barcodes you scanned to your master list of asset tags. The formula will show if anything is missing when there are no matches.

Here are more detailed instructions to follow along.

1. Import asset list int Scan to Spreadsheet
2. Save the asset list as import.csv
3. Import into Scan to Spreadsheet
4. Set the validation to Error when NOT in list
5. Create a new spreadsheet named Assets
6. Start scanning
7. When you scan a barcode that is not in the asset list
8. Save - saves immediately, you may continue scanning
9. Review - You can remove it or add a note when saving.
10. Finished, email the spreadsheet or share directly to Google Drive
11. Open Assets.csv
12. Copy the list of asset numbers to the main asset spreadsheet,
13. Paste in a different tab
14. Highlight the list of scanned serial numbers/asset tags
15. Right click and select Defined Named Range
16. Give the range a name of ASSETS
17. Click Done.
18. Open the main Assets listing
19. In the On Hand column, create a VLOOKUP
20. =VLOOKUP(A2, ASSETS, 1, FALSE) A2 - the column with the original asset number ASSETS - the named range from the scanned asset tags 1 - Column index we are searching, this is 1 to 1 FALSE - We want exact matches
21. Copy the VLOOKUP in the same column for all rows
22. Note the missing ones.

1. Import asset list int Scan to Spreadsheet
2. Save the asset list as import.csv
3. Import into Scan to Spreadsheet
4. Set the validation to Error when NOT in list
5. Create a new spreadsheet named Assets
6. Start scanning
7. When you scan a barcode that is not in the asset list
8. Save - saves immediately, you may continue scanning
9. Review - You can remove it or add a note when saving.
10. Finished, email the spreadsheet or share directly to Google Drive
11. Open Assets.csv
12. Copy the list of asset numbers to the main asset spreadsheet,
13. Paste in a different tab
14. Highlight the list of scanned serial numbers/asset tags
15. Right click and select Defined Named Range
16. Give the range a name of ASSETS
17. Click Done.
18. Open the main Assets listing
19. In the On Hand column, create a VLOOKUP
20. =VLOOKUP(A2, ASSETS, 1, FALSE) A2 - the column with the original asset number ASSETS - the named range from the scanned asset tags 1 - Column index we are searching, this is 1 to 1 FALSE - We want exact matches
21. Copy the VLOOKUP in the same column for all rows
22. Note the missing ones.