listing unmatched items

[ravi]

hi, would anyone pls help me out...!

i would like to extract an 'Unmatched' listing of data
from two or more worksheet files which has one common
field containing 'reference number'.

One file is downloaded from AS400 and converted to .txt
format which has to be compared with another file already
available in .txt format. I would like only
the 'unmatched' to be listed out based on a similar
column consisting of ref.nos.

Is it possible to get such listing only thru excel...?
appreciate if could assist me thru steps or where to look
for details.

thanks for your help!!

 

[Bernie Deitrick]

ravi,

Open both files, then use a helper column in the one where you want to
find the unmatched values. Then use a formula like this, that will
put TRUE next to unmatched values from column A, comparing them to the
values in the workbook "Reference Numbers" and Sheet "Reference Sheet"

=ISERROR(MATCH(A1,'[Reference Numbers.xls]Reference
Sheet'!$A:$A,FALSE))

Then you can sort based on your formula results, and all the unmatched
values will be grouped together.

HTH,
Bernie
MS Excel MVP