ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Return unique number, skip blanks (https://www.excelbanter.com/excel-worksheet-functions/192675-return-unique-number-skip-blanks.html)

lleytte

Return unique number, skip blanks
 
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

Return unique number, skip blanks
 
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,RO W())))
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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.


Ron Rosenfeld

Return unique number, skip blanks
 
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

lleytte

Return unique number, skip blanks
 
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.

"Max" wrote:

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,RO W())))
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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.


lleytte

Return unique number, skip blanks
 
Thank you Thank you Thank you Thank you Thank you infinity

Works perfect!

"Ron Rosenfeld" wrote:

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


Ron Rosenfeld

Return unique number, skip blanks
 
On Thu, 26 Jun 2008 08:56:01 -0700, lleytte
wrote:

Thank you Thank you Thank you Thank you Thank you infinity

Works perfect!


Glad to help.

Thanks for the feedback.
--ron

Max

Return unique number, skip blanks
 
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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"lleytte" wrote:
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.




All times are GMT +1. The time now is 01:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com