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
SetDifference (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 set 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. Determine the distinct list of numbers in one range and not another
Example 2. Determine the distinct list of sectors in one range and not another
Example 3. Determine the distinct list of assets in one multiple column range and not another
Example 4. Use the ByRow parameter to treat multiple column rows as single entities
Example 5. Use the same range for both parameters
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Example 1. Determine the distinct list of numbers in one range and not another
The input ranges consist of two lists of numbers. The SetDifference() function is first used to determine the distinct numbers that are in Range 1, but not in Range 2. The SetDifference() function is then used to determine the distinct numbers that are in Range 2, but not in Range 1.
| Range 1 | Range 2 | Difference Range 1, Range 2 | Difference Range 2, Range 1 |
| 1 | 7 | =SetDifference(A2:A7,B2:B5) | =SetDifference(B2:B5,A2:A7) |
| 5 | 5 | ||
| 6 | 4 | ||
| 4 | 2 | ||
| 5 | |||
| 8 |
| Range 1 | Range 2 | Output Range 1, Range 2 | Output Range 2, Range 1 |
| 1 | 7 | 1 | 2 |
| 5 | 5 | 6 | 7 |
| 6 | 4 | 8 | |
| 4 | 2 | ||
| 5 | |||
| 8 |
Example 2. Determine the distinct list of sectors in one range and not another
The input ranges consist of two lists of sectors. The SetDifference() function lists those sectors in Range 1 that are not in Range 2.
| Range 1 | Range 2 | Difference Range 1, Range 2 | Difference Range 2, Range 1 |
| Steel | Furnishing | =SetDifference(A2:A5,B2:B6) | =SetDifference(B2:B6,A2:A5) |
| Brewers | Steel | ||
| Banks | Food Products | ||
| Food Products | Airlines | ||
| Apparel Retailers |
| Range 1 | Range 2 | Output Range 1, Range 2 | Output Range 2, Range 1 |
| Steel | Furnishing | Banks | Airlines |
| Brewers | Steel | Brewers | Apparel Retailers |
| Banks | Food Products | Furnishing | |
| Food Products | Airlines | ||
| Apparel Retailers |
Example 3. Determine the distinct list of assets in one multiple column range and not another
Range 1 and Range 2 consist of multiple columns. The SetDifference() function determines which codes are in Range 1 but not in Range 2.
| Range 1 | Range 2 | Difference Range 1, Range 2 | |||
| CEB | PHO | XDU | LEF | NHP | =SetDifference(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 | BSF |
| MBD | BSF | VRR | MBD | VRR | GMP |
| KVJ | NHP | GTE | CEB | KVJ | |
| GMP | QGW | PHO | |||
| YKG | QJU | QGW | |||
| QJU | |||||
| XDU | |||||
| YKG | |||||
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 SetDifference() function determines which pairs are in Range 1 but not in Range 2.
| Range 1 | Range 2 | Difference Range 1, Range 2 | ||
| CEB | BUY | PHO | BUY | =SetDifference(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 | KVJ | SELL |
| MBD | SELL | GMP | HOLD | MBD | SELL |
| KVJ | SELL | CEB | BUY | YKG | HOLD |
| GMP | HOLD | QGW | BUY | ||
| YKG | HOLD | ||||
Example 5. Use the same range for both parameters
If the same range is passed to SetDifference() twice, the function will return an empty range.
| Range | Difference Range, Range |
| CEB | =SetDifference(A2:A7,A2:A7) |
| GMP | |
| KVJ | |
| GMP | |
| CEB | |
| NHP |
| Range | Output |
| CEB | |
| GMP | |
| KVJ | |
| GMP | |
| CEB | |
| NHP |