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.


[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004[/TD]
[TD="bgcolor: yellow"]APPLE [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1005[/TD]
[TD="bgcolor: transparent"]Mango[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1006[/TD]
[TD="bgcolor: transparent"]Grapes[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1007[/TD]
[TD="bgcolor: transparent"]pumkin[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1008[/TD]
[TD="bgcolor: transparent"]tomato[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1009[/TD]
[TD="bgcolor: transparent"]juice[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1010[/TD]
[TD="bgcolor: transparent"]Milk[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004[/TD]
[TD="bgcolor: yellow"]APPLE
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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