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.
Download Excel *.xlsx file
Remove-duplicates-in-same-month.xlsx
Remove duplicates within same year in excel
Array formula in B38:
Array formula in C38:
Download excel sample file for this article.
Remove-duplicates-in-same-month.xls
(Excel 97-2003 Workbook *.xls)
Count unique distinct values within same week, month or year
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
Highlight duplicates with same date, week or month
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
Extracts the month as a number from an Excel date. Formula in cell C3: =MONTH(B3) Excel Function Syntax MONTH(serial_number) Arguments […]
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Vlookup – Return multiple unique distinct values
Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]
Extract a unique distinct list sorted from A to Z
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
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.
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.