Author: Oscar Cronquist 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:

=IF(SUM(IF($A1:$F1<>"", 1/COUNTIF($A1:$F1, $A1:$F1), 0))>=COLUMNS($G1:G1), 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)), "") + CTRL + SHIFT + ENTER

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:

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.

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

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

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