Re: Difference between two ranges

From: JAY (anonymous_at_discussions.microsoft.com)
Date: 11/19/04 

Date: Fri, 19 Nov 2004 06:06:04 -0800

THATS GREAT THANKS SO MUCH,I KNEW IT WOULD BE FAIRLY EASY
BUT I WAS JUST HAVING TROUBLE UNDERSTANDING IT THE WAY IT
WAS PHRASED...THANKS AGAIN

>-----Original Message-----
>Hi Jay
>
>okay
>you have one list in column A cell range from A1:A10
>you have another list in column B cell range from B1:B10
>and in column C you want the values that are in BOTH
>in C1 type
>=IF(COUNTIF($A$1:$A$10,B1)>0,B1,"")
>
>and press Ctrl & Shift & Enter instead of just enter
>now fill down (move cursor over bottom right corner of
cell until you see a
>+ then double click)
>when excel encounters a value in both columns it will
write it into column
>C.
>
>HOWEVER, i think the last example on Chip's page matches
your SQL statement
>more accurately:
> ----
>Extracting Values On One List And Not Another
>
>Another frequent tasks involving lists is to create a
list containing values
>in one range that are not in another range. Suppose
there are two lists, in
>A1:A10 and B1:B10. Enter the following array formula in
the first cell of
>the range which is to contain the entries in B1:B10 that
do not occur in
>A1:A10.
>
>=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 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.)
>
>-----
>
>This will tell you what entries are in column A that are
not in column B,
>again enter the formula in C1 using CTRL & SHIFT & ENTER
and fill down.
>
>This time however, the values listed in C will be those
from A where a match
>can't be found in B.
>
>
>
>Hope this makes some sense. If you need further
assistance, just post back.
>
>
>Cheers
>
>julieD
>
>
>
>"Jay" <anonymous@discussions.microsoft.com> wrote in
message
>news:76cc01c4ce37$57ef7310$a601280a@phx.gbl...
>> JulieD
>>
>> Thanks, I know I need to use this, but I am hav a little
>> dumb moment. Can you possibly send me a working example
or
>> rephrase this:
>>
>> 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:
>>
>> 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.)
>>
>>
>>
>>>-----Original Message-----
>>>Hi Jay
>>>
>>>Chip Pearson has lots of information on his site for
>> working with duplicates
>>>http://www.cpearson.com/excel/duplicat.htm
>>>
>>>
>>>Cheers
>>>JulieD
>>>
>>>"Jay Mandevia" <jay.mandevia@naa.org.uk> wrote in
message
>>>news:02d801c4ce34$2fe7fb10$a501280a@phx.gbl...
>>>> Hi I would like to know if it is possible in excel to
>> tell
>>>> the difference in values between two columns. I know
in
>>>> SQL i can create query to say:
>>>>
>>>> select *
>>>> from table1
>>>> where id NOT IN (select ID from table2)
>>>>
>>>> How can i tell the which row is not in the first
column
>> in
>>>> excel?
>>>>
>>>> Thanks
>>>
>>>
>>>.
>>>
>
>
>.
>