Difference between two ranges

J

Jay Mandevia

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
 
J

Jay

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.)
 
J

JulieD

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
 
J

JAY

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
 
J

JulieD

you're welcome

JAY said:
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
Click to expand...
 
J

Jamie Collins

Jay Mandevia said:
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?
Click to expand...

Two points. First, the SQL you require would be:

select table1.MyDataCol
from table1 LEFT JOIN table2
ON table1.id = table2.id
WHERE table2.id IS NULL

Although your SQL would work as posted, it would run very slowly.

Second, you can run SQL queries against Excel data. To do this within
Excel, the popular choices are MS Excel for GUI tools and ADO for VBA
code.

Jamie.

--
 
Top