Kidd asks: Hi Oscar,Need a formula to count identical numbers in two columns but items must be in same row (position). 12 15 8 8 good count 1 22 19 7 22 for 22 not count cause is not in same […]

## Archive for the ‘Duplicate values’ Category

Kidd asks: Hi Oscar,Need a formula to count identical numbers in two columns but items must be in same row (position). 12 15 8 8 good count 1 22 19 7 22 for 22 not count cause is not in same […]

NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, play with it, and come to grips with its basics. Your example was clear and very useful, and this has […]

cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand this to the next step. Per your initial example we can see that Jeff in Row 1 and Shaun in […]

You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want to find duplicates among filtered visible values. Example, the table below has filtered values from January 2012. Create a new […]

I have written a few posts about two related tables and today I am going to show you how to work with three related tables: Lookups in three related tables and return multiple values Filter unique distinct values from three […]

In a previous post I described how to do lookups in a related table. In this post I am going to show you how to extract unique distinct values and duplicates from a related table. Unique distinct values Duplicate values […]

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small “d” + a running number for the duplicates which are duplicated the same. If no duplicates only to put in […]

In this post I am going to describe how to filter duplicate and unique distinct values from a really large dataset. Both array formula and advanced filter are too slow. In this example, cell range A1:A1 000 000 contains seven […]

Yesterday I demonstrated how to create a list of files in a folder and subfolders. Today I am going to show you how to identify duplicate files from the list we created yesterday. This can be useful if you have […]

Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key (like rows 3:4 or rows 8:10). I'd like to sum data in column D and to consider same key rows […]

The array formula in this blog article has no "Fuzzy logic" nor vlookup function. But it can return names or words arranged differently and with minor misspellings just like a user defined function with "Fuzzy logic". There are too many […]

Formula in cell G3: =IF(COUNTIFS($B$3:B3, B3, $C$3:C3, C3, $D$3:D3, D3, $E$3:E3, E3)>1, "Duplicate", "") + ENTER Copy cell G3 and paste down as far as needed. How the formula works in cell G13 Step 1 - Understand how relative and […]

This article describes how to highlight duplicate records and filter duplicate records. There is one record in each column, cell range B2:E5. See picture below. The array formula and conditional formatting formula in this article contain "Countifs", a function introduced […]

This article describes how to filter duplicate rows. The array formula in this article contains countifs, a function introduced in excel 2007. Countifs applies criteria to cells across multiple ranges and counts the number of times all criteria are met. Array […]

This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($A$1:$A$30, $A1, $B$1:$B$30, $B1, $C$1:$C$30, $C1)>1 + ENTER How to use conditional formatting formula Select cells A1:C30 Click "Home" tab Click […]

Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback with countif is that it is not able to coerce a range. I would like an unique list based on […]

I might have missunderstood Aamers question: I have a sheet with 3000 rows of invoice dates that are out of order. There could be a maximum of 6 dates and a minimum of 1 in each row. The invoice dates […]

