Excel - macro or vba code?

[patty123467]

How can I create a code that will compare sheets and replace another column when there is a unique identifier found. Sheet 1 and 2, column A will contain a unique ID but they wont be in the same order. Sheet 1 and 2 will have column B containing non-unique data. I need for sheet 1 column B to be replaced with the text in sheet 2 column b when there is a unique ID found in column a on sheets 1 and 2. See below example.

Sheet 1</SPAN>

Test1</SPAN></SPAN>	Test2</SPAN></SPAN>
z123</SPAN></SPAN>	apple </SPAN></SPAN>
z1234</SPAN></SPAN>	pear</SPAN></SPAN>
z12345</SPAN></SPAN>	banana </SPAN></SPAN>

<TBODY>
</TBODY>

Sheet 2</SPAN>

Test1</SPAN></SPAN>	Test2</SPAN></SPAN>
z1234</SPAN></SPAN>	red</SPAN></SPAN>
z123</SPAN></SPAN>	blue</SPAN></SPAN>
z12345</SPAN></SPAN>	yellow</SPAN></SPAN>

<TBODY>
</TBODY>

Apple would change to blue</SPAN>
Pear would change to red</SPAN>
banana would change to yellow</SPAN>

 

[Domski]

Can you not just do a vlookup?

Dom

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG24Jul25
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[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
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng2
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(Dn.Value)
            R.Offset(, 1).Value = Dn.Offset(, 1)
        [COLOR="Navy"]Next[/COLOR] R
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[patty123467]

Would V lookup replace a column for me? Also can I do that when comparing sheet1 and sheet2?

 

[patty123467]

Mick - where in excel would I enter that code? VBA? Can you give me the steps?

 

[MickG]

Try this:-
To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.


On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick

 

[patty123467]

Okay so this seems to me working, howevery, can you fix the code so that I am acutually not using the next column. What I want is for column E to be replaced. So, in my example below, Test 2 is acually not next to Test 1. Really I am looking at duplicated in Test 1 (column A) and replacing Column E. Hopefully that makes sense! Basically its the same as my example below but there are acually a bunch of columns in between Test 1 and Test 2.

Sheet 1</SPAN>

Test1</SPAN></SPAN>	Test2</SPAN></SPAN>
z123</SPAN></SPAN>	apple </SPAN></SPAN>
z1234</SPAN></SPAN>	pear</SPAN></SPAN>
z12345</SPAN></SPAN>	banana </SPAN></SPAN>

<TBODY>
</TBODY>

Sheet 2</SPAN>

Test1</SPAN></SPAN>	Test2</SPAN></SPAN>
z1234</SPAN></SPAN>	red</SPAN></SPAN>
z123</SPAN></SPAN>	blue</SPAN></SPAN>
z12345</SPAN></SPAN>	yellow

<TBODY>
</TBODY>

 

[MickG]

Change the line below ,where shown in red.

 R.Offset(,[B][COLOR=#ff0000] 4[/COLOR][/B]).Value = Dn.Offset(, 1)

 

[patty123467]

What does the 4 represent?

 

[patty123467]

Nevermind - I figured that out. Thank you very much for your assistance!