formula to return next different value within column

[yamapinews]

Such as
Col A
a
a
b
c
c
e
k
k
e

I need result in column B (duplicate value is not my concern, just jump to next different value)
ColB:
a
b
c
e
k
e

 

[Takae]

Hope this helps.

Sub test1()
Dim Dic, i As Long, LR As Long
Dim buf As String, keys
Set Dic = CreateObject("scripting.Dictionary")
On Error Resume Next
LR = cells(Rows.count, 1).End(xlUp).Row
For i = 1 To LR
    buf = cells(i, 2).Value
    Dic.Add buf, buf
Next
keys = Dic.keys
For i = 0 To Dic.count - 1
    cells(i + 2, 2) = keys(i)
Next
Set Dic = Nothing
End Sub

Formula!
C2 = =IF(COUNTIF($A$2:A2,A2)>1,C1,C1+1)
B2= =IFERROR(VLOOKUP(ROW()-1,IF({TRUE,FALSE},$C$1:$C$10,$A$1:$A$10),2,FALSE),"")

 

[Mike Szczesny]

Can you use something like this. This formula captures the unique values in a range. Place a header Unique in C1. Place formula in C2. You need use absolute reference in C1. I enclosed the =iferror part to remove error messages. Use Cntrl+Shift+Enter, then copy down. Here is the formula

=IFERROR(INDEX($A$1:$A$9,MATCH(0,COUNTIF($C$1:C1,$A$1:$A$9),0)), " ") a Unique a a b b c c c e e k k k e <tbody> </tbody>

<tbody>
</tbody>