## Remove duplicates within same month or year

The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same month and year.

**Array formula in B15:**

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

**Array formula in C15:**

### Explaining formula in cell B15

#### Step 1 - Concatenate year, month and item value

The YEAR function returns the year of an Excel date, the MONTH function returns the MONTH of an Excel date.

YEAR($B$4:$B$12)&"-"&MONTH($B$4:$B$12)&"-"&$C$4:$C$12

becomes

YEAR({40183; 40184; 40185; 40186; 40218; 40188; 40220; 40190; 40556})&"-"&MONTH({40183; 40184; 40185; 40186; 40218; 40188; 40220; 40190; 40556})&"-"&{1124; 1126; 1131; 1126; 1126; 1160; 1126; 1155; 1155}

The & ampersand sign lets you concatenate values, in this case, row-wise.

YEAR({40183; 40184; 40185; 40186; 40218; 40188; 40220; 40190; 40556})&"-"&MONTH({40183; 40184; 40185; 40186; 40218; 40188; 40220; 40190; 40556})&"-"&{1124; 1126; 1131; 1126; 1126; 1160; 1126; 1155; 1155}

returns

{"2010-1-1124"; "2010-1-1126"; "2010-1-1131"; "2010-1-1126"; "2010-2-1126"; "2010-1-1160"; "2010-2-1126"; "2010-1-1155"; "2011-1-1155"}

#### Step 2 - Match concatenated values to identify duplicates

The MATCH function finds the relative position of a value in an array or cell range.

MATCH(YEAR($B$4:$B$12)&"-"& MONTH($B$4:$B$12)&"-"&$C$4:$C$12, YEAR($B$4:$B$12)& "-"&MONTH($B$4:$B$12)& "-"&$C$4:$C$12, 0)

becomes

MATCH{"2010-1-1124"; "2010-1-1126"; "2010-1-1131"; "2010-1-1126"; "2010-2-1126"; "2010-1-1160"; "2010-2-1126"; "2010-1-1155"; "2011-1-1155"}, {"2010-1-1124"; "2010-1-1126"; "2010-1-1131"; "2010-1-1126"; "2010-2-1126"; "2010-1-1160"; "2010-2-1126"; "2010-1-1155"; "2011-1-1155"}, 0)

and returns

{1; 2; 3; 2; 5; 6; 5; 8; 9}

#### Step 3 - Compare value to sequence

If number is equal to corresponding number in sequence the logical expression returns TRUE.

MATCH(YEAR($B$4:$B$12)&"-"& MONTH($B$4:$B$12)&"-"& $C$4:$C$12,YEAR($B$4:$B$12)&"-"& MONTH($B$4:$B$12)&"-"& $C$4:$C$12,0)=MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12))

becomes

{1; 2; 3; 2; 5; 6; 5; 8; 9}=MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12))

becomes

{1; 2; 3; 2; 5; 6; 5; 8; 9}={1;2;3;4;5;6;7;8;9}

and returns

{TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}

#### Step 4 - Replace TRUE with corresponding row number

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(MATCH(YEAR($B$4:$B$12)&"-"&MONTH($B$4:$B$12)&"-"&$C$4:$C$12, YEAR($B$4:$B$12)&"-"&MONTH($B$4:$B$12)&"-"&$C$4:$C$12, 0)=MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12)), MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12)), "")

becomes

IF({TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}=MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12)), MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12)), "")

becomes

IF({TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}, {1;2;3;4;5;6;7;8;9}, "")

and returns

{1;2;3;"";5;6;"";8;9}.

#### Step 5 - Extract k-th smallest row number

To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.

The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.

SMALL(IF(MATCH(YEAR($B$4:$B$12)&"-"&MONTH($B$4:$B$12)&"-"&$C$4:$C$12, YEAR($B$4:$B$12)&"-"&MONTH($B$4:$B$12)&"-"&$C$4:$C$12, 0)=MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12)), MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12)), ""), ROWS($A$1:A1))

becomes

SMALL({1;2;3;"";5;6;"";8;9}, ROWS($A$1:A1))

becomes

SMALL({1;2;3;"";5;6;"";8;9}, 1)

and returns 1.

#### Step 6 - Get value

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX($C$4:$C$12, SMALL(IF(MATCH(YEAR($B$4:$B$12)&"-"&MONTH($B$4:$B$12)&"-"&$C$4:$C$12, YEAR($B$4:$B$12)&"-"&MONTH($B$4:$B$12)&"-"&$C$4:$C$12, 0)=MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12)), MATCH(ROW($B$4:$B$12), ROW($B$4:$B$12)), ""), ROWS($A$1:A1)))

becomes

INDEX($C$4:$C$12, 1)

and returns 1/5/2010 in cell B15.

### Get Excel *.xlsx file

Remove-duplicates-in-same-month.xlsx

### Remove duplicates within same year in excel

**Array formula in B38:**

**Array formula in C38:**

### Get excel sample file for this article.

Remove-duplicates-in-same-month.xls

(Excel 97-2003 Workbook *.xls)

### Unique distinct values category

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]

Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]

This article demonstrates formulas that list unique distinct values if they contain a specified substring. Table of contents Extract unique […]

Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]

Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 2 Responses to “Remove duplicates within same month or year”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hi,

I was looking for a formula to remove duplicates within the same month and found your website. I think that your formula is really useful but when I try to apply it (I also tried with your sample), unfortunately I don't get any results and Excel gives me the "Value!" error. I also checked the format (columns for date and number)but it did not work. Do you know maybe more?

Kind regards.

I am experiencing the same issue that Amanda experiencing. The formula doesn't seem to work as written with your sample data. Can you provide a working sample in an excel file? Thank you.