| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Unique Records ... No Blanks | Excel Discussion (Misc queries) | |||
| number columns skip blanks | Excel Discussion (Misc queries) | |||
| skip blanks | Excel Worksheet Functions | |||
| Skip Over Blanks | Excel Discussion (Misc queries) | |||
| Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) | |||
Linear Mode
