Question: How do i find numerical duplicates in a range spanning multiple columns?

how to identify dupes

Answer:

Array formula in B26:

=SMALL(IF(COUNTIF(tbl, tbl)>1, tbl, ""), ROW(1:1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in D26:

=SMALL(IF((COUNTIF(tbl, tbl)>1)*NOT(COUNTIF(D25:$D$25, tbl)), tbl, ""), 1) + CTRL + SHIFT + ENTER copied down as far as needed.

Explaining array formula in cell B26

=SMALL(IF(COUNTIF(tbl, tbl)>1, tbl, ""), ROW(1:1))

tbl is a named range. B4:F23

Step 1 - Count values

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNTIF(tbl, tbl) becomes

COUNTIF(55, 47, 86, 37, 38; 79, 44, 91, 30, 33; 42, 49, 73, 55, 10; 80, 9, 90, 43, 12; 20, 28, 4, 31, 20; 23, 83, 25, 86, 15; 68, 30, 46, 95, 6; 78, 59, 78, 96, 35; 68, 19, 94, 98, 29; 36, 53, 1, 68, 34; 89, 7, 54, 32, 56; 70, 26, 62, 35, 5; 48, 63, 41, 69, 3; 46, 97, 90, 0, 89; 17, 97, 92, 72, 62; 11, 3, 28, 49, 24; 20, 13, 64, 71, 67; 99, 20, 50, 4, 35; 58, 27, 45, 61, 1; 37, 13, 53, 1, 45, 55, 47, 86, 37, 38; 79, 44, 91, 30, 33; 42, 49, 73, 55, 10; 80, 9, 90, 43, 12; 20, 28, 4, 31, 20; 23, 83, 25, 86, 15; 68, 30, 46, 95, 6; 78, 59, 78, 96, 35; 68, 19, 94, 98, 29; 36, 53, 1, 68, 34; 89, 7, 54, 32, 56; 70, 26, 62, 35, 5; 48, 63, 41, 69, 3; 46, 97, 90, 0, 89; 17, 97, 92, 72, 62; 11, 3, 28, 49, 24; 20, 13, 64, 71, 67; 99, 20, 50, 4, 35; 58, 27, 45, 61, 1; 37, 13, 53, 1, 45)

becomes

{2, 1, 2, 2, 1; 1, 1, 1, 2, 1; 1, 2, 1, 2, 1; 1, 1, 2, 1, 1; 4, 2, 2, 1, 4; 1, 1, 1, 2, 1; 3, 2, 2, 1, 1; 2, 1, 2, 1, 3; 3, 1, 1, 1, 1; 1, 2, 3, 3, 1; 2, 1, 1, 1, 1; 1, 1, 2, 3, 1; 1, 1, 1, 1, 2; 2, 2, 2, 1, 2; 1, 2, 1, 1, 2; 1, 2, 2, 2, 1; 4, 2, 1, 1, 1; 1, 4, 1, 2, 3; 1, 1, 2, 1, 3; 2, 2, 2, 3, 2}

Step 2 - Identify duplicate values

{2, 1, 2, 2, 1; 1, 1, 1, 2, 1; 1, 2, 1, 2, 1; 1, 1, 2, 1, 1; 4, 2, 2, 1, 4; 1, 1, 1, 2, 1; 3, 2, 2, 1, 1; 2, 1, 2, 1, 3; 3, 1, 1, 1, 1; 1, 2, 3, 3, 1; 2, 1, 1, 1, 1; 1, 1, 2, 3, 1; 1, 1, 1, 1, 2; 2, 2, 2, 1, 2; 1, 2, 1, 1, 2; 1, 2, 2, 2, 1; 4, 2, 1, 1, 1; 1, 4, 1, 2, 3; 1, 1, 2, 1, 3; 2, 2, 2, 3, 2}>1

> identifies numbers lager than 1. The returning value is TRUE or FALSE.

becomes

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

Step 3 - Filter duplicate values

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

IF(COUNTIF(tbl, tbl)>1, tbl, "")

becomes

IF({True, False, True, True, False; False, False, False, True, False; False, True, False, True, False; False, False, True, False, False; True, True, True, False, True; False, False, False, True, False; True, True, True, False, False; True, False, True, False, True; True, False, False, False, False; False, True, True, True, False; True, False, False, False, False; False, False, True, True, False; False, False, False, False, True; True, True, True, False, True; False, True, False, False, True; False, True, True, True, False; True, True, False, False, False; False, True, False, True, True; False, False, True, False, True; True, True, True, True, True}, {55, 47, 86, 37, 38; 79, 44, 91, 30, 33; 42, 49, 73, 55, 10; 80, 9, 90, 43, 12; 20, 28, 4, 31, 20; 23, 83, 25, 86, 15; 68, 30, 46, 95, 6; 78, 59, 78, 96, 35; 68, 19, 94, 98, 29; 36, 53, 1, 68, 34; 89, 7, 54, 32, 56; 70, 26, 62, 35, 5; 48, 63, 41, 69, 3; 46, 97, 90, 0, 89; 17, 97, 92, 72, 62; 11, 3, 28, 49, 24; 20, 13, 64, 71, 67; 99, 20, 50, 4, 35; 58, 27, 45, 61, 1; 37, 13, 53, 1, 45}, "")

and becomes

{55, "", 86, 37, ""; "", "", "", 30, ""; "", 49, "", 55, ""; "", "", 90, "", ""; 20, 28, 4, "", 20; "", "", "", 86, ""; 68, 30, 46, "", ""; 78, "", 78, "", 35; 68, "", "", "", ""; "", 53, 1, 68, ""; 89, "", "", "", ""; "", "", 62, 35, ""; "", "", "", "", 3; 46, 97, 90, "", 89; "", 97, "", "", 62; "", 3, 28, 49, ""; 20, 13, "", "", ""; "", 20, "", 4, 35; "", "", 45, "", 1; 37, 13, 53, 1, 45}

Step 4 - Return the smallest value

SMALL(array,k)
Returns the k-th smallest row number in this data set.

=SMALL(IF(COUNTIF(tbl, tbl)>1, tbl, ""), ROW(1:1))

becomes

=SMALL({55, "", 86, 37, ""; "", "", "", 30, ""; "", 49, "", 55, ""; "", "", 90, "", ""; 20, 28, 4, "", 20; "", "", "", 86, ""; 68, 30, 46, "", ""; 78, "", 78, "", 35; 68, "", "", "", ""; "", 53, 1, 68, ""; 89, "", "", "", ""; "", "", 62, 35, ""; "", "", "", "", 3; 46, 97, 90, "", 89; "", 97, "", "", 62; "", 3, 28, 49, ""; 20, 13, "", "", ""; "", 20, "", 4, 35; "", "", 45, "", 1; 37, 13, 53, 1, 45}, ROW(1:1))

becomes

=SMALL({55, "", 86, 37, ""; "", "", "", 30, ""; "", 49, "", 55, ""; "", "", 90, "", ""; 20, 28, 4, "", 20; "", "", "", 86, ""; 68, 30, 46, "", ""; 78, "", 78, "", 35; 68, "", "", "", ""; "", 53, 1, 68, ""; 89, "", "", "", ""; "", "", 62, 35, ""; "", "", "", "", 3; 46, 97, 90, "", 89; "", 97, "", "", 62; "", 3, 28, 49, ""; 20, 13, "", "", ""; "", 20, "", 4, 35; "", "", 45, "", 1; 37, 13, 53, 1, 45}, 1)

and returns 1

Explaining array formula in cell D26

=SMALL(IF((COUNTIF(tbl, tbl)>1)*NOT(COUNTIF(D25:$D$25, tbl)), tbl, ""), 1)

tbl is a named range. B4:F23

Step 1 - Count values

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNTIF(tbl, tbl) becomes

COUNTIF(55, 47, 86, 37, 38; 79, 44, 91, 30, 33; 42, 49, 73, 55, 10; 80, 9, 90, 43, 12; 20, 28, 4, 31, 20; 23, 83, 25, 86, 15; 68, 30, 46, 95, 6; 78, 59, 78, 96, 35; 68, 19, 94, 98, 29; 36, 53, 1, 68, 34; 89, 7, 54, 32, 56; 70, 26, 62, 35, 5; 48, 63, 41, 69, 3; 46, 97, 90, 0, 89; 17, 97, 92, 72, 62; 11, 3, 28, 49, 24; 20, 13, 64, 71, 67; 99, 20, 50, 4, 35; 58, 27, 45, 61, 1; 37, 13, 53, 1, 45, 55, 47, 86, 37, 38; 79, 44, 91, 30, 33; 42, 49, 73, 55, 10; 80, 9, 90, 43, 12; 20, 28, 4, 31, 20; 23, 83, 25, 86, 15; 68, 30, 46, 95, 6; 78, 59, 78, 96, 35; 68, 19, 94, 98, 29; 36, 53, 1, 68, 34; 89, 7, 54, 32, 56; 70, 26, 62, 35, 5; 48, 63, 41, 69, 3; 46, 97, 90, 0, 89; 17, 97, 92, 72, 62; 11, 3, 28, 49, 24; 20, 13, 64, 71, 67; 99, 20, 50, 4, 35; 58, 27, 45, 61, 1; 37, 13, 53, 1, 45)

becomes

{2, 1, 2, 2, 1; 1, 1, 1, 2, 1; 1, 2, 1, 2, 1; 1, 1, 2, 1, 1; 4, 2, 2, 1, 4; 1, 1, 1, 2, 1; 3, 2, 2, 1, 1; 2, 1, 2, 1, 3; 3, 1, 1, 1, 1; 1, 2, 3, 3, 1; 2, 1, 1, 1, 1; 1, 1, 2, 3, 1; 1, 1, 1, 1, 2; 2, 2, 2, 1, 2; 1, 2, 1, 1, 2; 1, 2, 2, 2, 1; 4, 2, 1, 1, 1; 1, 4, 1, 2, 3; 1, 1, 2, 1, 3; 2, 2, 2, 3, 2}

Step 2 - Select a unique value

NOT(COUNTIF(D25:$D$25, tbl))

D25:$D$25 is a relative and absolute cell reference.

becomes

NOT(COUNTIF("Duplicate values occuring only once in the list below", {55, 47, 86, 37, 38; 79, 44, 91, 30, 33; 42, 49, 73, 55, 10; 80, 9, 90, 43, 12; 20, 28, 4, 31, 20; 23, 83, 25, 86, 15; 68, 30, 46, 95, 6; 78, 59, 78, 96, 35; 68, 19, 94, 98, 29; 36, 53, 1, 68, 34; 89, 7, 54, 32, 56; 70, 26, 62, 35, 5; 48, 63, 41, 69, 3; 46, 97, 90, 0, 89; 17, 97, 92, 72, 62; 11, 3, 28, 49, 24; 20, 13, 64, 71, 67; 99, 20, 50, 4, 35; 58, 27, 45, 61, 1; 37, 13, 53, 1, 45}))

becomes

{True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True}

Step 3 - Multiply arrays from step 1 and step 2

(COUNTIF(tbl, tbl)>1)*NOT(COUNTIF(D25:$D$25, ))

becomes

{2, 1, 2, 2, 1; 1, 1, 1, 2, 1; 1, 2, 1, 2, 1; 1, 1, 2, 1, 1; 4, 2, 2, 1, 4; 1, 1, 1, 2, 1; 3, 2, 2, 1, 1; 2, 1, 2, 1, 3; 3, 1, 1, 1, 1; 1, 2, 3, 3, 1; 2, 1, 1, 1, 1; 1, 1, 2, 3, 1; 1, 1, 1, 1, 2; 2, 2, 2, 1, 2; 1, 2, 1, 1, 2; 1, 2, 2, 2, 1; 4, 2, 1, 1, 1; 1, 4, 1, 2, 3; 1, 1, 2, 1, 3; 2, 2, 2, 3, 2}*{True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True; True, True, True, True, True}

becmoes

{2, 1, 2, 2, 1; 1, 1, 1, 2, 1; 1, 2, 1, 2, 1; 1, 1, 2, 1, 1; 4, 2, 2, 1, 4; 1, 1, 1, 2, 1; 3, 2, 2, 1, 1; 2, 1, 2, 1, 3; 3, 1, 1, 1, 1; 1, 2, 3, 3, 1; 2, 1, 1, 1, 1; 1, 1, 2, 3, 1; 1, 1, 1, 1, 2; 2, 2, 2, 1, 2; 1, 2, 1, 1, 2; 1, 2, 2, 2, 1; 4, 2, 1, 1, 1; 1, 4, 1, 2, 3; 1, 1, 2, 1, 3; 2, 2, 2, 3, 2}

Step 4 - Filter duplicate values from tbl

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

IF((COUNTIF(tbl, tbl)>1)*NOT(COUNTIF(D25:$D$25, tbl)), tbl, "")

becomes

IF({2, 1, 2, 2, 1; 1, 1, 1, 2, 1; 1, 2, 1, 2, 1; 1, 1, 2, 1, 1; 4, 2, 2, 1, 4; 1, 1, 1, 2, 1; 3, 2, 2, 1, 1; 2, 1, 2, 1, 3; 3, 1, 1, 1, 1; 1, 2, 3, 3, 1; 2, 1, 1, 1, 1; 1, 1, 2, 3, 1; 1, 1, 1, 1, 2; 2, 2, 2, 1, 2; 1, 2, 1, 1, 2; 1, 2, 2, 2, 1; 4, 2, 1, 1, 1; 1, 4, 1, 2, 3; 1, 1, 2, 1, 3; 2, 2, 2, 3, 2}, {55, 47, 86, 37, 38; 79, 44, 91, 30, 33; 42, 49, 73, 55, 10; 80, 9, 90, 43, 12; 20, 28, 4, 31, 20; 23, 83, 25, 86, 15; 68, 30, 46, 95, 6; 78, 59, 78, 96, 35; 68, 19, 94, 98, 29; 36, 53, 1, 68, 34; 89, 7, 54, 32, 56; 70, 26, 62, 35, 5; 48, 63, 41, 69, 3; 46, 97, 90, 0, 89; 17, 97, 92, 72, 62; 11, 3, 28, 49, 24; 20, 13, 64, 71, 67; 99, 20, 50, 4, 35; 58, 27, 45, 61, 1; 37, 13, 53, 1, 45} , "")

becomes

{55, 47, 86, 37, 38; 79, 44, 91, 30, 33; 42, 49, 73, 55, 10; 80, 9, 90, 43, 12; 20, 28, 4, 31, 20; 23, 83, 25, 86, 15; 68, 30, 46, 95, 6; 78, 59, 78, 96, 35; 68, 19, 94, 98, 29; 36, 53, 1, 68, 34; 89, 7, 54, 32, 56; 70, 26, 62, 35, 5; 48, 63, 41, 69, 3; 46, 97, 90, 0, 89; 17, 97, 92, 72, 62; 11, 3, 28, 49, 24; 20, 13, 64, 71, 67; 99, 20, 50, 4, 35; 58, 27, 45, 61, 1; 37, 13, 53, 1, 45}

Step 5 - Return the smallest value

=SMALL(IF((COUNTIF(tbl, tbl)>1)*NOT(COUNTIF(D25:$D$25, tbl)), tbl, ""), 1)

becomes

=SMALL({55, 47, 86, 37, 38; 79, 44, 91, 30, 33; 42, 49, 73, 55, 10; 80, 9, 90, 43, 12; 20, 28, 4, 31, 20; 23, 83, 25, 86, 15; 68, 30, 46, 95, 6; 78, 59, 78, 96, 35; 68, 19, 94, 98, 29; 36, 53, 1, 68, 34; 89, 7, 54, 32, 56; 70, 26, 62, 35, 5; 48, 63, 41, 69, 3; 46, 97, 90, 0, 89; 17, 97, 92, 72, 62; 11, 3, 28, 49, 24; 20, 13, 64, 71, 67; 99, 20, 50, 4, 35; 58, 27, 45, 61, 1; 37, 13, 53, 1, 45}, 1)

and returns 1.

Named ranges

tbl (A2:A12)
What is named ranges?

To identify text duplicates, see this post:
Extract duplicates from a range using excel array formula

Download excel sample file for this tutorial.

how-to-find-duplicates.xls

(Excel 97-2003 Workbook *.xls)

Functions in this article

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference)
returns the row number of a reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

SMALL(array,k)
Returns the k-th smallest row number in this data set.