Dear experts,

In excel, I have to find the differences between two sets of data.
Just like your set theory from grade 9 .

In one set, I have, say,

A
B
C
D
E
F

In the other set, I have,

D
E
F


Set one, minus set two is:
A
B
C


I would like to put the results of the minus into a new column.


In sql, you would just:

Query1
minus
Query2

But database is not an option here. The data is only in the
spreadsheet.


How can I do the same thing in excel?

Related to this, would be: Union, and Intersect.


Thanks

Actually SQL is an option:

http://www.erlandsendata.no/english/...php?t=envbadac

If using ADO, you shouldn't query from an open workbook. A solution is to
do

Activeworkbook.SaveCopyAs Name

then query against that copy, then you can Kill it when done.

--
Regards,
Tom Ogilvy


wrote in message
ups.com...
Dear experts,

In excel, I have to find the differences between two sets of data.
Just like your set theory from grade 9 .

In one set, I have, say,

A
B
C
D
E
F

In the other set, I have,

D
E
F


Set one, minus set two is:
A
B
C


I would like to put the results of the minus into a new column.


In sql, you would just:

Query1
minus
Query2

But database is not an option here. The data is only in the
spreadsheet.


How can I do the same thing in excel?

Related to this, would be: Union, and Intersect.


Thanks

Try using an unmatched query.

" wrote:

Dear experts,

In excel, I have to find the differences between two sets of data.
Just like your set theory from grade 9 .

In one set, I have, say,

A
B
C
D
E
F

In the other set, I have,

D
E
F


Set one, minus set two is:
A
B
C


I would like to put the results of the minus into a new column.


In sql, you would just:

Query1
minus
Query2

But database is not an option here. The data is only in the
spreadsheet.


How can I do the same thing in excel?

Related to this, would be: Union, and Intersect.


Thanks

hi !

assuming set1 in 'A1:A6', set2 in 'B1:B3' and 'new sets' in columns:
'C' - data in set1 NOT in set2 [or unmatched/minus/differences]
'D' - data in both sets [or matches]...
[also] items in both sets are less than 100...
- try with the following array formulae {ctrl}-{shift}-{enter}:

[C1] =index($a$1:$a$6,small(--substitute(row($a$1:$a$6)*(countif($b$1:$b$3,$a$1: $a$6)=0),0,100),row()))
[D1] =index($a$1:$a$6,small(--substitute(row($a$1:$a$6)*countif($b$1:$b$3,$a$1:$ a$6),0,100),row()))

copy/down until you get errors [or 0's if you change index(... to offset(... or indirect(... ]
you could adequate for data in set2 NOT in set1

hth,
hector.
=====
In excel, I have to find the differences between two sets of data.
Just like your set theory from grade 9. In one set, I have, say,
A
B
C
D
E
F
In the other set, I have,
D
E
F
Set one, minus set two is:
A
B
C
I would like to put the results of the minus into a new column.
In sql, you would just:
Query1
minus
Query2
But database is not an option here. The data is only in the spreadsheet.
How can I do the same thing in excel?
Related to this, would be: Union, and Intersect.

One way using non-array formulas ..

Set 1 assumed in A1:A6, Set 2 in B1:B3

Put in C1:
=IF(ISERROR(SMALL(D:D,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

Put in D1: =IF(ISNUMBER(MATCH(A1,B:B,0)),"",ROW())

Select C1:D1, copy down to D6
(cover the extent of data in set 1)

Col C will return the desired results, all neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ups.com...
Dear experts,

In excel, I have to find the differences between two sets of data.
Just like your set theory from grade 9 .

In one set, I have, say,

A
B
C
D
E
F

In the other set, I have,

D
E
F


Set one, minus set two is:
A
B
C


I would like to put the results of the minus into a new column.


In sql, you would just:

Query1
minus
Query2

But database is not an option here. The data is only in the
spreadsheet.


How can I do the same thing in excel?

Related to this, would be: Union, and Intersect.


Thanks

Hi,



If there is no duplicated values, the ***intersection*** is a inner join:


SELECT a.f1 FROM a INNER JOIN b ON a.f1=b.f1


where f1 and g1 are the involved columns in tables a and b



If there is no duplicated values, the ***subtraction***, a-b, is an outer
join with test on the b side:


SELECT a.f1 FROM a LEFT JOIN b ON a.f1=b.g1 WHERE b.g1 IS NULL


If you don't want duplicated values, the ***union*** is ... an union:


SELECT f1 FROM a UNION SELECT g1 FROM b



I assumed you desired SQL based solutions, not Excel based solutions.



Hoping it may help,
Vanderghast, Access MVP


wrote in message
ups.com...
Dear experts,

In excel, I have to find the differences between two sets of data.
Just like your set theory from grade 9 .

In one set, I have, say,

A
B
C
D
E
F

In the other set, I have,

D
E
F


Set one, minus set two is:
A
B
C


I would like to put the results of the minus into a new column.


In sql, you would just:

Query1
minus
Query2

But database is not an option here. The data is only in the
spreadsheet.


How can I do the same thing in excel?

Related to this, would be: Union, and Intersect.


Thanks