SetIntersect
The SetIntersect() function returns the set-theoretic intersection of two ranges. In other words, it returns all distinct elements that appear in both ranges.
Related functions: SetDifference, SetUnion
Syntax:
Qx.SetIntersect(Range 1, Range 2, [ByRow])- In Excel with Quintessence add-in versions prior to 25.1, use:
SetIntersect() - In the Quintessence Editor (Studio) using Quintessence language, use:
SetIntersect() - In the Quintessence Editor (Studio) using Python language, use:
Qx.SetIntersect()
ⓘ See parameter descriptions
| Range 1 | The first range of data. |
| Range 2 | The second range of data. |
| ByRow (optional) | Determines whether to treat multi-column input as rows (e.g., side-by-side items as pairs) or as a set of discrete entities. |
Remarks:
- The default parameters are:
- ByRow:
False
- ByRow:
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Distinct numbers common to both ranges
Range 1 (A1:A6):
| 1 |
| 5 |
| 6 |
| 4 |
| 5 |
| 8 |
Range 1 (B1:B4):
| 7 |
| 5 |
| 4 |
| 3 |
Find all numbers Range 1 and Range 2 have in common:
=Qx.SetIntersect(A1:A6, B1:B4)Result:
| 4 |
| 5 |
> Example 2: Treat multiple columns as single entities
Range 1 (A1:B5):
| CEB | BUY |
| MBD | SELL |
| KVJ | SELL |
| GMP | HOLD |
| YKG | HOLD |
Range 2 (C1:D4):
| PHO | BUY |
| GMP | |
| CEB | BUY |
| QGW | BUY |
Find all unique rows in Range 1 that also appear in Range 2:
=Qx.SetIntersect(A1:B5, C1:D4, TRUE)Result:
| CEB | BUY |
> Example 3: Treat multiple columns as separate entities
Range 1 (A1:C5):
| CEB | PHO | XDU |
| MBD | SBSF | VRR |
| KVJ | NHP | |
| GMP | QCW | |
| YKG | QJU |
Range 2 (D1:E3):
| LEF | NHP |
| MBD | VRR |
| GTE | CEB |
Find all unique rows in Range 1 that also appear in Range 2:
=Qx.SetIntersect(A1:C5, D1:E3)Result:
| CEB |
| MBD |
| NHP |
| VRR |