ListDupes

Description

ListDupes displays the rows that are duplicated in the input data. It is essentially the opposite of Excel’s UNIQUE function which lists the rows that occur only once (with cell formula =UNIQUE(X,,TRUE))

Syntax

ListDupes(X) where X is an input table or column vector. The function will return the duplicate rows but only once (ie not all the occurrences).

Source Code

ListDupes = LAMBDA(
    data, // input table (any rows/cols), or a single column/row of values
    LET(
        // build a per-row comparison key
        keys, IF(
            COLUMNS(data) = 1,
            TOCOL(data),
            BYROW(data, LAMBDA(r, TEXTJOIN(CHAR(1), FALSE, r)))
        ),
        // number of unique keys
        uniqueKeys, UNIQUE(keys),
        // count number of each unique item and filter them out
        counts, MAP(uniqueKeys, LAMBDA(k, SUM(--(keys = k)))),
        dupKeys, FILTER(uniqueKeys, counts > 1),
        // pull back the first occurrence of each duplicated row
        IFERROR(
            CHOOSEROWS(data, XMATCH(dupKeys, keys)),
            "No duplicate rows found"
        )
    )
);

NumUnique