Aamer asks:

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:

Using the array formula in post: How to extract a unique distinct list from a column in excel is too slow, in my opinon.

Advanced filter does not remove the blanks or work with a cell range containing multiple columns.

I found some useful vba code here: http://www.j-walk.com/ss/excel/tips/tip47.htm and created this simple udf:

Public Function UniqueValues(rng As Variant) As Variant
Dim Test As New Collection
Dim Value As Variant
Dim Item As Variant
Dim temp() As Variant
ReDim temp(0)
rng = rng.Value
On Error Resume Next
For Each Value In rng
  If Len(Value) > 0 Then Test.Add Value, CStr(Value)
Next Value
On Error GoTo 0
For Each Item In Test
  temp(UBound(temp)) = Item
  ReDim Preserve temp(UBound(temp) + 1)
Next Item
UniqueValues = Application.Transpose(temp)
End Function

This udf doesn´t sort the values or display blanks once the data runs out.

How to sort the values (Excel 2007):

  1. Select values
  2. Copy values (Ctrl + C)
  3. Right click on an empty column and select "Paste Special.."
  4. Click "Values"
  5. Click OK!
  6. Click "Data" tab on the ribbon
  7. Click "Sort smallest to largest" button

How to implement user defined function in excel

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste the above user defined function
  4. Exit visual basic editor
  5. Select a sheet
  6. Select a cell range
  7. Type =UniqueValues($A$1:$F$3000) into formula bar and press CTRL+SHIFT+ENTER

Download excel sample file for this tutorial.

unique distinct dates large data set.xls (1,1 MB)
(Excel 97-2003 Workbook *.xls)

Related posts:

Excel udf:Filter duplicates in a large dataset in excel

Excel udf: Count unique distinct values in a large dataset

Count unique distinct values in a large dataset with a date criterion

Remove duplicates on same date in excel

Remove duplicates within same month or year in excel