Return unique number, skip blanks

[lleytte]

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.

 

[Max]

One simple, fast, non-array way to get it up
Assume source data running in M1 down
In EC1:
=IF(M1="","",IF(COUNTIF(M$1:M1,M1)>1,"",ROW()))
In ED1:
=IF(ROW()>COUNT(EC:EC),"",INDEX(M:M,SMALL(EC:EC,ROW())))
Select & copy EC1:ED1 down to cover the max expected extent of data in col
M, eg down to ED1000? Col ED will return the exact results that you seek

 

[Ron Rosenfeld]

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

 

[lleytte]

Thanks. I did try, but I must have done something wrong. All that shows up in
column EC are numbers that are unique but are not any of the numbers listed
in column M.

Column ED gives does give me two unique numbers consecutively and without
blanks, but only two of the six unique numbers are listed.

I'll keep going over it to see if I can catch my mistake.

 

[lleytte]

Thank you Thank you Thank you Thank you Thank you infinity

Works perfect!

 

[Ron Rosenfeld]

Thank you Thank you Thank you Thank you Thank you infinity

Works perfect!

Glad to help.

Thanks for the feedback.
--ron

 

[Max]

From what you describe, your source data in col M probably starts in row 4 down
Here's how you can easily adapt it to suit
In EC4:
=IF(M4="","",IF(COUNTIF(M$4:M4,M4)>1,"",ROW()))
Leave EC1:EC3 blank

In ED4:
=IF(ROWS($1:1)>COUNT(EC:EC),"",INDEX(M:M,SMALL(EC:EC,ROWS($1:1))))
Select & copy EC4:ED4 down to cover the max expected extent of data in col
M, eg down to ED100? Col ED should return the exact results that you seek, or
my name isn't Max. Let me know here ..