Series Alphabetically Based On Unique List

[muhammad susanto]

hi all..

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


[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]order/desired[/TH]
[TH]data[/TH]
[/TR]
</thead><tbody> [TR]
[TD]A[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]apricot[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]apricot[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]apricot[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]apricot[/TD]
[/TR]
[TR]
[TD]etc..[/TD]
[TD]avocado[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]blackberry[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]blackberry[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]blackberry[/TD]
[/TR]
</tbody>[/TABLE]

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

m.susanto

 

[muhammad susanto]

[TABLE="class: cms_table_tableizer-table"]
<tbody>[TR="class: cms_table_tableizer-firstrow"]
[TH="align: center"]order/desired[/TH]
[TH="align: center"]data[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]apricot[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]apricot[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]apricot[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]apricot[/TD]
[/TR]
[TR]
[TD]etc..[/TD]
[TD]avocado[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]blackberry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]blackberry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]blackberry[/TD]
[/TR]
</tbody>[/TABLE]
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)