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?

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