## Remove duplicates and sort dates by each row in excel

*Article last updated on August 15, 2017*

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 are in row A1:F1 and I would like the ordered, unique distinct list to be in G1:L1, and also display a blank once the data runs out. How do I alter your formula to accomplish this?

**Answer #2:**

Some random dates**:
**

Row 2 contains a duplicate (29-May-1969).

I am using cell range H1:M1 instead**.
**

Values in H1:M1 are sorted from smallest to largest and next row and next.. Cell M2 is blank. 29-May-1969 has a duplicate.

**Array formula in H1:**

Copy cell H1 and paste it to the right as far as needed. Then copy all formula cells in row 1 and paste them down as far as needed.

(Here is answer #1:Excel udf: Remove duplicates from a large dataset)

**Explaining the formula**

IF(SUM(IF($A1:$F1<>"", 1/COUNTIF($A1:$F1, $A1:$F1), 0))>=COLUMNS($G1:G1), ... ,"") creates blank cells when data runs out.

($A1:$F1<>"") is a criterion to avoid blank cells from being sorted.

COUNTIF($G1:G1, $A1:$F1)=0 is a criterion to filter unique distinct dates.

SMALL(IF(($A1:$F1<>"")*(COUNTIF(**$G1:G1**, $A1:$F1)=0), $A1:$F1, ""), 1) sorts all unique distinct cells except blanks using dynamic relative and absolute cell references (**bolded**).

MATCH(SMALL(IF(($A1:$F1<>"")*(COUNTIF($G1:G1, $A1:$F1)=0), $A1:$F1, ""), 1), IF($A1:$F1<>"", (COUNTIF($G1:G1, $A1:$F1)=0)*$A1:$F1, 1), 0) returns the relative position of each sorted unique distinct date.

INDEX($A1:$F1, MATCH(SMALL(IF(($A1:$F1<>"")*(COUNTIF($G1:G1, $A1:$F1)=0), $A1:$F1, ""), 1), IF($A1:$F1<>"", (COUNTIF($G1:G1, $A1:$F1)=0)*$A1:$F1, 1), 0)) returns a date.

### Download excel file

unique distinct dates horisontal without blanks1.xls (~700 KB)

(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.

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

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

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

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

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

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

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

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

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

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]

Lookup two index columns in excel

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

### 2 Responses to “Remove duplicates and sort dates by each row 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

Hi, I need the exact same thing, but my data are in colums, instead lines.

I have a sheet with 1 colum of dates are out of order and whit blanks. The dates are in col D:D and I would like the ordered and blanks removeds, unique distinct list to be in other sheet. How to do it?

Gava, Ronaldo

This formula sorts values, removes blanks and creates unique distinct values.

Replace List with your cell range in column D.

Array formula in cell B2:

$B$1:B1, List)=0)*(List<>""), 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), (COUNTIF(List, "<"&List)+1)*(List<>""), 0))Remember to change bolded values if your formula is entered in another cell than B2.