Please help!
I have to compare values in column A of workbook.1 to any values in column D
of workbook.2, and if match is found, replace found value with the value in
column D of workbook.1.
For example:
Let say we have value "pony" in A5 of workbook.1.
We have to search for this value in column D of workbook.2
If match is found: " There is a pony in the zoo", we have to replace found
value with value "white pony" in D5 of the workbook.1, so resulting value in
workbook.2 is: "There is a white pony in the zoo".

Alex,
[I have written the formula assuming Sheet1 and Sheet2 are in the same
workbook. You need to change the reference to Sheet2 to the appropriate
workbook]

Copy this into Sheet1 B5
=SUBSTITUTE(INDIRECT("Sheet2!D"&MATCH("*"&A5&"*",S heet2!$D$1:$D$20,0)),A5,D5)

"Alex" wrote:

Please help!
I have to compare values in column A of workbook.1 to any values in column D
of workbook.2, and if match is found, replace found value with the value in
column D of workbook.1.
For example:
Let say we have value "pony" in A5 of workbook.1.
We have to search for this value in column D of workbook.2
If match is found: " There is a pony in the zoo", we have to replace found
value with value "white pony" in D5 of the workbook.1, so resulting value in
workbook.2 is: "There is a white pony in the zoo".

Entered in B5 of Sheet1 of Workbook.1

=SUBSTITUTE(INDIRECT("[Workbook.2]Sheet2!D"&MATCH("*"&A5&"*",[Workbook.2]Sheet2!$D$1:$D$20,0)),A5,D5)

Adjust for your workbook/sheet names...

"Sheeloo" wrote:

Alex,
[I have written the formula assuming Sheet1 and Sheet2 are in the same
workbook. You need to change the reference to Sheet2 to the appropriate
workbook]

Copy this into Sheet1 B5
=SUBSTITUTE(INDIRECT("Sheet2!D"&MATCH("*"&A5&"*",S heet2!$D$1:$D$20,0)),A5,D5)

"Alex" wrote:

Please help!
I have to compare values in column A of workbook.1 to any values in column D
of workbook.2, and if match is found, replace found value with the value in
column D of workbook.1.
For example:
Let say we have value "pony" in A5 of workbook.1.
We have to search for this value in column D of workbook.2
If match is found: " There is a pony in the zoo", we have to replace found
value with value "white pony" in D5 of the workbook.1, so resulting value in
workbook.2 is: "There is a white pony in the zoo".

Thank you Sheeloo, I'll try to use your formula. It's my bad I did't explain
the problem clear enough. I have excel file (workbook.1) with column A (old
variables names) and column D (new variables names). On the other hand I got
40 excel files (workbook.2 -40), where those old variables names used here
and there, sometimes in a middle of the text. The goal is to replace all old
variables with the new ones. I think it can't be done without VBA.
Thanks.

"Sheeloo" wrote:

Entered in B5 of Sheet1 of Workbook.1

=SUBSTITUTE(INDIRECT("[Workbook.2]Sheet2!D"&MATCH("*"&A5&"*",[Workbook.2]Sheet2!$D$1:$D$20,0)),A5,D5)

Adjust for your workbook/sheet names...

"Sheeloo" wrote:

Alex,
[I have written the formula assuming Sheet1 and Sheet2 are in the same
workbook. You need to change the reference to Sheet2 to the appropriate
workbook]

Copy this into Sheet1 B5
=SUBSTITUTE(INDIRECT("Sheet2!D"&MATCH("*"&A5&"*",S heet2!$D$1:$D$20,0)),A5,D5)

"Alex" wrote:

Please help!
I have to compare values in column A of workbook.1 to any values in column D
of workbook.2, and if match is found, replace found value with the value in
column D of workbook.1.
For example:
Let say we have value "pony" in A5 of workbook.1.
We have to search for this value in column D of workbook.2
If match is found: " There is a pony in the zoo", we have to replace found
value with value "white pony" in D5 of the workbook.1, so resulting value in
workbook.2 is: "There is a white pony in the zoo".

Yes, you need a VBA solution...
If you are willing to spend a few bucks then you can try a commercially
availabe Add-In
One you can try is availabe at
http://www.ablebits.com/excel-find-r...anager-addins/

Site says it has a 15 day fully functional trial version...

