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&"*",Sheet2!$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&"*",Sheet2!$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&"*",Sheet2!$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&"*",Sheet2!$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&"*",Sheet2!$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".

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

"Alex" wrote:

> 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&"*",Sheet2!$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".

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!

"Sheeloo" wrote:

> 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...
>
> "Alex" wrote:
>
> > 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&"*",Sheet2!$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".