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.


--
scubagrl
------------------------------------------------------------------------
scubagrl's Profile: http://www.excelforum.com/member.php...o&userid=14299
View this thread: http://www.excelforum.com/showthread...hreadid=467513

scubagrl Wrote:
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 procedu

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 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

[b]=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=\"DUPLICAT E\",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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=467513