Help with formula

[scubagrl]

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

 

[BenjieLop]

I have tried different approaches for this problem and still can no
figure it out. I am working with two different spreadsheets and need t
find matching data in both sheets. I need to compare column A on RAW.xl
and column C on SMS.xls. Any matches between the two columns is put int
a third file called master. The data I am looking at is text. Any hel
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 [/B] AND COPY DOWN UNTIL SAY A100 (THIS CAN B
ADJUSTED TO SUIT YOUR REQUIREMENTS).

2. DO THE SAME FOR ALL THE ENTRIES IN SMS.XLS. YOU CAN LIST THESE I
COLUMN B USING THIS FORMULA

*=[SMS.XLS]SHEET1!C1 * AND COPY DOWN UNTIL SAY B100 (THIS CAN B
ADJUSTED TO SUIT YOUR REQUIREMENTS).

YOUR NEXT STEP IS TO COMPARE THESE 2 COLUMNS FOR DUPLICATES. ENTER THI
FORMULA

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

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

You can now use Column D to list all the entries that have bee
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