Aamer asks: I have a sheet with 3000 rows of invoice dates that are out of order. There could be a maximum of 6 dates and a minimum of 1 in each row. The invoice dates are in row A1:F1 […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it the dates are overlapping as well. Is there anyway to work this out? eg Serial | Start date | End […]

AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate values in each rows. SAMPLE COLUMN A contains these data: 3M - Asia 3M South America 3M - Africa 3M […]

Here is a picture of a closed workbook How to count unique distinct text values in a closed workbook This formula works with possible blank cells in range A1:A6. =SUM(IF(ISBLANK('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6), 0, IF(MATCH('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, 'C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, 0)=ROW('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6)-MIN(ROW('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6))+1, 1, […]

Here is a picture of the closed workbook. There can´t be any blank cells or text values in your range. As you can see, there are five unique distinct values (1, 2, 3, 5 and 6) and one duplicate value […]

Highlight duplicates on same date Conditional formatting formula: =SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))>1 Highlight duplicates on same week Conditional formatting formula: =SUMPRODUCT(--($B16&"-"&YEAR($C16)&"-"&$D16=$B16:$B$16&"-"&YEAR($C16:$C$16)&"-"&$D16:$D$16))>1 Highlight duplicates on same month Conditional formatting formula: =SUMPRODUCT(--(YEAR($C29)&"-"&MONTH($C29)&"-"&$D29=YEAR($C29:$C$29)&"-"&MONTH($C29:$C$29)&"-"&$D29:$D$29))>1 Download excel sample file for this article. Highlight-duplicates-within-same-date-week-month-year.xls (Excel 97-2003 Workbook *.xls) Functions […]

Filter duplicates on same date Array formula in F3: =INDEX($C$3:$C$11, SMALL(IF(MATCH($C$3:$C$11&$D$3:$D$11, $C$3:$C$11&$D$3:$D$11, 0)<>ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed. Array formula in G3: =INDEX($D$3:$D$11, SMALL(IF(MATCH($C$3:$C$11&$D$3:$D$11, $C$3:$C$11&$D$3:$D$11, 0)<>ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, […]

Table of contents Remove duplicates within same month and year in excel Remove duplicates within same year in excel Remove duplicates within same month and year in excel Array formula in B15: =INDEX(Date, SMALL(IF(MATCH(YEAR(Date)&"-"&MONTH(Date)&"-"&Item, YEAR(Date)&"-"&MONTH(Date)&"-"&Item, 0)=ROW(Date)-MIN(ROW(Date))+1, ROW(Date)-MIN(ROW(Date))+1, ""), ROW(A1))) + […]

In a previous post we created a unique distinct list of dates and data removing any duplicates on same date. In this post we will remove duplicates within same week. Array formula in B15: =INDEX($B$4:$B$12, SMALL(IF(MATCH(YEAR($A$4:$A$12)&"-"&$A$4:$A$12&"-"&$C$4:$C$12, YEAR($A$4:$A$12)&"-"&$A$4:$A$12&"-"&$C$4:$C$12, 0)=ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1, ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1, ""), […]

Question: Column A1 Has dates Column B as data A1 : 1/1/2010 : 5000 A2 : 2/1/2010 : 4000 A3 : 1/1/2010 : 5000 A4 : 5/1/2010 : 5000 A5 : 6/1/2010 : 4000 From column B the values which […]

Array formula in B12: =INDEX(tbl, MIN((IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1))), MATCH(0, COUNTIF($B$11:B11, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(SEARCH($D$9, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))=0)+(COUNTIF(tbl, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(ISNUMBER(SEARCH($D$9, tbl))), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))<=1), 0), 1) + CTRL […]

Array formula in B12: =INDEX(tbl, MIN((IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9), ROW(tbl)-MIN(ROW(tbl))+1))), MATCH(0, COUNTIF($B$11:B11, INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9)*(COUNTIF(tbl, tbl)>1), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(LEFT(INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9)*(COUNTIF(tbl, tbl)>1), ROW(tbl)-MIN(ROW(tbl))+1)), , 1), LEN($D$9))<>$D$9), 0), 1) + CTRL + SHIFT + ENTER copied down as far […]

Validate "All duplicate values" list (D2:D7) Array formula in E13: =AND(COUNTIF(list, all_duplicates)=COUNTIF(all_duplicates, all_duplicates)) + CTRL + SHIFT + ENTER Validate "Values having duplicates" list (F3:F4) Array formula in E17: =AND(AND(COUNTIF(list, Duplicates)>1), AND(COUNTIF(Duplicates, Duplicates)=1)) + CTRL + SHIFT + ENTER Named […]

Array formula in B15: =INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)>1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(0, ISNONTEXT(INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)>1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+COUNTIF($B$14:B14, INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)>1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(COUNTIF(tbl, INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)>1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))<2), 0), 1) + CTRL + SHIFT + ENTER copied […]

