Re: duplicates

From: JulieD (JulieD_at_hctsReMoVeThIs.net.au)
Date: 02/16/05 

Date: Wed, 16 Feb 2005 23:59:39 +0800

Hi Dan

from Chip Pearson's page on duplicates
(http://www.cpearson.com/excel/duplicat.htm)
-----
Extracting Values Common To Two Lists

You can easily extract values that appear in both of two lists. Suppose your
lists are in A1:A10 and B1:B10. Enter the following array formula in the
first cell of the range which is to contain the common entries:

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

Change B1 and $A$1:$A$10 to the first cells in the ranges from which data
that you want to extract common items. Then, use Fill Down (from the Edit
menu) to fill the formula down to as many rows as you need to hold the
common entries (i.e., up to as many rows as there are in the original
range.) 

---
NOTE:  an array formula (as above) needs to be committed with control & 
shift & enter not just enter.
Cheers
JulieD
"Dan @BCBS" <DanBCBS@discussions.microsoft.com> wrote in message 
news:4C4B2EAF-D623-4ACC-9464-AECFB7EEE7B1@microsoft.com...
>I hope this is a simple question!
>
> I have one spread*** with two columns, each column is a list of provider
> numbers.
> All I want to do is identify the provider numbers that are in both 
> columns.
>
> Sounds simple, I've tried many things with no success.
>
> Thanks