On Wed, 25 Jun 2008 16:09:01 -0700, lleytte
wrote:

All I have is excell and I'm not allowed to download anything.

Column M has a list of numbers including repeats.

Coumn EC already displays unique numbers using the formula - I don't
remember how I made this work.

=IF(COUNTIF($M$5,M5)=1,M1,"")

I want to display the unique numbers consecutively in Column ED without
blanks.

How can I do this? I don't want to delete any cells because I don't want to
move information in a section below it.

desired result
Column M Column EC Column ED
45518 45518 45518
42850 42850 42850
43850 43850 43850
42850 42580
45518 45520
42580 42580
43850
42850
45518
45520 45520
43850
42850
45518

Sorry if the columns don't line up.

Please help.

You could use a User Defined Function:

To enter this, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

The function returns an unsorted array of unique values. One way to use this
is to enter the formula:

INDEX(uniquevalues($M$1:$M$100),ROWS($1:1))

into ED1 and fill down as needed.

If you fill down too far, you will start to get #REF! errors. To not see them,
you could use the more involved formula:

=IF(ISERR(INDEX(uniquevalues($M$1:$M$100),ROWS($1: 1))),"",INDEX(uniquevalues($M$1:$M$100),ROWS($1:1) ))


=================================
Option Explicit
Function UniqueValues(rg As Range) As Variant
Dim cCol As Collection
Dim vRes() As Variant
Dim c As Range
Dim i As Long

Set cCol = New Collection

On Error Resume Next
For Each c In rg
cCol.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0

ReDim vRes(1 To cCol.Count)
For i = 1 To cCol.Count
vRes(i) = cCol(i)
Next i
UniqueValues = vRes
End Function
====================================
--ron