Lookup in same column

[harshab6]

Hi Experts,

I have nearly 1000 account numbers In Column A and accountdescription of those in column B, Every day I get the 100of Account number in mail and some accounts are reputative Ihave to update account number in Column A search the description incolumn B and update the same Is it possible to lookup the same Column,I need amacro or Formula when the new account number are updated in column A theformula automatically searches the description in column B and update thosedescription for repetitive account numbers in column B if the account numbersare not repetitive the row should be blank.

A	B
1004	APPLE
1005	Mango
1006	Grapes
1007	pumkin
1008	tomato
1009	juice
1010	Milk
1004	APPLE
1000

<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG23Mar06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, 1).Value
    [COLOR="Navy"]Else[/COLOR]
        Dn.Offset(, 1).Value = .Item(Dn.Value)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[harshab6]

Thanks for help.It worked like a Charm