Is This POSSIBLE

[Rebecca]

Hi. I am very new at using EXCEL 2003 and I was wondering if the program can
do the following: I want to search for a certain word in Column B and
another (usually different) word in Column D (both words are in the same
row), and replace a blank cell in Column E with a particular word (also in
the same row). And I would like to do this in all the sheets (that is, a
global search and replace). Could someone give me some advice as to how this
could be done? Please explain in easy English. Thanks.

[CLR]

One way would be to select a cell in your data field (assuming no empty
columns between data columns, and you back up your data first) and then do
Data > Filter > Autofilter.........then click the arrow at the top of column
B and select Word1, then select the arrow at the top of Column D and select
Word 2............this will leave only those rows which have both your Word1
in column B, and your Word 2 in column D..........then you can just type and
copy and paste the NEWWORD you want in column E of those filtered
rows..............then do Data > Filter > Autofilter again to turn off the
filter...........then just repeat the whole process for each
sheet...........

hth
Vaya con Dios,
Chuck, CABGx3


"Rebecca" <Rebecca@discussions.microsoft.com> wrote in message
news:E9896B9C-D82D-458E-B39D-1EBF0485235A@microsoft.com...
> Hi. I am very new at using EXCEL 2003 and I was wondering if the program
can
> do the following: I want to search for a certain word in Column B and
> another (usually different) word in Column D (both words are in the same
> row), and replace a blank cell in Column E with a particular word (also in
> the same row). And I would like to do this in all the sheets (that is, a
> global search and replace). Could someone give me some advice as to how
this
> could be done? Please explain in easy English. Thanks.

[IanRoy]

Hi, Rebecca;
Here is another way. Select all the sheets. (Click first tab and shift+click
the last tab.) Assuming these words start on row 2, enter into E2 this
formula:
=IF(AND(B2="bword",D2="dword"),"eword","")
Replace:
bword with the word you are searching for in column B,
dword with the word you are searching for in column D,
eword with the word you want to place in column E.
Fill that formula down as far as necessary. (By grabbing the lower right
corner of the cell containing the formula, and dragging it down column E.)
Click any un-bolded tab to ungroup the sheets.
You're done.
Hope this helps.
Ian.

"Rebecca" wrote:

> Hi. I am very new at using EXCEL 2003 and I was wondering if the program can
> do the following: I want to search for a certain word in Column B and
> another (usually different) word in Column D (both words are in the same
> row), and replace a blank cell in Column E with a particular word (also in
> the same row). And I would like to do this in all the sheets (that is, a
> global search and replace). Could someone give me some advice as to how this
> could be done? Please explain in easy English. Thanks.

[IanRoy]

Hi, Rebecca;
I have a correction to my last post. When I tried to ungroup the sheets by
clicking an un-bolded tab it didn't work. First time I've seen that. A more
reliable way, I think:
Right-click a tab within the group and choose "Ungroup Sheets."
Regards,
Ian.

[Rebecca]

Dear Ian,

I apologize for being a little slow here, but as I said I am very new to
Excel, so I could not follow your instructions. Could you please explain one
more time , but this time like you are explaining something to a six-year
old. I haven't learned yet how to use formulas.

"IanRoy" wrote:

> Hi, Rebecca;
> I have a correction to my last post. When I tried to ungroup the sheets by
> clicking an un-bolded tab it didn't work. First time I've seen that. A more
> reliable way, I think:
> Right-click a tab within the group and choose "Ungroup Sheets."
> Regards,
> Ian.

[IanRoy]

Hi, Rebecca;

I don't know how to write for a six-year-old, but Word tells me the
following is at Flesch-Kincaid Grade level 6.4, rather harder than what you
asked for, and for that I apologize.

Assumption 1:
You asked for global search and replace, so I made the assumption that:
there is one word you want to look for in column B of all sheets,
another word you want to look for in column D of all sheets,
and one word you want to insert in column E of all sheets.
If this is correct, give me the words and the row number of the top row they
appear, and I will write you a formula that you can copy and paste into your
worksheets.

Assumption 2:
Assumption 1 rather conflicts with "(usually different)" in your first post.
If you mean to change these words from time to time, you will want to enter
these words into some three cells on one or more of your worksheets. Let me
know which cell contains the word for column B, which for column D, and which
for column E, and I will write the formula for that. Also, let me know if
these words differ by sheet.

