Comparing 2 colums and if match replace or provide value from the other column

Hi All,

I have one column with a data that can repeat for example:

Column A

markg
danielw
robertx
danielw
sylviaz
markg
...

on the other column I will have following information

Column C and D here values are only once
C D
markg | Mark Gold
danielw | Daniel Walsh
robertx | Robert Xmen
sylviaz | Sylvia Zet

I need big help to create function in VB (I use Excel 2010) that will take first colum A as range each cell from column A (A1,A2,...) will be compared with Column C to find a match where find the match replace with full name from column D.

Can you please help is it possible and where to start.

Thank you very much in advance for your help and time.

Regards,
Daniel

Thank you very much for your reply.

Can you please advise how to covert this to function () with 2 arguments 1 range to search second to compare.

For example :

Function test(s,c)

Dim r As Excel.Range

Set r = Range("a1:a10")

For Each c In r.Cells
strReplace = ""

On Error Resume Next
strReplace = Cells(Application.WorksheetFunction.Match(c.Text, Range("a:a"), 0), 4)
On Error GoTo 0

If strReplace <> "" Then c.Text = strReplace
Next c

End Function

Unfortunately at the moment give me Run-time error'1004'

Regards,
Daniel

Hi Shelton,

This is absolutely brilliant, thank you very much.

Can I ask for small change or explanation.

1. The function change the values in column C. In my version column C and D will be a key to check (single login and name assigned) and column A should updated.

As you can see in column A login can appear multiple time and in this case the full name should appear multiple times.

2. Can this function be build so i could pick column that need to be updated and the ranged of 2 columns against which will be checked.

3. As you can see I am very new to this and forum user can you explain how to rate your answer because it is brilliant and super fast.

Hi Shelton,

I agree my explanation is not clear (please see attached file) this is not live example but it will look similar.

I hope in the attached file it will be more clear.

Thank you again for your help.

Regards,
Daniel

Hi Shelton,

Thank you very much for your help, I have rated this post as Excellent.

Your knowledge about VB is great.

Regards,
Daniel