Union and Intersect
A couple of rarely used features of worksheet formulas are Unions and Intersects.
Unions
Most Excel users have probably have come across Union - the combination of ranges to be treated as one range. eg. =SUM(A1, A5:A11, C10)
If you start by typing the formula =SUM( then hold down CTRL and select ranges, Excel automatically defines a Union for you.
The ranges are separated by a comma. The comma is referred to as a Union operator.
I’ve seen Unions used for a Grand Total by summing Sub Totals
Intersects
Just like the Union operator is a comma, the Intersect operator is a space.
When you separate ranges with a space then the range used is where they intersect. eg. =A1:C1 B1:B3 will return the same result as =B1
I’ve never seen Intersects used in the wild. I’ve only recently discovered it myself.
It’s interesting to note that in this sample changing Feb to Jan =SUM((Apple, Banana) Jan) does not result in a Circular Reference.
jkpieterse:
I knew about the intersect and union operators, but I didn’t know you could nest them!
17 January 2005, 6:08 amRob van Gelder:
I can’t help but feel there is hidden potential with intersects. It looks very similar to a lookup.
The Operator Precedence orders Intersect before Union which is why I used a bracket.
17 January 2005, 2:40 pmross:
when i saw this, i started to thing about using Intersect with row and coloum, could be slow though.
18 January 2005, 4:58 amOla Sandstrom:
Great!
Intersect by a blank separator is new to me. I’ll definitly use it.
Ola
This is also new to me: http://www.nsl.com/k/excel.k
19 January 2005, 6:40 pmjay:
First, nice & useful blog - thanks!
Second, I’m not convinced the union operator is really an operator and not just a piece of microsoft rhetoric. I’ve never seen it used in a context where it couldn’t ALSO be interpreted as a comma separating arguments (e.g., in a SUM function). For example, the following array formula, which uses an intersect operator, works:
{SUM(IF((A1:A10 A1:10)=”a”,1,0))}
whereas this one, which uses the so-called “union operator” returns a #VALUE! error
{SUM(IF((A1:A5,A6:10)=”a”,1,0))}
This is enourmously frustrating because an authentic union operator could be extremely useful when doing, e.g., complex database lookups.
3 February 2005, 3:43 pmRob van Gelder:
Jay,
Thanks Jay - nice spotting
=A1:A5, A8:A10 is valid, but always returns #VALUE!
A pseudo-union.
Rob
3 February 2005, 5:53 pmJim:
This looks like it has a lot of potential for me. I have created named ranges which represent columns of unique parameters, and named ranges which represent groups of rows to partition the source of the data. What I would like to do is copy the several cells which are the interseection of the parameter range with a particular group of rows. I.e. column AB is named param1 and rows 10 to 20 are named datasetA and are several readings I want to do some computing on. What I have been experimenting with, without success, is using something like:
intersect(range(”param1″),range(”datasetA”)).copy worksheets(”sheet2″).range(”A1″)
Suggestions?
1 December 2005, 7:22 pmThanks, Jim.
Benny:
This is an informative page, with almost maliciously incorrect terminology.
The [space] character is, indeed, an “intersect operator.”
The [comma] character is IN NO WAY a mathematical “union operator.” It is in fact “set addition,” which SQL calls “union all.”
A union returns a SET (in this case, a SET of cells). Set Addition returns a MULTISET. The comma is Set Addition, because using Comma with the same cell twice returns TWO (of the same) cell. For instance:
3=COUNT(C2,C2,C2)=COUNT((C2,C2,C2))
If comma were really a union operator, these counts would return 1!
A less useless example is taking a “plus” shaped range:
6=COUNT(F4:H4,G3:G5)
Obviously, that plus-shaped range has 5 cells. Cell G4 is counted twice, PRECISELY because the comma-character is NOT a union operator.
How sad, that Excel does not support mathematical union (without using VBA).
21 July 2008, 7:52 am-b
Rob van Gelder:
Benny
You may be correct, but Union is the label as assigned by Microsoft.
ptgIsect: Intersection (ptg=0Fh)
Computes the intersection of the top two operands. This is the Excel space operator.
ptgUnion: Union (ptg=10h)
Computes the union of the top two operands. This is the Excel comma operator.
ptgRange: Range (ptg=11h)
21 July 2008, 4:52 pmComputes the minimal bounding rectangle of the top two operands. This is the Excel colon operator.