Compare A Scanned Barcode And Display It On The Correct Column


Hi, my job require me to check assets using scanner and this is the excel format my company given me. But at this moment, when I scanned, I had to first search for the correct barcode and then I scanned it at that column. Then the checked Column will turn Yes/Green. As my job require me to walk around with my laptop I had difficulties in searching the data while holding my laptop on my hand specially for a long period of time if I am conducting a Annual Stocktaking of the 2000 Assets within 3 days. 

Therefore, I trying to see if I can improve on the scanning process, and if there is any ways or methods that allow excel to auto search and input the barcode to the correct cells on a scan here box I created on top of the spreadsheet, but try many ways, but unable to do it. And  also hope that at the end of the check also help to caculate the total qty of the Yes and No checked by Asset Type, so that I can input them into my main report.

Hope that someone, can help me on this. Thanks.



Selected Answer

Make a new worksheet and scan each barcode on a new row in column A on that worksheet.

Then put a Vlookup function into worksheet one and copy that down.

Here is a sample vlookup that you could put into cell A4 and copy it down:


For the yes/not columns, use this formula:



Thanks you, Don for the formula I will try it out and then get back to you the next few days. Now year end have a lot of Annual Stocktaking to complete. Thanks again.
JNG0609 (rep: 2) Oct 31, '16 at 10:58 am
You're welcome! And if this worked for you, don't forget to mark the answer by clicking the Select Answer button for it.
don (rep: 1237) Nov 1, '16 at 7:44 pm
Hi Don, I tried, it can work,but this there anyway to let the correct answer appear on the correct column on the main worksheet. At the moment it don't. Thanks
JNG0609 (rep: 2) Nov 3, '16 at 7:11 pm
You are going to have to be more specific. This formula should work that way when you put it into Sheet1. If you need to create a cross-worksheet formula, look to one of our tutorials for this: Linking Cells in Excel
don (rep: 1237) Nov 3, '16 at 11:42 pm
Hi Don, Thanks. I got it. For the yes/not columns, I  use is formula:=IFERROR(INDEX('FORMULA_DO NOT DELETE'!$A$1:$A$300,MATCH($D$1:$D$271,$A$1:$A$271,0)),"NO!")

JNG0609 (rep: 2) Nov 5, '16 at 10:58 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login