Equivalent of Minus in Excel. Also Union, Intersect.

[dba_222]

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

 

[Tom Ogilvy]

Actually SQL is an option:

http://www.erlandsendata.no/english/index.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.

 

[Guest]

Try using an unmatched query.

 

[Héctor Miguel]

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.
=====

 

[Max]

One way using non-array formulas ..

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

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

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

Select C11, 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

 

[Herbert Seidenberg]

Another option is Advanced Filter.
Your data might look like this:
Lista
A
B
C
D
E
F

Listb
D
E
F

Criteria
Test
TRUE

Results
A
B
C

Select the first 7 cells and name them database.
Use Insert > Name > Define
Again select these 7 cells and
Insert > Name > Create > Top Row
Do the previous step for the 4 cells of the next region
and the 3 cells of the next region.
In the cell that says TRUE, enter this formula
=COUNTIF(Listb,Lista)=0
Data > Filter > Advanced Filter > Copy to another location >
enter location.
The other fields of the Filter window will already have the right info.

 

[Michel Walsh]

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