Pl. note that I have NOT tried it and I do NOT have any relationship with
the suggested site/Add-In.

______________________________________________
Suggested logic
Open workbook1
loop through all other workbooks
loop through all worksheets
loop through words to replace
find and replace
next word
next worksheet
next workbook


"Alex" wrote:

Thank you Sheeloo, I'll try to use your formula. It's my bad I did't explain
the problem clear enough. I have excel file (workbook.1) with column A (old
variables names) and column D (new variables names). On the other hand I got
40 excel files (workbook.2 -40), where those old variables names used here
and there, sometimes in a middle of the text. The goal is to replace all old
variables with the new ones. I think it can't be done without VBA.
Thanks.

"Sheeloo" wrote:

Entered in B5 of Sheet1 of Workbook.1

=SUBSTITUTE(INDIRECT("[Workbook.2]Sheet2!D"&MATCH("*"&A5&"*",[Workbook.2]Sheet2!$D$1:$D$20,0)),A5,D5)

Adjust for your workbook/sheet names...

"Sheeloo" wrote:

Alex,
[I have written the formula assuming Sheet1 and Sheet2 are in the same
workbook. You need to change the reference to Sheet2 to the appropriate
workbook]

Copy this into Sheet1 B5
=SUBSTITUTE(INDIRECT("Sheet2!D"&MATCH("*"&A5&"*",S heet2!$D$1:$D$20,0)),A5,D5)

"Alex" wrote:

Please help!
I have to compare values in column A of workbook.1 to any values in column D
of workbook.2, and if match is found, replace found value with the value in
column D of workbook.1.
For example:
Let say we have value "pony" in A5 of workbook.1.
We have to search for this value in column D of workbook.2
If match is found: " There is a pony in the zoo", we have to replace found
value with value "white pony" in D5 of the workbook.1, so resulting value in
workbook.2 is: "There is a white pony in the zoo".

It's seems that code should be pretty simple, and if it is done right, should
take less than a sec. to complete the job. I wish I knew VBA well enough.
Thank you Sheeloo for your help, I'll try everything you suggested.

"Sheeloo" wrote:

Yes, you need a VBA solution...
If you are willing to spend a few bucks then you can try a commercially
availabe Add-In
One you can try is availabe at
http://www.ablebits.com/excel-find-r...anager-addins/

Site says it has a 15 day fully functional trial version...

Pl. note that I have NOT tried it and I do NOT have any relationship with
the suggested site/Add-In.

______________________________________________
Suggested logic
Open workbook1
loop through all other workbooks
loop through all worksheets
loop through words to replace
find and replace
next word
next worksheet
next workbook


"Alex" wrote:

Thank you Sheeloo, I'll try to use your formula. It's my bad I did't explain
the problem clear enough. I have excel file (workbook.1) with column A (old
variables names) and column D (new variables names). On the other hand I got
40 excel files (workbook.2 -40), where those old variables names used here
and there, sometimes in a middle of the text. The goal is to replace all old
variables with the new ones. I think it can't be done without VBA.
Thanks.

"Sheeloo" wrote:

Entered in B5 of Sheet1 of Workbook.1

=SUBSTITUTE(INDIRECT("[Workbook.2]Sheet2!D"&MATCH("*"&A5&"*",[Workbook.2]Sheet2!$D$1:$D$20,0)),A5,D5)

Adjust for your workbook/sheet names...

"Sheeloo" wrote:

Alex,
[I have written the formula assuming Sheet1 and Sheet2 are in the same
workbook. You need to change the reference to Sheet2 to the appropriate
workbook]

Copy this into Sheet1 B5
=SUBSTITUTE(INDIRECT("Sheet2!D"&MATCH("*"&A5&"*",S heet2!$D$1:$D$20,0)),A5,D5)

"Alex" wrote:

Please help!
I have to compare values in column A of workbook.1 to any values in column D
of workbook.2, and if match is found, replace found value with the value in
column D of workbook.1.
For example:
Let say we have value "pony" in A5 of workbook.1.
We have to search for this value in column D of workbook.2
If match is found: " There is a pony in the zoo", we have to replace found
value with value "white pony" in D5 of the workbook.1, so resulting value in
workbook.2 is: "There is a white pony in the zoo".