Subtraction Query

B

Brian Bagnall

I have two worksheets in the same document with a long list of Email
addresses. I want to use a Query to create a new list that has only
the Email addresses that are not present in both lists (i.e.
subtraction).

In Excel 2003 I tried going to Data, Import External Data, New
Database Query and it made me select another worksheet. When I
selected the one I was working on it said there are no tables in the
worksheet. (Whats the difference between a column of data and a
table?)

Anyway, am I going about this the proper way to perform a Query?

Thanks for any assistance you can offer,

Brian
 
C

Chip Pearson

Hi Anne,

It seems that every one of your replies points to a page on your
web site. Just out of curiosity, do you write the reply as a web
page when you read the post?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
A

Anne Troy

I did in the beginning if I felt the question was commonly asked. But now, I
only seem to be adding one every couple weeks or so. I do update them, tho,
from time to time, when I see a "gotcha" that I didn't include. Office
Articles is more in keeping what I wanted The Office Experts to be, but had
to sell TOE when I got sick and desperately needed money, and just happened
to have a buyer. :)
************
Anne Troy
www.OfficeArticles.com
 
C

Chip Pearson

where I don't feel like such a dummy. :)

You're hardly a dummy in the Excel groups. All of your replies
are correct and relevant. Great job!


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
R

Ron Coderre

In Excel 2003 I tried going to Data, Import External Data, New Database
Query and it made me select another worksheet. When I selected the one I
was working on it said there are no tables in the worksheet. (Whats the
difference between a column of data and a table?)
Click to expand...

You need to set up named ranges for MS Query to be able to identify your
data lists.
You do that by:
•Insert.Name.Define
•Type in the name you want to use (eg rngEmails2)
•Select the range to use that name for, including the column heading (your
list of emails).
•Click the Add button


Brian Bagnall said:
I have two worksheets in the same document with a long list of Email
addresses. I want to use a Query to create a new list that has only the
Email addresses that are not present in both lists (i.e. subtraction).
Click to expand...

After setting up your named ranges, try this . . .

•Insert a new worksheet into the workbook and select that worksheet
•Save the workbook
•Data.Import External Data.New Database Query
•Select the Excel Files data source
•Select the workbook you just saved (even though it's the current workbook)
•Select one of the range names
•Click next until the final MS Query screen
•Select the option to edit the query
•Click the SQL button to edit the SQL code
•Edit the code to resemble this:

SELECT ComboList.EmailList
FROM (
SELECT ListOne.EmailList
FROM `C:\EmailLists`.rngEmails1 ListOne
UNION ALL
SELECT ListTwo.EmailList
FROM `C:\EmailLists`.rngEmails2 ListTwo
) ComboList
Group By ComboList.EmailList

•Run the query
•Click the Return Data button

Does that help?

Regards,
Ron
 
R

Ron Coderre

For the SQL that only returns email addresses that are in one list, but not
in both, this is one approach (assuming no duplicate items in individual
lists):

SELECT EmailList
FROM (
SELECT EmailList
FROM `C:\EmailLists`.rngEmails1
UNION ALL
SELECT EmailList
FROM `C:\EmailLists`.rngEmails2
)
GROUP BY EmailList
HAVING COUNT(EmailList)=1

Regards,
Ron
 
Top