Comparing 2 columns of text

[christal]

I am trying to compare two columns of text on two different worksheets. How can I show or identify errors (misspellings, new names, etc.) and missing data? For example, comparing Sheet 1, column A, range 1-25 and Sheet 2, column A, range 1-25.

I would like to display any errors on sheet 1.

Any ideas would be greatly appreciated!!

Christal

[Dave Hawley]

Hi Christal

On sheet1 Cell B1 put

=IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$25,0)),Sheet2!A1 & " needs help","No Problem")

Copy down as far as needed. You may also need to expand the range $A$1:$A$25

[Aladin Akyurek]

On 2002-04-10 05:26, christal wrote:
I am trying to compare two columns of text on two different worksheets. How can I show or identify errors (misspellings, new names, etc.) and missing data? For example, comparing Sheet 1, column A, range 1-25 and Sheet 2, column A, range 1-25.

I would like to display any errors on sheet 1.

Any ideas would be greatly appreciated!!

Christal

You could name the ranges of interest in the second workbook via the Name Box on the Formula Bar, e.g.,

Range as name for A1:A25 in the second workbook.

and use the following formula in the first workbook in B1 and copy it down:

=IF(COUNTIF(christalWB2.xls!Range,A1),"","Error")

This gives only which entries in the first workbook are non-existent in the christalWB2.

Aladin

[christal]

That was some fantastic work!! But....
I am getting a message that every cell is not matching, and I know atleast the first one matches. I copied my formaula for you to see if I did it correctly... can you figure out what happened??

=IF(ISNA(MATCH($D$2:$D$112,0)),[InvAFH0204.xls]Sched51!$D$2:$D$112 & " needs help","No Problem")

Thanks!!

Christal

[ This Message was edited by: christal on 2002-04-10 09:14 ]