Thread: Equivalent of Minus in Excel. Also Union, Intersect.
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.access.queries,microsoft.public.excel.querydao,microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
  
Posts: 9,221
Default Equivalent of Minus in Excel. Also Union, 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



Reply With QuoteReply With Quote