Stocktake of Barcoded Inventory in Excel using a Barcode Scanner

Kobus Horn

New Member
Joined
Nov 11, 2024
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Good morning... I am struggling to get this figured out... Please help... I'm not an Excel Expert and would appreciate guidance

This is my Setup
* I have a Warehouse with about 6000 Unique Barcodes of Technical Equipment... One Unique Barcode for every piece of Equipment (Barcode looks as follows: AVB001234, AVB001235, AVB001236, AVB001237 etc etc etc)
* I want to do a Stocktake using a Barcode Scanner that has already been setup to work in Excel... The Scanner is currently setup in this way... I Choose a Cell, then Press the Trigger and when the Barcode is Scanned Barcode "Enters" into Cell and then goes to the Cell Below Automatically... In other words... every Barcode that gets scanned it will put in that Barcode in the Cell Chosen and then automatically moves to the cell below ready for scanning again (Firstly it needs to "GO BACK" to "E1" repeatably scan after scan after scan
* I need guidance with the following PLEASE

Mr Excel 1.jpg


1. I need to setup the "SCANNING CELL - "E1" (Highlighted in Yellow) which was scanned using Barcode Scanner in such a way that I click there once with my Mouse to Highlight "E1"
2. Then Click the Trigger of the Barcode Scanner on a Unit on my Warehouse (Showing example scanned AVB002001 in "E1")
3. Then the Workbook must find that Barcode Scanned in Column "E" searching from Row 2 - 3000 in Column "E" finding the Corresponding Barcode "AVB002001"
... also keeping in mind that sometimes there will be some text behind the Barcode (Example - AVB001613 - Pro) that does NOT exist on the Barcode BUT it does exist on the Spreadsheet... so if there is Text behind the Barcode it must still find it - (Example - "AVB001613 - PRO") as shown on the Image in Cell "E40" - This was manually put in the Spreadsheet for extra descriptive text for "some" equipment
4. When the Barcode is found, put in a "YES" in Column "D" (STOCK) next to the same Barcode to "Mark" it as "SEEN"
5. Then change the Cell Color in Column "D" to Green to make it easier to see what needs to be still Scanned in the Warehouse
6. Once it has been "SEEN" and the "YES" put into Column "D" next to the corresponding Barcode and the Cell changed color to Green
7. IF the Barcode Scanned does NOT Exist in the Workbook

After the above has been done the following needs to happen...

RETURN to Cell "E1"
CLEAR the content scanned (Example AVB002001)
and be READY for the Next Barcode to be Scanned

This obviously needs to work in a Stocktaking manner where the Scanner can be Repeatably Triggered Click, After Click, After Click without needing to stop to change Cell after every Trigger...

*Needs to work as Follows -... "E1" Scan... Add YES, Change Color, Go back to E1 and Scan next... Scan Triggered every few seconds on next Barcode

I have to mention the fact that the Scanner when Scanned... automatically enters the Barcode (Example) "AVB002001" in the cell chosen and the Automatically goes to the next row below ready for the next scan...

I hope I have explained this in such a detailed way that someone can understand exactly what I need and PLEASE Help me
 

Attachments

  • Excel Screenshot.png
    Excel Screenshot.png
    88.6 KB · Views: 1

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,223,577
Messages
6,173,163
Members
452,503
Latest member
AM74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top