• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I am trying to use Excel to sort barcodes and return text in a seperate column after it is found

S

SeaWater

New Member
I am able to input bar code scans into Cell A1 into a chemical inventory spread sheet. In column B1 I used the =MATCH function to return the row it is found in. I now need to have a text character , say an "X" returned into the last column (G) of my spread sheet after each row entry and for the X to stay in the cell when I go to scan the next chemical's bar code. Items which have not been found can remain blank and then removed from inventory. Thank you for any input.
 
Hi,
Welcome to forum Chandoo.org...

Not clear to me....
Can you post a sample of your data with expected result in excel file?

Regards,
 
Thanks,

Try this works for you or not: in G3 and copy down

=IF(INDEX($E$3:$E$205,MATCH($A$1,$E$3:$E$205,0),1)=$E3,"X","")

Regards,
 
I forgot to share CF way:
Select Column E range
Go to conditional format > formula:
=E3=$A$1

Set fill color to suite.
 
Thank you for your help so far... how do I "lock" the "x" and keep it from disappearing when I go onto the next barcode number?
 
Hi SeaWater,

To "lock" the result of a formula, you probably need some sort of macro solution. Perhaps its easier to simply store each scanned barcode on a new row each time on a different sheet and have your inventory sheet a compare formula based on your scanned barcodes.

Please see attached file.
 

Attachments

SeaWater said:
Yes it works!!! Is it possible to transfer The VBA program to other files?
Click to expand...
Sure just copy this code to the sheet module on which you want it.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing And Target.Count = 1 And Not IsError(Target.Offset(, 1)) Then
    Cells(Range("B1").Value, 7) = "X"
End If
End Sub

Regards,
 
You must log in or register to reply here.
Back
Top