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

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

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### 2 Responses to “Remove duplicates and sort dates by each row in excel”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.