Complex data analysis, finding values from one main source

[Eduards]

Hi,
In column A:

Is a list of all Barcodes with not correct format and correct format.

In column E:

List of Barcodes in correct format

The problem:

The correct format is in Column E: with 0 in the front and a check digit end of the number. This column is the main source where Barcodes are assigned from.

Our system somehow screwed up and automatically assigned Barcodes that either has or hasn't got a zero in the front or... has or hasn't got a check digit at the end of a Barcode. All data from database extracted to column A

I need to point out or highlight, or any other way to identify all the Barcodes from Column A that match Column E.

In the sample workbook, all Barcodes from Column A match the one in E.
This is because, if you look at column E:

Shows this value here;
05013351100041

The ones highlighted in BOLD is what matters and match to column A.

So A1 A2 A3 A4 A5 are all true values.

If that makes no sense, please let me know and ill try my best to explain.

Thanks!

[Eduards]

Im thinking maybe a custom sort that searches the middle number for all barcodes in Column A? Is that possible?

I have over 5,000 Barcodes in column A

[Sam Capricci]

you could use something like this to find out if what is in E matches what is in A...
=IF(IFERROR(VLOOKUP(E2,$A$2:$A$5,1,FALSE),"")="","","match")
if it doesn't match A it will return blank and match if it is.
now if you want to fine out if A is in E then reverse the direction to this...
=IF(IFERROR(VLOOKUP(A2,$E$2:$E$5,1,FALSE),"")="","","match")

oh, and by the way, extend the reference cells as needed.
and lastly, you can change the third blank to this if you want it to say doesn't match
=IF(IFERROR(VLOOKUP(E2,$A$2:$A$5,1,FALSE),"")="","doesn't match","match")
and reverse and extend as needed.

[davsth]

It is not clear if other values can appear that wouldn't count.
=COUNTIF($E$2:$E$17,"*"&A2&"*") would work for the example you have given but 050133511000411 would also result in a positive value. this may be a problem it may not!

or
=COUNTIF($E$2:$E$17,"?"&A2&"?")+COUNTIF($E$2:$E$17,"?"&A2)+COUNTIF($E$2:$E$17,A2&"?")+COUNTIF($E$2:$E$17,A2)

[Eduards]

It is not clear if other values can appear that wouldn't count.
=COUNTIF($E$2:$E$17,"*"&A2&"*") would work for the example you have given but 050133511000411 would also result in a positive value. this may be a problem it may not!

or
=COUNTIF($E$2:$E$17,"?"&A2&"?")+COUNTIF($E$2:$E$17,"?"&A2)+COUNTIF($E$2:$E$17,A2&"?")+COUNTIF($E$2:$E$17,A2)

Hi, this one is perfect! how can it highlight the cell in column A its there?

[davsth]

apply it as a conditional format to the cells in column a Use a formula to determine what cells to format 1 is equivalent to true and choose a colour for the cells when this condition is met