I'm sure this can be done so pls assist...

I have to worksheets.

The first one has two columns: Old Label and New Label.

The 2nd worksheet has several columns and i want to do a search in a
specific column (column O). The search should be the text in the other sheet
(Old Label) and all matches should be replaced with the New Label.

Please help!

AFAIK this has to be done with a macro. To avoid problems with ranges I
create Range names Old_Labels, New_Labels and Labels on the sheet you want
to update. Copy the code into a VB Module, ALT + F8, Insert Module and Paste
the code into the module. Return to the workbook and run the code. ALT + F8,
select code and click Run.

Sub test()
Set rng = Range("Labels")
Set rngOld = Range("Old_Labels")
Set rngNew = Range("New_Labels")

For Each c In rng
x = WorksheetFunction.Match(c, rngOld, 0)
If IsError(x) Then
'do nothing
Else
c.Value = WorksheetFunction.Index(rngNew, WorksheetFunction.Match(c,
rngOld, 0))
End If
Next
End Sub

Regards
Peter


"ant1983" wrote:

I'm sure this can be done so pls assist...

I have to worksheets.

The first one has two columns: Old Label and New Label.

The 2nd worksheet has several columns and i want to do a search in a
specific column (column O). The search should be the text in the other sheet
(Old Label) and all matches should be replaced with the New Label.

Please help!