To explain the formula:
To let Excel know that your cell entry is a formula, you start it with an
equal sign.
The Excel function "AND" returns the value "TRUE" if all of its arguments
are true.
So, if we use the arguments B2="Fred," and D2="Jane" with the AND function
so:
=AND(B2="Fred",D2="Jane") and those cells contain those words, this formula
will return the value: TRUE. (The quotes around Fred and Jane seem to be
needed to tell Excel to look for text.) That is not enough yet. We want to
return a word besides TRUE. We can do that by nesting the AND function and
its arguments inside an IF function. IF returns one value if its argument is
true, and another if its argument is false. So, if we want to return say
"friends" in cell E2, if both Fred and Jane are present, we would write into
cell E2:
=(IF(AND(B2="Fred",D2="Jane"),"friends","")
This way, if both Fred and Jane are present E2 will show: friends. If only
one of them or neither are present, "" tells Excel to show empty text.

If these words will not always be the same, then we can put them in other
cells and then refer to those cells in the formula. We could put Fred in cell
F1, Jane in cell F2, and friends in cell F3, for example. Then write the
formula in E2:
=(IF(AND(B2=$F$1,D2=$F$2),$F$3,"")
The dollar signs tell Excel to refer to those cells regardless of where on
the sheet this formula is copied. The other cell references will adjust, so
that if you copy or fill this formula down to E3, the formula in E3 will read:
=(IF(AND(B3=$F$1,D3=$F$2),$F$3,"")
Unlike text, you won't need to enclose cell references with quotes.
Then if you want to use another set of three words, or any of them, you can
change the entries in cells F1, F2, and F3, and the formulae will update
column E.

If that explanation is adequate, I wll be overjoyed. But if not, come back
with questions, or the information to let me write a formula that you can
copy and paste.

Regards, and welcome to Excel.
Ian.


"Rebecca" wrote:

> Dear Ian,
>
> I apologize for being a little slow here, but as I said I am very new to
> Excel, so I could not follow your instructions. Could you please explain one
> more time , but this time like you are explaining something to a six-year
> old. I haven't learned yet how to use formulas.

[Rebecca]

Dear Ian,

Below is a sample of only one sheet, and there is one sheet for every book
of the Old Greek (LXX, Septuagint) Bible plus the New Testament. It is
arranged as follows (though in this post we can't see the Greek font):

Please note: The first row is blank (I inserted some information here).

The first column (A) is the book (in this example Exodus), the second column
(B) is the Greek dictionary form, the third column (C) is the parsing, the
fourth column is the Greek word declined (D), the blank fifth column (E) is
where I want to put the English translation of the word in D column, and the
last column (F) contains the number of where the word ranks in the book (and
used for resorting). I realize this could be done in Access, but I want to
do this in Excel for several reasons I won't bore you with the details.

A B C D E F
Exo 01:01 ou-toj rdnnp tau/ta 1
Exo 01:01 o` dnnp ta. 2
Exo 01:01 o;noma nnnpc ovno,mata 3
Exo 01:01 o` dgmp tw/n 4
Exo 01:01 ui`o,j ngmpc ui`w/n 5
Exo 01:01 ivsrah,l ngmsp israhl 6
Exo 01:01 o` dgmp tw/n 7
Exo 01:01 eivsporeu,omai vpxpgmp eivspeporeume,nwn 8
Exo 01:01 eivj pa eivj 9
Exo 01:01 ai;guptoj nafsp ai;gupton 10
Exo 01:01 a[ma pd a[ma 11
Exo 01:01 ivakw,b ndmsp iakwb 12
Exo 01:01 o` ddms tw/| 13
Exo 01:01 path,r ndmsc patri. 14

As you can see the dictionary word for the (o`) as several forms. This is
want to do: as I translate from the first word in the book I want to search
for the same word in B (ou-toj = this) and the same word in D (tau/ta =
these) and translate this B and D combination in column E (these). Then I go
down to the second row and do the same. And this should be a global search
and replace of all the books (sheets) of the Greek Bible.

"IanRoy" wrote:

> Hi, Rebecca;
>
> I don't know how to write for a six-year-old, but Word tells me the
> following is at Flesch-Kincaid Grade level 6.4, rather harder than what you
> asked for, and for that I apologize.
>
> Assumption 1:
> You asked for global search and replace, so I made the assumption that:
> there is one word you want to look for in column B of all sheets,
> another word you want to look for in column D of all sheets,
> and one word you want to insert in column E of all sheets.
> If this is correct, give me the words and the row number of the top row they
> appear, and I will write you a formula that you can copy and paste into your
> worksheets.
>
> Assumption 2:
> Assumption 1 rather conflicts with "(usually different)" in your first post.
> If you mean to change these words from time to time, you will want to enter
> these words into some three cells on one or more of your worksheets. Let me
> know which cell contains the word for column B, which for column D, and which
> for column E, and I will write the formula for that. Also, let me know if
> these words differ by sheet.
>
> To explain the formula:
> To let Excel know that your cell entry is a formula, you start it with an
> equal sign.
> The Excel function "AND" returns the value "TRUE" if all of its arguments
> are true.
> So, if we use the arguments B2="Fred," and D2="Jane" with the AND function
> so:
> =AND(B2="Fred",D2="Jane") and those cells contain those words, this formula
> will return the value: TRUE. (The quotes around Fred and Jane seem to be
> needed to tell Excel to look for text.) That is not enough yet. We want to
> return a word besides TRUE. We can do that by nesting the AND function and
> its arguments inside an IF function. IF returns one value if its argument is
> true, and another if its argument is false. So, if we want to return say
> "friends" in cell E2, if both Fred and Jane are present, we would write into
> cell E2:
> =(IF(AND(B2="Fred",D2="Jane"),"friends","")
> This way, if both Fred and Jane are present E2 will show: friends. If only
> one of them or neither are present, "" tells Excel to show empty text.
>
> If these words will not always be the same, then we can put them in other
> cells and then refer to those cells in the formula. We could put Fred in cell
> F1, Jane in cell F2, and friends in cell F3, for example. Then write the
> formula in E2:
> =(IF(AND(B2=$F$1,D2=$F$2),$F$3,"")
> The dollar signs tell Excel to refer to those cells regardless of where on
> the sheet this formula is copied. The other cell references will adjust, so
> that if you copy or fill this formula down to E3, the formula in E3 will read:
> =(IF(AND(B3=$F$1,D3=$F$2),$F$3,"")
> Unlike text, you won't need to enclose cell references with quotes.
> Then if you want to use another set of three words, or any of them, you can
> change the entries in cells F1, F2, and F3, and the formulae will update
> column E.
>
> If that explanation is adequate, I wll be overjoyed. But if not, come back
> with questions, or the information to let me write a formula that you can
> copy and paste.
>
> Regards, and welcome to Excel.
> Ian.
>
>
> "Rebecca" wrote:
>
> > Dear Ian,
> >
> > I apologize for being a little slow here, but as I said I am very new to
> > Excel, so I could not follow your instructions. Could you please explain one
> > more time , but this time like you are explaining something to a six-year
> > old. I haven't learned yet how to use formulas.

[IanRoy]

Dear Rebecca,
Translating Old Greek? Pretty advanced for a six-year-old, I must say!
The formula I wrote won't do it. Chuck's filtering method will, but you
would have filter each sheet for the same combination of B and D, paste the
translation into each one, and then re-filter each sheet for the next
combination. I'm going to think about this and get back to you some time
today.
Regards,
Ian.

[IanRoy]

Dear Rebecca,

Sorry about the delay, but I do have an answer for you. Insert into every
sheet an helper column containing a CONCATENATE function to combine the text
from B and D. Then copy one of these sheets to use as a lookup table and use
the function VLOOKUP in each of your other sheets to return your English
translation from that table for each unique combination of word and
declension. To see what these functions do and what syntax they require,
click the "fx" on your formula bar and look up you formula in the reulting
dialog.

After translating everything in your lookup table, delete duplicate rows
from it and copy the untranslated rows from your next sheet into it. Continue
translation. Hopefully this lookup table will not exceed the sixty-five
thousand some rows of which Excel is capable. If it does, you will need to
make a second lookup table and point the VLOOKUP functions from cells not yet
containing translations to the new lookup table.

I have done this with a sample workbook containing the data you posted. It
works. You would enter your translations in the lookup table, and VLOOKUP
wouId copy those results into each of your other sheets. If you want that
sample, plus detailed instructions, please e-mail me at i_macewan at
earthlink dot net. and we can discuss terms.

Regards,
Ian.


"Rebecca" wrote:

> Dear Ian,
>
> Below is a sample of only one sheet, and there is one sheet for every book
> of the Old Greek (LXX, Septuagint) Bible plus the New Testament. It is
> arranged as follows (though in this post we can't see the Greek font):
>
> Please note: The first row is blank (I inserted some information here).
>
> The first column (A) is the book (in this example Exodus), the second column
> (B) is the Greek dictionary form, the third column (C) is the parsing, the
> fourth column is the Greek word declined (D), the blank fifth column (E) is
> where I want to put the English translation of the word in D column, and the
> last column (F) contains the number of where the word ranks in the book (and
> used for resorting). I realize this could be done in Access, but I want to
> do this in Excel for several reasons I won't bore you with the details.
>
> A B C D E F
> Exo 01:01 ou-toj rdnnp tau/ta 1
> Exo 01:01 o` dnnp ta. 2
> Exo 01:01 o;noma nnnpc ovno,mata 3
> Exo 01:01 o` dgmp tw/n 4
> Exo 01:01 ui`o,j ngmpc ui`w/n 5
> Exo 01:01 ivsrah,l ngmsp israhl 6
> Exo 01:01 o` dgmp tw/n 7
> Exo 01:01 eivsporeu,omai vpxpgmp eivspeporeume,nwn 8
> Exo 01:01 eivj pa eivj 9
> Exo 01:01 ai;guptoj nafsp ai;gupton 10
> Exo 01:01 a[ma pd a[ma 11
> Exo 01:01 ivakw,b ndmsp iakwb 12
> Exo 01:01 o` ddms tw/| 13
> Exo 01:01 path,r ndmsc patri. 14
>
> As you can see the dictionary word for the (o`) as several forms. This is
> want to do: as I translate from the first word in the book I want to search
> for the same word in B (ou-toj = this) and the same word in D (tau/ta =
> these) and translate this B and D combination in column E (these). Then I go
> down to the second row and do the same. And this should be a global search
> and replace of all the books (sheets) of the Greek Bible.