Compare variables in two excel workbooks

[Alex]

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".

 

[Sheeloo]

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&"*",Sheet2!$D$1:$D$20,0)),A5,D5)

 

[Sheeloo]

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...

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&"*",Sheet2!$D$1:$D$20,0)),A5,D5)

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]

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.

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...

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&"*",Sheet2!$D$1:$D$20,0)),A5,D5)

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".

 

[Sheeloo]

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-replace-manager-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

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.

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...

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&"*",Sheet2!$D$1:$D$20,0)),A5,D5)

:

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]

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.

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-replace-manager-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

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.

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...

:

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&"*",Sheet2!$D$1:$D$20,0)),A5,D5)

:

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".

 

[Sheeloo]

This is a good problem to learn VBA...

I can help you through the paces. You can send me mails at my id. Click on
my name to see how to get the id...

You need to learn -
how to cycle through files in a directory
how to cycle through worksheets
how to find and replace...

If you want I can write the code for you...

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.

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-replace-manager-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

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.

:

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...

:

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&"*",Sheet2!$D$1:$D$20,0)),A5,D5)

:

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'll definitely try to learn VBA, but it's not going to take day o two, and I
need this code today... Could you please write it for me?
I appreciate your help!

This is a good problem to learn VBA...

I can help you through the paces. You can send me mails at my id. Click on
my name to see how to get the id...

You need to learn -
how to cycle through files in a directory
how to cycle through worksheets
how to find and replace...

If you want I can write the code for you...

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.

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-replace-manager-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


:

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.

:

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...

:

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&"*",Sheet2!$D$1:$D$20,0)),A5,D5)

:

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".