SetUnion
The SetUnion() function returns the set-theoretic union of two ranges (mathematically denoted by ∪). In other words, SetUnion() returns all distinct elements that are contained in either range or in both ranges.
Related functions: SetDifference, SetIntersect
Syntax
SetUnion (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 either or both ranges
Example 2. Determine the distinct list of sectors in either or both ranges
Example 3. Determine the distinct list of numbers in either or both multiple column ranges
Example 4. Use the ByRow parameter to treat multiple column rows as single entities
Example 5. Use SetUnion to list the unique values in a single range
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. Determine the distinct list of numbers in either or both ranges
The input ranges consist of two lists of numbers. The SetUnion() function outputs all the distinct numbers in Range 1 or Range 2 or both.
| Range 1 | Range 2 | Range 1 ∪ Range 2 |
| 1 | 7 | =SetUnion(A2:A5,B2:B4) |
| 3 | 5 | |
| 6 | 4 | |
| 4 |
| Range 1 | Range 2 | Output |
| 1 | 7 | 1 |
| 3 | 5 | 3 |
| 6 | 4 | 4 |
| 4 | 5 | |
| 6 | ||
| 7 |
Example 2. Determine the distinct list of sectors in either or both ranges
The input ranges consist of two lists of sectors. The SetUnion() function outputs all the distinct sectors in Range 1 or Range 2 or both.
| Range 1 | Range 2 | Range 1 ∪ Range 2 |
| Steel | Furnishing | =SetUnion(A2:A5,B2:B6) |
| Brewers | Steel | |
| Banks | Food Products | |
| Food Products | Airlines | |
| Apparel Retailers |
| Range 1 | Range 2 | Output |
| Steel | Furnishing | Airlines |
| Brewers | Steel | Apparel Retailers |
| Banks | Food Products | Banks |
| Food Products | Airlines | Brewers |
| Apparel Retailers | Food Products | |
| Furnishing | ||
| Steel |
Example 3. Determine the distinct list of numbers in either or both multiple column ranges
Range 1 and Range 2 consist of multiple columns. The SetUnion() function determines the distinct codes in either or both ranges.
| Range 1 | Range 2 | Range 1 U Range 2 | |||
| CEB | PHO | XDU | LEF | NHP | =SetUnion(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 SetUnion() function determines the distinct pairs in either or both ranges.
| Range 1 | Range 2 | Range 1 U Range 2 | ||
| CEB | BUY | PHO | BUY | =SetUnion(A2:B6,C2:D5,TRUE) |
| MBD | SELL | KVJ | HOLD | |
| KVJ | SELL | NHP | HOLD | |
| GMP | HOLD | QGW | BUY | |
| YKG | HOLD | |||
| Range 1 | Range 2 | Output | |||
| CEB | BUY | PHO | BUY | CEB | BUY |
| MBD | SELL | KVJ | HOLD | GMP | HOLD |
| KVJ | SELL | NHP | HOLD | KVJ | HOLD |
| GMP | HOLD | QGW | BUY | KVJ | SELL |
| YKG | HOLD | MBD | SELL | ||
| NHP | HOLD | ||||
| PHO | BUY | ||||
| QGW | BUY | ||||
| YKG | HOLD | ||||
Note: In this example ‘KVJ’ appears in the output twice. This is because ‘KVJ HOLD’ and ‘KVJ SELL’ are treated as discrete entities.
Example 5. Use SetUnion to list the unique values in a single range
If a single range is passed to SetUnion(), or a single range is passed to SetUnion() twice, the function will return the distinct elements from that range. In this example, a single range of share codes is listed. The SetUnion() function determines the unique list of share codes in the Range. The output is sorted alphanumerically.
| Range | Range U Range | Range U Range |
| NHP | =SetUnion(A2:A7) | =SetUnion(A2:A7,A2:A7) |
| CEB | ||
| GMP | ||
| KVJ | ||
| GMP | ||
| CEB |
Note:
The output is the same for both the single and duplicate input range. The former is very useful for quickly assessing the unique values in a list.
| Range | Output | Output |
| NHP | CEB | CEB |
| CEB | GMP | GMP |
| GMP | KVJ | KVJ |
| KVJ | NHP | NHP |
| GMP | ||
| CEB |