## Extract duplicate records

This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array formula which is demonstrated below. The function that makes this all possible is the COUNTIFS function, introduced in Excel 2007.

The COUNTIFS function evaluates criteria to cells across multiple ranges and counts the number of times all criteria are met.

### Array formula in cell A30:

Copy cell A30 and paste it to the right as far as needed. Then copy cells and paste them down as far as needed.

### How this formula works in cell A30

*Step 1 - Find duplicates*

=INDEX($A$2:$D$25, SMALL(IF(**COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1**, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1), ROW(A1)), COLUMN(A1)

COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1

returns this array:

{False, False, True, False, False, False, True, True, True, True, True, True, True, False, True, True, True, True, True, False, True, True, True, True}

*Step 2 - Use array to extract row numbers*

IF({False, False, True, False, False, False, True, True, True, True, True, True, True, False, True, True, True, True, True, False, True, True, True, True}, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1)

becomes

IF({False, False, True, False, False, False, True, True, True, True, True, True, True, False, True, True, True, True, True, False, True, True, True, True}, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24})

and returns this array:

{False, False, 3, False, False, False, 7, 8, 9, 10, 11, 12, 13, False, 15, 16, 17, 18, 19, False, 21, 22, 23, 24}

*Step 3 - Return the k-th smallest value*

SMALL(IF(COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1), ROW(A1))

becomes

SMALL({False, False, 3, False, False, False, 7, 8, 9, 10, 11, 12, 13, False, 15, 16, 17, 18, 19, False, 21, 22, 23, 24}, ROW(A1))

and returns 3.

*Step 4 - Return a value of the cell at the intersection of a particular row and column*

=INDEX($A$2:$D$25, SMALL(IF(COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1), ROW(A1)), COLUMN(A1))

becomes

=INDEX($A$2:$D$25, 3, COLUMN(A1))

becomes

=INDEX($A$2:$D$25, 3, 1) and returns "Sample1"