replacing contents of a cell with data froma list/file

  • Thread starter Thread starter Andy T
  • Start date Start date
A

Andy T

Hi

I guess most things can be done but what I need to know is urgent and
I hope someone may be able to assist quickly,

I have a speard sheet with a grid listing on it with names like
Midlands 1 midlands 2 Southwest 1 and soutwest 2 etc etc

What I would like is a quick method of replacing these cells of data
with real names.

e.g all occurances of Midlands 1 replaced with Fred Smith

Midlands 2 replaced with joe brown etc etc

rather like mail merge in word.

I know I can do simple find and replace but its rather time consuming!

Thanks in advance

Regards Andy T
 
One way is to use a helper sheet ..

Assume your source data is within say, A2:K100 in Sheet1
and your lookup list is in Sheet2, cols A and B, viz:

Midlands 1 Fred Smith
Midlands 2 George K

etc

In a new sheet,
Place in A2:
=IF(Sheet1!A2="","",IF(ISNA(VLOOKUP(Sheet1!A2,Sheet2!$A:$B,2,0)),Sheet1!A2,VLOOKUP(Sheet1!A2,Sheet2!$A:$B,2,0)))
Copy across/down to K100. This returns the source range in Sheet1 with the
replacements that you want done while leaving other source cells "intact".
Then just copy A2:K100 & paste special as values to overwrite the source
range in Sheet1. Delete away the new sheet.
 
You can also use a macro to complete the task. Add other replacement values
to suit. This will replace all instances of each.

Sub ChangeToName()
Application.ScreenUpdating = False

Cells.Replace What:="Midlands 1", Replacement:="Fred Smith", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="Midlands 2", Replacement:="Joe Brown", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="Southwest 1", Replacement:="Fred Brown", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="Southwest 2", Replacement:="Joe Smith", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

Application.ScreenUpdating = True
End Sub


Regards,

Alan
 
One way is to use a helper sheet ..

Assume your source data is within say, A2:K100 in Sheet1
and your lookup list is in Sheet2, cols A and B, viz:

Midlands 1 Fred Smith
Midlands 2 George K

etc

In a new sheet,
Place in A2:
=IF(Sheet1!A2="","",IF(ISNA(VLOOKUP(Sheet1!A2,Sheet2!$A:$B,2,0)),Sheet1!A2,VLOOKUP(Sheet1!A2,Sheet2!$A:$B,2,0)))
Copy across/down to K100. This returns the source range in Sheet1 with the
replacements that you want done while leaving other source cells "intact".
Then just copy A2:K100 & paste special as values to overwrite the source
range in Sheet1. Delete away the new sheet.
Click to expand...


Thanks mate but that is way above my abilities.

I may be able to use it in future now I have something to learn but as
I need it for 7am in the morning I guess its easier to do it long
hand.

Thanks for your time and assistance!

Regards Andy T
 
No problem. But it's really quite simple to set-up and complete. Keep the
steps handy for another time when you might need to do it for a huge source
range, say A2:IV65000 <g>
 
Back
Top