Asset Inventory, there has to be an easier way! | Business Data Collection Tools

“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.