Problem comparing text/numerical columns in Excel

[Ammarhm]

Hi all
I am having a real problem comparing two columns in excel, I am attaching the excel file with this post
Simply. I have 2 lists, List 1 and 2, each no. in the file represents an item
The problem is that the item no. are entered as numerical, while in the second one they are entered as text, so the an item might be entered as 1234 in the first list and 001234 in the second list (remeber that entering numbers starting with 0 in a cell causes the 0 to be automatically ommitted)
Sometimes if the item is entered two or three times a suffex -2 or -3 is added x 142454-2 and 142454-3

The porblem is comparing to see the differences between the columns, how can I do that?

Regards

 

[Brianwarnock]

to look for matches of Col A in Col B

In cell C2 enter

=IF(LEN(B2)>7,VALUE(LEFT(B2,7))&RIGHT(B2,2),B2*1)

in d2

=MATCH(A2,C$2:C$1016,0)

drag the formulae down

matches will give the row number -1, non matches #N/A
to reverse the procedure in cell e2
=MATCH(C2,A$2:A$1016,0)

and repeat


Brian