This blog post describes how to extract duplicates sorted from A to Z from two different columns combined. Array formula in D2: =IF(MIN(IF((COUNTIF(List1, List1)+COUNTIF(List2, List1))*NOT(COUNTIF($D$1:D1, List1))>1, COUNTIF(List1, "<"&List1)+COUNTIF(List2, "<"&List1), MAX(COUNTIF(List2, "<"&List2)+COUNTIF(List1, "<"&List2)+1)))<MIN(IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))*NOT(COUNTIF($D$1:D1, List2))>1, COUNTIF(List2, "<"&List2)+COUNTIF(List1, "<"&List2), MAX(COUNTIF(List1, "<"&List1)+COUNTIF(List2, […]

Question: How do I get the largest and smallest unique and duplicate value? Answer: Max duplicate value, formula in C22: =MAX(IF(COUNTIF(List, List)>1, List, )) + CTRL + SHIFT + ENTER Min duplicate value, formula in C24: =MIN(IF(COUNTIF(List, List)>1, List, "")) […]

Count duplicate distinct values Formula in E18: =ABS(SUM(IF(COUNTIF(A2:A16, A2:A16)>1, (1/COUNTIF(A2:A16, A2:A16))-1, 0))) + CTRL + SHIFT + ENTER Download excel example file Count duplicate distinct values in a column in excel.xls (Excel 97-2003 Workbook *.xls) Functions: ABS(number) Returns the absolute […]

Question: How do I filter duplicate rows and sort by date? Answer: Column A and B are the original list. Column D and E are the filtered duplicate list sorted by date. See picture below. Array formula in D2: =INDEX(Date, […]

Question: How do I highlight duplicate values in two ranges combined? Answer: Range 1, A1:D4, named tbl Range 2, A6:D9, named tbl1 Conditional formatting formula in A1:D4: =IF(ROW(A1)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, MIN(ROW(A1)-MIN(ROW(tbl))), MAX(COLUMN(tbl))), A1)+COUNTIF(OFFSET(tbl, MIN(ROW(A1)-MIN(ROW(tbl))), 0, 1, MIN(COLUMN(A1))), A1), COUNTIF(OFFSET(tbl, […]

Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in A2: =MIN(IF(COUNTIF(List, List)>1, List, ""))=A2 How to implement conditional formatting formula to your workbook in excel 2007 For example, your […]

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula in C2: =INDEX(List1, MATCH(MIN(IF(COUNTIF(List1, List1)*IF(COUNTIF(C1:$C$1, List1)=1, 0, 1)>1, COUNTIF(List1, "<"&List1), "")), IF(COUNTIF(List1, List1)>1, COUNTIF(List1, "<"&List1), ""), 0)) + CTRL + […]

Question: How do I highlight duplicate values in a range (A1:D4) using conditional formatting? Answer: Conditional formatting formula: =IF(ROW(G5)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, ROW(G5)-MIN(ROW(tbl)), MAX(COLUMN(tbl))-MIN(COLUMN(tbl))+1), G5)+COUNTIF(OFFSET(tbl, 0, 0, ROW(G5)-MIN(ROW(tbl))+1, COLUMN(G5)-MIN(COLUMN(tbl))+1), G5), COUNTIF(OFFSET(tbl, 0, 0, 1, COLUMN(G5)-MIN(COLUMN(tbl))+1), G5))>1 Highlight all values having […]

Overview Inspired by a comment in this post Unique values from multiple columns using array formulas, I created an array formula that extracts duplicates (repeated values) from a range spanning multiple columns and rows. Excel 2007 array formula in A2: […]

Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array formula? Answer: Excel 2007 array formula in D2: =IFERROR(IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF(D1:$D$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)), INDEX(List2, MATCH(0, COUNTIF(D1:$D$1, List2)+IF((COUNTIF(List2, […]

Table of contents Extract a list of duplicates from a column using array formula Extract duplicates using conditions Extract duplicates with exceptions Extract a list of duplicates from a column using array formula Excel 2007 array formula in C2: =INDEX($A$2:$A$20, […]

Question: I have two ranges or lists where I want to extract duplicates? Answer: Excel 2007 formula in D2: =IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF(D1:$D$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)), INDEX(List2, MATCH(0, COUNTIF(D1:$D$1, List2)+IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))>1, 0, 1), 0))), "") + CTRL + […]