## Remove duplicates on same date in excel

*Article last updated on December 02, 2010*

**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 are duplicate i want to remove only those which are on same date .

Remove Duplicates from Column B by comparing from Column A Which has date. as, if the duplication is occurred on next date it should not be counted as not a duplicate because the same data is entered in the next date.

Example : Customer has Purchased Item A on 1/1/2010

Customer has purchase Item A on 1/1/2010

I want to assume that On 1/1/2010 Item A has a duplicate

If the customer has purchased Item A on 2/1/2010 . this is not duplicate because the item is purchased on the next date.

**Answer:**

**Array formula in D2:**

**Array formula in E2:**

### Download excel sample file for this article.

remove dupes on same date.xls

(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

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROW(**reference**)** returns the rownumber of a reference

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

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

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

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

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 an unique distinct […]

Create a unique distinct alphabetically sorted list

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

### 3 Responses to “Remove duplicates on same date in excel”

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

[...] in Excel on Apr.13, 2010. Email This article to a Friend In a previous post we created a unique distinct list of dates and data removing any duplicates on same [...]

I have 4K rows - does it break if there are too many rows - it seems to stop functioning for me.

Jackie,

I recommend using formulas as described in the post if you need a "dynamic" calculation like in a dashboard or similar.

Follow instructions below if you want to simply remove duplicate records.

Select cell range A1:B5.

Go to tab "Data" on the ribbon.

Click "Remove Duplicates" button

Click OK

Click OK button