Comparing barcode data to a column in Excel 1

[dwtlsmith]

I am trying to compare a barcode to a list (column) in Excel. I've found ways to compare a column to column but I haven't found how to compare one string to an entire column.

Trying to verify that we don't have redundant barcodes.

Thanks
Doug

 

[alexit]

Use the FIND command
If your column is in A1:C30 and the barcode string is stored in B1 then copy this into C1:C30:

=FIND(B1,A1)

Then column C will show #VALUE! if the string in not present or a number which is the start postion where the string is first found if present.

 

[joerd]

That is probably =FIND($B$1, A1) for cell C1, and copy down. In cell C30 you should get =FIND($B$1, A30)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[alexit]

Thanks joerd, I forgot about fixing the string to find...

 

[dwtlsmith]

I got the FIND command to work.
Thanks!
I also was able to get the EXACT command to work. The only problem I have is I would like the answer in 1 cell only. For example, if the code in A1 matched any of the codes in B1:B6000 then C1 would be TRUE or 1.

I am using that cell(answer) in some VB for a different machine.

 

[joerd]

Then you should use COUNTIF, for example:
=(COUNTIF($B$1:$B$6000,A1)>0)
COUNTIF gives you the number of occurrences in the range B1:B6000 where the value equals A1. If it is greater than 0, the boolean expression evaluates to TRUE.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[dwtlsmith]

Thank You.
CountIf does all I needed.


Thanks
Doug