SetDifference
The SetDifference() function returns the set-theoretic difference between two ranges. In other words, it returns all distinct elements that are in the first range but not in the second range.
Related functions: SetIntersect, SetUnion
Syntax:
Qx.SetDifference(Range 1, Range 2, [ByRow])- In Excel with Quintessence add-in versions prior to 25.1, use:
SetDifference() - In the Quintessence Editor (Studio) using Quintessence language, use:
SetDifference() - In the Quintessence Editor (Studio) using Python language, use:
Qx.SetDifference()
ⓘ 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 in each range
Range 1 (A1:A6):
| 1 |
| 5 |
| 6 |
| 4 |
| 5 |
| 8 |
Range 1 (B1:B4):
| 7 |
| 5 |
| 4 |
| 3 |
Find all numbers in Range 1 not present in Range 2:
=Qx.SetDifference(A1:A6, B1:B4)Result:
| 1 |
| 6 |
| 8 |
Find all numbers in Range 2 not present in Range 1:
=Qx.SetDifference(B1:B4, A1:A6)Result:
| 2 |
| 7 |
> 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 | HOLD |
| CEB | BUY |
| QGW | BUY |
Find all unique rows in Range 1 not present in Range 2:
=Qx.SetDifference(A1:B5, C1:D4, TRUE)Result:
| KVJ | SELL |
| MBD | SELL |
| YKG | HOLD |