## How to find duplicates in a range spanning multiple columns?

*Article updated on January 31, 2011*

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

**Answer:**

**Array formula in B26:**

**Array formula in D26:**

### 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(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.

(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.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 3 Responses to “How to find duplicates in a range spanning multiple columns?”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Dear Get Digital Help,

I have a similar table to the one above that I need to check for duplicates. The only difference between my table and the example table is that mine contains more rows of data per column. I have tried adapting the instructions and formula to my table but with no success. I even downloaded the example table but as soon as I made any changes to it, the formulas no longer work. Maybe some more detailed information regarding what each operator in the formula does would be helpful. That way if I understand what each does, I can make whatever changes are necessary to adapt them to my table.

Thanks,

Dave Pruiett

Dave Pruiett,

I don´t think the operators are the problem. But I will write a formula explanation.

Does the range contain only numerical values?

Did you adjust the named range (tbl)?

Did you adjust the relative and absolute reference in the formula (bolded):

=SMALL(IF((COUNTIF(tbl, tbl)>1)*NOT(COUNTIF(

D25:$D$25, tbl)), tbl, ""), 1)Did you press and hold CTRL + SHIFT and then press Enter after typing the formula in the formula bar?

Did you copy the first cell (B26 or D26) (Ctrl + C) and then paste (Ctrl + V) down as far as needed?

Thank you so much for the reply Oscar. I'm embarrassed to say that I must not have pressed and held CTRL+SHIFT+ENTER after entering the formula because once I did, the formula worked.

Best regards,

Dave Pruiett