## Archive for Duplicate values

Extract a list of alphabetically sorted duplicates based on a condition

The following image shows you a data set in column B and C. The formula in cell E2 extracts a […]

Extract duplicate values with exceptions

The formula in cell E2 returns duplicate values from column A but values in column C are excluded from the […]

Filter duplicate values using critera

The following worksheet allows you to search for duplicate names using a date range (cell B1 and B2) and a […]

Easily identify groups of duplicate rows

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

Filter unique distinct and duplicate values from a large data set in excel 2007

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]

Remove duplicates from a large dataset [UDF]

Aamer asks: I have a sheet with 3000 rows of invoice dates that are out of order. There could be […]

Filter duplicate words from a cell range [UDF]

AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]

Filter duplicates within same date, week or month in excel

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 […]

Remove duplicates within same month or year

Table of contents Remove duplicates within same month and year in excel Remove duplicates within same year in excel Remove […]

Remove duplicates in same week in excel

In a previous post we created a unique distinct list of dates and data removing any duplicates on same date. […]

Remove duplicates on same date in excel

Question: Column A1 Has dates Column B as data A1 : 1/1/2010 : 5000 A2 : 2/1/2010 : 4000 A3 […]

Filter duplicate values in a range using “contain” condition in excel

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, […]

Filter duplicate text values in a range using “begins with” criterion in excel

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, […]

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 […]

Extract duplicate text values from a range containing both numerical and text values

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, […]

Filter duplicates from two columns combined and sort from A to Z using array formula in excel

This blog post describes how to extract duplicates sorted from A to Z from two different columns combined. Array formula […]

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]

Filter duplicate rows and sort by date using array formula in excel

Question: How do I filter duplicate rows and sort by date? Answer: Column A and B are the original list. […]

Extract a list of alphabetically sorted duplicates from a column

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula […]

Extract duplicates from a range using excel array formula

Overview Inspired by a comment in this post Unique values from multiple columns using array formulas, I created an array […]

Extract a list of duplicates from three columns combined using array formula in excel

Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array […]

Extract a list of duplicates from a column

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Extract a list of duplicates from two columns combined using array formula in excel

Question: I have two ranges or lists where I want to extract duplicates? Answer: Excel 2007 formula in D2: =IFERROR(IFERROR(INDEX(List1, […]

Create a list of duplicates where adjacent cell value meets a condition

Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]

How to extract a list of duplicates from a column in excel

Edit: A better shorter easier array formula in this post. Here is a solution (Column C) to extract duplicates from […]

Extract duplicates and their adjacent values

Question: How do i only put duplicate cell values from a list into another new list and their adjacent values? […]