Remove duplicates and sort dates by each row in excel
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
Related posts:
How to sort cells filtered by two dates
How to sort text cells filtered by two dates, part 2
Excel udf: Remove duplicates from a large dataset
Recommended categories
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:
Remember to change bolded values if your formula is entered in another cell than B2.