replace code/id values with a name found in a list

[ebbo]

Hi, Couldn't think how I could describe this problem in my topic title.

Please see the attachment

I need a macro that will replace the codes in worksheet 1 column C.

To do this I will need to look up the id in Worksheet 2 column B and then replace it using the associated name in Column A of worksheet 2.

All help much appreciated!

[davegugg]

I believe this does what you are looking for:

Public Sub ebbo()

For i = 1 To Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    For j = 1 To Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        If Sheets("Sheet1").Cells(i, 3).Value = Sheets("Sheet2").Cells(j, 2).Value Then
            Sheets("Sheet1").Cells(i, 3).Value = Sheets("Sheet2").Cells(j, 1).Value
        End If
    Next j
Next i

End Sub

Let me know if I got that wrong.

You could also use a vlookup in column D and then paste the values over to column C, if you don't want to use vba.

[ebbo]

thats perfect, i tested it with this small sample

i will have to test it tomorrow at work too see if it works well with a much larger sample!

many thanks for your help!

[itmanager]

Hi,

I have something similar to ebbo but in my case the worksheet 1 column B have comma separated values which need to be looked up in worksheet 2 column B and replace it using the associated id in column A of worksheet 2.

Please see attached.

Any help much appreciated!

Thank you.

[HaHoBe]

Hi, itmanager,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Ciao,
Holger