Help with formula

[scubagrl]

I have tried different approaches for this problem and still can not figure it out. I am working with two different spreadsheets and need to find matching data in both sheets. I need to compare column A on RAW.xls and column C on SMS.xls. Any matches between the two columns is put into a third file called master. The data I am looking at is text. Any help is appreciated. Thanks.

[BenjieLop]

I have tried different approaches for this problem and still can not figure it out. I am working with two different spreadsheets and need to find matching data in both sheets. I need to compare column A on RAW.xls and column C on SMS.xls. Any matches between the two columns is put into a third file called master. The data I am looking at is text. Any help is appreciated. Thanks.

I would suggest the following procedure:

1. On the 3rd file, Master.xls, list all the entries of RAW.xls in, say, Column A. Assuming that the entries are from Sheet1 of RAW.xls, your formula would be

=[RAW.xls]Sheet1!A1 and copy down until say A100 (this can be adjusted to suit your requirements).

2. Do the same for all the entries in SMS.xls. You can list these in Column B using this formula

=[SMS.xls]Sheet1!C1 and copy down until say B100 (this can be adjusted to suit your requirements).

Your next step is to compare these 2 columns for duplicates. Enter this formula

=IF(COUNTIF($A$1:$A$100,B1)>0,"Duplicate","")

in Column C and copy down until C100. Column C will now let you know which entries are both found in Columns A and B.

You can now use Column D to list all the entries that have been identified as "Duplicate." You can enter this formula in Cell D1.

=INDEX($B$1:$B$100,SMALL(IF($C$1:$C$100="Duplicate",ROW($B$1:$B$100)),ROW(1:1)))

NOTE: This is an array formula, so commit with "Ctrl-Shift-Enter" instead of simply doing "Enter." Copy this down until D100.

I hope this will help you with your problem.

Regards.