How to find duplicates in a range spanning multiple columns?
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.
Related posts:
Find missing numbers in a range from multiple columns
How to find duplicates in a Microsoft Excel list
Filter duplicates from two columns combined and sort from A to Z using array formula in excel
Extract a list of duplicates from two columns combined using array formula in excel
Recommended categories
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