DISTINCTROWS
Description
Combines a set of ranges into a single range while removing any rows that are duplicates. DISTINCTROWS 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. DISTINCTROWS is case-sensitive.
Syntax
DISTINCTROWS(
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
=DISTINCTROWS(A2:B5,D2:E5)
where the formula is in cell A7, 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
UNIQUE