DISTINCTROWS2
Description
Combines a set of ranges into a single range while removing any rows that are duplicates.
DISTINCTROWS2 evaluates text and instance values as not distinct from each other.
When the supplied range contains both an instance value and a text string that are
the same, the function returns 1 row, the instance value. DISTINCTROWS2 is
case-insensitive.
Syntax
DISTINCTROWS2(
range1
, [
range2
], ...)
- range1: The first range of rows.
- range2: The second range of rows.
Example
This is the original spreadsheet:
A | B | C | D | E | |
|---|---|---|---|---|---|
1
| Heading1 | Heading2 | Heading3 | Heading4 | |
2
| A | 1 | B | 2 | |
3
| B | 2 | 2 | B | |
4
| C | 3 | C | 5 | |
5
| D | 4 | D | 4 |
The result of
=DISTINCTROWS2(A2:B5,D2:E5)
where the formula is in cell A6,
is:
A | B | C | D | E | |
|---|---|---|---|---|---|
7
| A | 1 | |||
8
| B | 2 | |||
9
| C | 3 | |||
10
| D | 4 | |||
11
| 2 | B | |||
13
| C | 5 |
Notes
- This function takes any number of ranges. Typically each range has a similar row structure, but this is not required.
- The function adds a row to the final result if a row containing the exact same values in the exact same order is not already present in the result. This enables you to combine rows from different ranges and remove duplicate rows. The function considers all values in a row when determining if a row is a duplicate or not.
- This function is intended for use in array formulas.
Related Functions
UNIQUE2