SetIntersect
The SetIntersect() function returns the set-theoretic intersection of two ranges (mathematically denoted as ∩). In other words, SetIntersect() returns all distinct elements that are contained in both ranges.
Related functions: SetDifference, SetUnion
Syntax
SetIntersect (Range 1, Range 2, ByRow)
| Range 1 | The first range of cells in Excel |
| Range 2 | The second range of cells in Excel |
| ByRow | This determines whether to treat multi-column input as rows (for example side-by-side items as pairs) or as a list of discrete entities. The default is False – all items are treated as discrete entities. |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Return the distinct set of numbers that are common to two ranges
Example 2. Return the distinct set of sectors that are common to two ranges
Example 3. Return the distinct set of assets that are common to two multiple column ranges
Example 4. Use the ByRow parameter to treat multiple column rows as single entities
Example 5. Use the same range for both parameters
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Return the distinct set of numbers that are common to two ranges
The input ranges consist of two lists of numbers. The SetIntersect() function outputs the distinct elements that are in both Range 1 and Range 2.
| Range 1 | Range 2 | Range 1 ∩ Range 2 |
| 1 | 7 | =SetIntersect(A2:A6,B2:B5) |
| 3 | 8 | |
| 6 | 4 | |
| 4 | 5 | |
| 8 |
| Range 1 | Range 2 | Output |
| 1 | 7 | 4 |
| 3 | 8 | 8 |
| 6 | 4 | |
| 4 | 5 | |
| 8 |
Example 2. Return the distinct set of sectors that are common to two ranges
The input ranges consist of two lists of sectors. The SetIntersect() function lists those sectors in Range 1 that are also in Range 2.
| Range 1 | Range 2 | Range 1 ∩ Range 2 |
| Steel | Furnishing | =SetIntersect(A2:A5,B2:B6) |
| Brewers | Steel | |
| Banks | Food Products | |
| Food Products | Airlines | |
| Apparel Retailers |
| Range 1 | Range 2 | Output |
| Steel | Furnishing | Food Products |
| Brewers | Steel | Steel |
| Banks | Food Products | |
| Food Products | Airlines | |
| Apparel Retailers |
Example 3. Return the distinct set of assets that are common to two multiple column ranges
Range 1 and Range 2 cover multiple columns. The SetIntersect() function determines the distinct codes in both ranges.
| Range 1 | Range 2 | Range 1 ∩ Range 2 | |||
| CEB | PHO | XDU | LEF | NHP | =SetIntersect(A2:C6,D2:E4) |
| MBD | BSF | VRR | MBD | VRR | |
| KVJ | NHP | GTE | CEB | ||
| GMP | QGW | ||||
| YKG | QJU | ||||
| Range 1 | Range 2 | Output | |||
| CEB | PHO | XDU | LEF | NHP | CEB |
| MBD | BSF | VRR | MBD | VRR | MBD |
| KVJ | NHP | GTE | CEB | NHP | |
| GMP | QGW | VRR | |||
| YKG | QJU | ||||
Example 4. Use the ByRow parameter to treat multiple column rows as single entities
Range 1 and Range 2 consist of multiple columns. In this example, ‘ByRow’ is set to True. Therefore the entire row in a range is treated as an entity. The SetUnion() function determines the distinct pairs that are in both ranges.
| Range 1 | Range 2 | Range 1 ∩ Range 2 | ||
| CEB | BUY | PHO | BUY | =SetIntersect(A2:B6,C2:D5,TRUE) |
| MBD | SELL | GMP | HOLD | |
| KVJ | SELL | CEB | BUY | |
| GMP | HOLD | QGW | BUY | |
| YKG | HOLD | |||
| Range 1 | Range 2 | Output | |||
| CEB | BUY | PHO | BUY | CEB | BUY |
| MBD | SELL | GMP | HOLD | GMP | HOLD |
| KVJ | SELL | CEB | BUY | ||
| GMP | HOLD | QGW | BUY | ||
| YKG | HOLD | ||||
Example 5. Use the same range for both parameters
If the same range is passed to SetIntersect() twice, the function will return the distinct elements from that range in alphanumeric order. In this example, a single range of share codes is listed. SetIntersect() determines the unique list of share codes in the range.
| Range | Range ∩ Range |
| NHP | =SetIntersect(A2:A7,A2:A7) |
| CEB | |
| GMP | |
| KVJ | |
| GMP | |
| CEB |
| Range 1 | Output |
| NHP | CEB |
| CEB | GMP |
| GMP | KVJ |
| KVJ | NHP |
| GMP | |
| CEB |