Is there a formula that will creat a unique list from a list of numbers??

[bat18]

I am wondering if there is a formula that will create a unique list of numbers from a set of numbers. I don't want to do a filter as I need the formula to update dynamically. For example

In column C a set of numbers will appear (from a formula that depends on a changing cell in cell A1), I then want in column D a formula that will make the list of numbers in column C into one list of unique entries.

Any advice would be appreciated

Thanks

 

[Fowmy]

Try below link:

http://www.mrexcel.com/forum/showthread.php?t=394522

 

[Ruddles]

This isn't a formula but it does what you're looking for:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Const sRow As Long = 1       [/SIZE][/FONT][FONT=Courier New][SIZE=1][COLOR=green]' source start row[/COLOR][/SIZE][/FONT]
[SIZE=1][FONT=Courier New]Const sColumn As Long = 3    '[COLOR=green] source column - 3=C[/COLOR][/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Const tColumn As Long = 4    [COLOR=green]' target column - 4=D[/COLOR][/FONT][/SIZE]
 
[FONT=Courier New][SIZE=1]Public Sub CopyUnique()[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Dim arr() As String[/SIZE][/FONT]
[SIZE=1][FONT=Courier New]Dim iLastRow As Long[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim jPtr As Long[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim kPtr As Long[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim sTemp As String[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]Columns(tColumn).ClearContents[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]iLastRow = Cells(Rows.Count, sColumn).End(xlUp).Row[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]ReDim arr(iLastRow) As String[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]For jPtr = 1 To iLastRow[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] arr(jPtr) = Cells(jPtr, sColumn)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Next jPtr[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]For jPtr = sRow To iLastRow - 1[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] For kPtr = jPtr + 1 To iLastRow[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   If arr(jPtr) = arr(kPtr) Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     arr(kPtr) = ""[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   ElseIf arr(jPtr) > arr(kPtr) Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     sTemp = arr(jPtr)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     arr(jPtr) = arr(kPtr)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     arr(kPtr) = sTemp[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   End If[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] Next kPtr[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Next jPtr[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]jPtr = sRow - 1[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]For kPtr = sRow To iLastRow[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] If arr(kPtr) <> "" Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   jPtr = jPtr + 1[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   Cells(jPtr, tColumn) = arr(kPtr)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] End If[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Next kPtr[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]MsgBox CStr(iLastRow - sRow) & " rows processed" & Space(10) & vbCrLf & vbCrLf _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    & CStr(jPtr - sRow) & " unique rows extracted", vbOkOnly + vbInformation[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]End Sub[/FONT][/SIZE]

Make a copy of your worksheet, then paste this into the worksheet's code module and run it.

 

[barry houdini]

Assuming positive numbers in C2:C100 leave D1 blank and put this array formula in D2

=IFERROR(SMALL(IF(C$2:C$100>D1,C$2:C$100),1),"")

confirm with CTRL+SHIFT+ENTER and copy down the column as far as you might need - when numbers are exhausted you get blanks

Assumes Excel 2007 or later

 

[bat18]

Hi

The macro works nearly to how I need it but it's far too complicated for me to manipulate. The 2 problems i have are, it copies the unique entries into D1 when I need it to start in D4.

Also a lot of my numbers have 000 at the start of them and I need them to stay there, so some how treat the numbers as text??

Any help would be great

thanks

 

[bat18]

=IFERROR() doesn't seem to be a valid function?? I am on Excel 2003 if that makes any difference.

Thanks

 

[barry houdini]

Also a lot of my numbers have 000 at the start of them and I need them to stay there, so some how treat the numbers as text??

Are they actually numbers or formatted as text? Do you care what order they are listed in?

 

[bat18]

They are numbers formatted as text and their order is unimportant. It is important they remain the same length (which means all the 000s at the front must be present)

 

[barry houdini]

You could try this formula in D2

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(C$2:C$100,SMALL(IF(COUNTIF(D$1:D1,C$2:C$100)+(C$2:C$100=""),"",ROW(C$2:C$100)-ROW(C$2)+1),1))))

confirmed with CTRL+SHIFT+ENTER and copied down

 

[bat18]

Works brilliantly thanks but it's crashing my excel at the minute!! Is there any chance that Ruddles macro could be adapted to keep the 000s at the start and start pasting in 4th row rather than 1st??

Thanks