Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

BARCODE SEARCH AND PUT INFORMATION IN COLUMN

0

This is a two-fold question (I a not a programmer) but I really want to learn and i need help with barcodes. If/when I scan a barcode it enters the information on the left side (A2-A200). If say for some reason I want to go to a barcode that is random and click on it - it goes to the next column. I want it to search for the correct row (identical information) and match it up (i.e. E2 (serial number) matches A2 scanned (serial number). NOT E2 (serial number) matched to A100 (serial number). That is my big issue...I have tried the index, search, vlookup, xlookup, etc.

The second part if It's allowed....is B2-B200 I want it to change to YES and green if A2-A200 matches the (serial number) of E2-200. 

Neither of these seems hard but I can't get it to work the way I want it too...

Thanks.

excelvlookup
MS Office Version: 365
posted by: NIXONPHOTOS (rep: 4) on Thu Oct 29, 2020 at 11:44 am
Answer
Discuss

Answers

1
Selected Answer

From your confirmation below that you already have 138 or more Serial Numbers with Equipment ID/ Machine information  and want to search those, please see the attached spreadsheet.

In that, I've added three rows to the top and, for your serial numbers, added imaginary Equipment and Machine ID (where E_5 indicates a match was made to row 5 etc.).

If you put a known serial number in cell A2 (I've left it with C298RB00517 from row 16), Index and Match will populate C2, D2 and E2). If you enter a serial not recorded in A5:E200 (say "13579"), those cells will show #N/A and B2 will show "Not found".

The formula in C2 is:

=INDEX($C$5:$C$200,MATCH($A2,$E$5:$E$200,0))

and to understand it, look at the TeachExcel tutorial on Index and Match here Index and Match Tutorial

The formula in B2 is different to yours and I haven't added any conditional formatting (but toned down the colours in yours!).

Hope this helps (and apologies for putting a discussion point as an Answer initially)

posted by: John_Ru (rep: 6142) on Thu Oct 29, 2020 at 1:01 pm
Discuss

Discussion

I already have all the information in the spreadsheet including the serial number. I want to scan a barcode that has that serial number on it and have it match up to the existing information that is in the spreadsheet and at that point shows me that it has been found by changing colors, or something to signify that it was scanned. 

Hope this helps...
NIXONPHOTOS (rep: 4) Oct 29, '20 at 1:12 pm
I appreciate the training...I like what you have done. It's still putting the New Scan where ever it wants. I scanned E5 and it put the serial number in a7 and shows NO!. Is there a way for it to know to place it in A5 when scanning the barcode?
NIXONPHOTOS (rep: 4) Oct 29, '20 at 1:59 pm
Thank you so much...after reading the link you gave me and experimenting I have it figured out. 
NIXONPHOTOS (rep: 4) Oct 29, '20 at 3:14 pm
Sorry but you have not said how a new scan is added to your spreadsheet so I can't help. I can't understand your *goes into A7" in the context of my revision. 

I assumed you tried the file I sent and realise that data from a new scan would need to go in cell A2 for the search to be done (and if nothing found, I guessed you would add new data in an empty row in the range A5:E200).

I'm currently out and no access to my PC but might be able to help later.
John_Ru (rep: 6142) Oct 29, '20 at 3:22 pm
Oops! I was typing the reply above when your reply was sent. Glad to hear you have it sorted now. 
John_Ru (rep: 6142) Oct 29, '20 at 3:25 pm
Add to Discussion


Answer the Question

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