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?
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:
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
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