formula to return next different value within column

yamapinews

New Member
Joined
Nov 28, 2016
Messages
9
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
720
Hope this helps.

Code:
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),"")
 
Last edited:

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
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)), " ")

aUnique
aa
bb
cc
ce
ek
k
k
e

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top