Series Alphabetically Based On Unique List

[muhammad susanto]

hi all..

how to make series alphabetically based on unique adjacent cell...

order/desired	data
A	apple
B	apricot
C	banana
C	banana
D	apricot
D	apricot
D	apricot
etc..	avocado
	blackberry
	blackberry
	blackberry

<thead>
</thead><tbody>
</tbody>

my target in column order/desired
any help greatly appreciated..

m.susanto

 

[muhammad susanto]

order/desired	data
1	apple
2	apricot
3	banana
3	banana
4	apricot
4	apricot
4	apricot
etc..	avocado
	blackberry
	blackberry
	blackberry

<tbody>
</tbody>

i thinkthis more simple...change alphabet with series number

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG16Apr00
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c, Temp [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Temp [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Dn.Value = Temp.Value [COLOR="Navy"]Then[/COLOR] c = c + 1
    [COLOR="Navy"]End[/COLOR] If
    Dn.Offset(, -1).Value = c
    [COLOR="Navy"]Set[/COLOR] Temp = Dn
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jmacleary]

You can do this without using VBA. Assuming your table starts at A1, put a value of 1 in cell A2. Put the following formula in cell A3 and copy down as necessary: =IF(B3=B2,A2,A2+1)