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

 

[bosco_yip]

Or try this non array formula ,

=LOOKUP("zzzz",IF({1,0},"",INDEX(C:C,MATCH(,INDEX(ISNA(MATCH(C$2:C$100,D$1:D1,))-1,),)+1)))

Regards

 

[bosco_yip]

If you wanted to use short formula

1] This short array formula :

{=INDEX(C:C,MIN(IF(COUNTIF(E$1:E1,C$2:C$100),60000,ROW(C$2:C$100))))&""}

Or,

2] If C1 keep empty, this shorter array formula can also be used :

{=INDEX(C:C,MATCH(,COUNTIF(F$1:F1,C$2:C$100),)+1)&""}

Regards

 

[Ruddles]

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

Try this:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Const sRow As Long = 1  [/SIZE][SIZE=1][COLOR=green]   ' source start row[/COLOR][/SIZE][/FONT]
[SIZE=1][FONT=Courier New]Const sColumn As Long = 3  [COLOR=green]' source column[/COLOR][/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Const tColumn As Long = 4  [COLOR=green]' target column[/COLOR][/FONT][/SIZE]
[SIZE=1][FONT=Courier New][COLOR=red][B]Const tRow As Long = 3[/B][/COLOR]     [COLOR=green]' target start row[/COLOR][/FONT][/SIZE]
 
[FONT=Courier New][SIZE=1]Public Sub CopyUnique()[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]' routine for sorting column sColumn to column tColumn[/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][COLOR=red][B]Range("D1:D" & CStr(iLastRow)).NumberFormat = "@"[/B][/COLOR][/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 [COLOR=red][B]+ tRow[/B][/COLOR], 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[COLOR=blue] [B]+ 1[/B][/COLOR]) & " rows processed" & Space(10) & vbCrLf & vbCrLf _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     & CStr(jPtr - sRow [B][COLOR=blue]+ 1[/COLOR][/B]) & " unique rows extracted", vbOKOnly + vbInformation[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]End Sub[/FONT][/SIZE]

The bits in red are the bits which have changed. The bits in blue are corrections. Just replace the entire procedure with this new code.