Greetings, again. I think I asked for help in the wrong place, so please
forgive me for posting this again (though I am a new user, indeed). By the
way, this is not a homework assignment, so feel free to give some advice (in
step-by-step easy English, please).

I am using MS Excel 2007 (beta). I have four columns: A, B, C, and D (D is
blank). If the Hebrew word in a particular row in Col. A is, say, "gggg,"
the parsing (grammar) in the same row in Col. B for that word is "nbcpd," and
the Hebrew word in the same row in Col. C is "gggdd" (the dictionary form for
gggg in Col. A), I want to place the word "tree" in the same row in blank
column in Col. D (the English translation of the word in Col. A, with the
information in Cols. B and C taken into consideration -- tense, number,
gender, etc.).

I want to go down the spreadsheet one row / word at a time (each row is one
Hebrew word) and fill in every blank in Col D., but I want to remain in the
same row (or maybe come back to it, however this could be done) and translate
all the other rows with the same A, B, and C combinations.

I hope I haven't been too clumsy in explaining what I am trying to do. I
don't want to use Access for a reason I won't go into here (there are certain
calculations that I would like to do later in Excel). Could someone please
help me with this problem? Thank you.

This is what I answered when you posted a similar question in new users
albeit with different words and A, B and C

In D2 (or whatever row your data starts) put

=IF(AND(A2="hat",B2="bug",C2="flower"),"tree","")


copy down, if you want some more flexibility you can use

=IF(AND(A2=$F$1,B2=$G$1,C2=$H$1),"tree","")

where you would put the 3 words in F1, G1 and H1 respectively




--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Rebecca" wrote in message
...
Greetings, again. I think I asked for help in the wrong place, so please
forgive me for posting this again (though I am a new user, indeed). By
the
way, this is not a homework assignment, so feel free to give some advice
(in
step-by-step easy English, please).

I am using MS Excel 2007 (beta). I have four columns: A, B, C, and D (D
is
blank). If the Hebrew word in a particular row in Col. A is, say, "gggg,"
the parsing (grammar) in the same row in Col. B for that word is "nbcpd,"
and
the Hebrew word in the same row in Col. C is "gggdd" (the dictionary form
for
gggg in Col. A), I want to place the word "tree" in the same row in blank
column in Col. D (the English translation of the word in Col. A, with the
information in Cols. B and C taken into consideration -- tense, number,
gender, etc.).

I want to go down the spreadsheet one row / word at a time (each row is
one
Hebrew word) and fill in every blank in Col D., but I want to remain in
the
same row (or maybe come back to it, however this could be done) and
translate
all the other rows with the same A, B, and C combinations.

I hope I haven't been too clumsy in explaining what I am trying to do. I
don't want to use Access for a reason I won't go into here (there are
certain
calculations that I would like to do later in Excel). Could someone please
help me with this problem? Thank you.

Thanks, Mr. Sjoblom, for your much-need help. I apologized for posting in
another group, but as I said there, I am a new user, but since I am already
here, well...

I hate to sound like a real dummy, but could you please explain your
solution step-by-step? Once I put the formula you suggested in the D column,
then what do I do? Please read the rest of this post for some details.

The entire spreadsheet has over 400,000 rows (and as I mentioned, each row
has one Hebrew word, one parsing of this word, and the dictionary definition
of this word -- three columns in all -- plus the empty D column where the
English translation goes).

As I go down through the Bible, word by word, when I see the Hebrew word for
"house," for example, I want to simply fill in the blanks where this word
(and its parsing and dictionary form) is located throughout the various
chapters and verses. It would be nice if I could simply seach on the three
columns simultaneously-- A, B, and C -- and then replace in the empty D
column. But alas!

"Peo Sjoblom" wrote:

This is what I answered when you posted a similar question in new users
albeit with different words and A, B and C

In D2 (or whatever row your data starts) put

=IF(AND(A2="hat",B2="bug",C2="flower"),"tree","")


copy down, if you want some more flexibility you can use

=IF(AND(A2=$F$1,B2=$G$1,C2=$H$1),"tree","")

where you would put the 3 words in F1, G1 and H1 respectively




--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Rebecca" wrote in message
...
Greetings, again. I think I asked for help in the wrong place, so please
forgive me for posting this again (though I am a new user, indeed). By
the
way, this is not a homework assignment, so feel free to give some advice
(in
step-by-step easy English, please).

I am using MS Excel 2007 (beta). I have four columns: A, B, C, and D (D
is
blank). If the Hebrew word in a particular row in Col. A is, say, "gggg,"
the parsing (grammar) in the same row in Col. B for that word is "nbcpd,"
and
the Hebrew word in the same row in Col. C is "gggdd" (the dictionary form
for
gggg in Col. A), I want to place the word "tree" in the same row in blank
column in Col. D (the English translation of the word in Col. A, with the
information in Cols. B and C taken into consideration -- tense, number,
gender, etc.).

I want to go down the spreadsheet one row / word at a time (each row is
one
Hebrew word) and fill in every blank in Col D., but I want to remain in
the
same row (or maybe come back to it, however this could be done) and
translate
all the other rows with the same A, B, and C combinations.

I hope I haven't been too clumsy in explaining what I am trying to do. I
don't want to use Access for a reason I won't go into here (there are
certain
calculations that I would like to do later in Excel). Could someone please
help me with this problem? Thank you.

Hi Rebecca

It is difficult to understand exactly what you are after from your
description here (with various letter combinations) or your posting in
another ng with real words that Peo responded to.
You talk about 400,000 individual rows, each with a separate word, but
you then talk about wanting to replace items where each occurrence of
the word appears in various chapters and verses.
For the latter, you would just use a Vlookup formula on your dictionary
to insert the equivalent English/Hebrew translation.
I think we need a little clearer definition of what you are seeking in
order to be able to help you.

In the interim, (and hoping I do not cause any offence by referring you
to a Christian bible), John Walkenbach has set the whole of the King
James version of the Bible in an Excel workbook, with each chapter on a
separate tab and some Formulae to find occurrences of words and some VBA
code plus forms to extract verses.
I mention this as you might like to look at his methodology to see if it
offers you any assistance in designing and implementing what you are
trying to do.
You can find John's file at
http://j-walkblog.com/index.php?/web...ible_in_excel/

--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Mr. Sjoblom, for your much-need help. I apologized for
posting in
another group, but as I said there, I am a new user, but since I am
already
here, well...

I hate to sound like a real dummy, but could you please explain your
solution step-by-step? Once I put the formula you suggested in the D
column,
then what do I do? Please read the rest of this post for some
details.

The entire spreadsheet has over 400,000 rows (and as I mentioned, each
row
has one Hebrew word, one parsing of this word, and the dictionary
definition
of this word -- three columns in all -- plus the empty D column where
the
English translation goes).

As I go down through the Bible, word by word, when I see the Hebrew
word for
"house," for example, I want to simply fill in the blanks where this
word
(and its parsing and dictionary form) is located throughout the
various
chapters and verses. It would be nice if I could simply seach on the
three
columns simultaneously-- A, B, and C -- and then replace in the empty
D
column. But alas!

"Peo Sjoblom" wrote:

This is what I answered when you posted a similar question in new
users
albeit with different words and A, B and C

In D2 (or whatever row your data starts) put

=IF(AND(A2="hat",B2="bug",C2="flower"),"tree","")


copy down, if you want some more flexibility you can use

=IF(AND(A2=$F$1,B2=$G$1,C2=$H$1),"tree","")

where you would put the 3 words in F1, G1 and H1 respectively




--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Rebecca" wrote in message
...
Greetings, again. I think I asked for help in the wrong place, so
please
forgive me for posting this again (though I am a new user, indeed).
By
the
way, this is not a homework assignment, so feel free to give some
advice
(in
step-by-step easy English, please).

I am using MS Excel 2007 (beta). I have four columns: A, B, C, and
D (D
is
blank). If the Hebrew word in a particular row in Col. A is, say,
"gggg,"
the parsing (grammar) in the same row in Col. B for that word is
"nbcpd,"
and
the Hebrew word in the same row in Col. C is "gggdd" (the
dictionary form
for
gggg in Col. A), I want to place the word "tree" in the same row in
blank
column in Col. D (the English translation of the word in Col. A,
with the
information in Cols. B and C taken into consideration -- tense,
number,
gender, etc.).

I want to go down the spreadsheet one row / word at a time (each
row is
one
Hebrew word) and fill in every blank in Col D., but I want to
remain in
the
same row (or maybe come back to it, however this could be done) and
translate
all the other rows with the same A, B, and C combinations.

I hope I haven't been too clumsy in explaining what I am trying to
do. I
don't want to use Access for a reason I won't go into here (there
are
certain
calculations that I would like to do later in Excel). Could someone
please
help me with this problem? Thank you.

Thanks, Roger for your useful advice. It is my fault for being so sloppy in
my explanations. Please bear with me one more time.

My spreadsheet (Excel 2007) has about 400,000 rows, from Genesis to 2
Chronicles in the Hebrew Bible (or Tanach, if you prefer). Each row has five
columns. See below for an example of a few rows (I can't use Hebrew fonts
here so I'll just make up some words):

A B C D E
Gen 1:1 gjgj nsf gggj (empty)
Gen 1:1 gss ndm gga (empty)
Gen 1:1 gjgj nsf gggj (empty)

In Col. A is the book, chapter, and verse (for the first three words in Gen.
1 you can see listed here)
In Col. B is the Hebrew word
In Col. C is the parsing of this word (case, gender, number, etc.)
In Col. D is the dictionary form of this Hebrew word
In Col. E is where the English translation of this word will go.

As you can see the first and third rows contain the same word (word,
parsing, and dictionary form) so as I proceed through the Bible, translating
into English row-by-row, if I can insert the English translation in all the
places where there is this (and subsequently others) three-column
combination, I can save a lot of time and insure translation consistency.
Peo kindly gave me a formula to work with, but I found it too cumbersome and
confusing to use with such a large spreadsheet. It would be nice if the
search and replace function could include several columns in the search box
(say three), and replace something in another column altogether. Alas, if
everything were so simple. Could you please give me some advice on how I can
do this, perhaps with a macro of something, though as I said above, I am a
new user, and I need things explained in easy English? Thanks.



"Roger Govier" wrote:

Hi Rebecca

It is difficult to understand exactly what you are after from your
description here (with various letter combinations) or your posting in
another ng with real words that Peo responded to.
You talk about 400,000 individual rows, each with a separate word, but
you then talk about wanting to replace items where each occurrence of
the word appears in various chapters and verses.
For the latter, you would just use a Vlookup formula on your dictionary
to insert the equivalent English/Hebrew translation.
I think we need a little clearer definition of what you are seeking in
order to be able to help you.

In the interim, (and hoping I do not cause any offence by referring you
to a Christian bible), John Walkenbach has set the whole of the King
James version of the Bible in an Excel workbook, with each chapter on a
separate tab and some Formulae to find occurrences of words and some VBA
code plus forms to extract verses.
I mention this as you might like to look at his methodology to see if it
offers you any assistance in designing and implementing what you are
trying to do.
You can find John's file at
http://j-walkblog.com/index.php?/web...ible_in_excel/

--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Mr. Sjoblom, for your much-need help. I apologized for
posting in
another group, but as I said there, I am a new user, but since I am
already
here, well...

I hate to sound like a real dummy, but could you please explain your
solution step-by-step? Once I put the formula you suggested in the D
column,
then what do I do? Please read the rest of this post for some
details.

The entire spreadsheet has over 400,000 rows (and as I mentioned, each
row
has one Hebrew word, one parsing of this word, and the dictionary
definition
of this word -- three columns in all -- plus the empty D column where
the
English translation goes).

As I go down through the Bible, word by word, when I see the Hebrew
word for
"house," for example, I want to simply fill in the blanks where this
word
(and its parsing and dictionary form) is located throughout the
various
chapters and verses. It would be nice if I could simply seach on the
three
columns simultaneously-- A, B, and C -- and then replace in the empty
D
column. But alas!

"Peo Sjoblom" wrote:

This is what I answered when you posted a similar question in new
users
albeit with different words and A, B and C

In D2 (or whatever row your data starts) put

=IF(AND(A2="hat",B2="bug",C2="flower"),"tree","")


copy down, if you want some more flexibility you can use

=IF(AND(A2=$F$1,B2=$G$1,C2=$H$1),"tree","")

where you would put the 3 words in F1, G1 and H1 respectively




--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Rebecca" wrote in message
...
Greetings, again. I think I asked for help in the wrong place, so
please
forgive me for posting this again (though I am a new user, indeed).
By
the
way, this is not a homework assignment, so feel free to give some
advice
(in
step-by-step easy English, please).

I am using MS Excel 2007 (beta). I have four columns: A, B, C, and
D (D
is
blank). If the Hebrew word in a particular row in Col. A is, say,
"gggg,"
the parsing (grammar) in the same row in Col. B for that word is
"nbcpd,"
and
the Hebrew word in the same row in Col. C is "gggdd" (the
dictionary form
for
gggg in Col. A), I want to place the word "tree" in the same row in
blank
column in Col. D (the English translation of the word in Col. A,
with the
information in Cols. B and C taken into consideration -- tense,
number,
gender, etc.).

I want to go down the spreadsheet one row / word at a time (each
row is
one
Hebrew word) and fill in every blank in Col D., but I want to
remain in
the
same row (or maybe come back to it, however this could be done) and
translate
all the other rows with the same A, B, and C combinations.

I hope I haven't been too clumsy in explaining what I am trying to
do. I
don't want to use Access for a reason I won't go into here (there
are
certain
calculations that I would like to do later in Excel). Could someone
please
help me with this problem? Thank you.

Hi Rebecca

This may be a bit unwieldy with the size of the data table you are
using, but it should work.
I left a gap in column F and in G1 I entered
=B1&"|"&C1&"|"&D1
This is just a concatenation of your three entries, with the pipe
character | (shifted backslash) in between.
In H1 I entered
=E1
I then copied this down my sheet
In cell E1 I entered
=VLOOKUP(B1&"|"&C1&"|"&D1,$G$1:$H$40000,2,0) (I couldn't use 400,000
as I am still on XL2003)
and copied this down the sheet

Now, a you enter the English value in E1, it will get copied to H1 and
if there are any entries the same lower down the sheet, they will pick
up the translation.
Cells in column G will contain || and in column H will contain 0 for any
rows where you have not entered any values in B, C and D.
Equally, column E will contain 0, until there is a translation either
from a typed entry by you, or through having been found via the Vlookup
formula.

This will only work provided translations are made in order, i.e. the
translation would have to have been entered in row 1 of your example,
for row 3 to find it automatically.

At some point, when you have values in a contiguous range of cells in
column E, you can copy those cells then Paste SpecialValues over the
top of them to "fix" the values in place of the formulae.

I hope this helps in your mammoth undertaking.
IF you have any further problems, post back.



--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Roger for your useful advice. It is my fault for being so
sloppy in
my explanations. Please bear with me one more time.

My spreadsheet (Excel 2007) has about 400,000 rows, from Genesis to 2
Chronicles in the Hebrew Bible (or Tanach, if you prefer). Each row
has five
columns. See below for an example of a few rows (I can't use Hebrew
fonts
here so I'll just make up some words):

A B C D E
Gen 1:1 gjgj nsf gggj (empty)
Gen 1:1 gss ndm gga (empty)
Gen 1:1 gjgj nsf gggj (empty)

In Col. A is the book, chapter, and verse (for the first three words
in Gen.
1 you can see listed here)
In Col. B is the Hebrew word
In Col. C is the parsing of this word (case, gender, number, etc.)
In Col. D is the dictionary form of this Hebrew word
In Col. E is where the English translation of this word will go.

As you can see the first and third rows contain the same word (word,
parsing, and dictionary form) so as I proceed through the Bible,
translating
into English row-by-row, if I can insert the English translation in
all the
places where there is this (and subsequently others) three-column
combination, I can save a lot of time and insure translation
consistency.
Peo kindly gave me a formula to work with, but I found it too
cumbersome and
confusing to use with such a large spreadsheet. It would be nice if
the
search and replace function could include several columns in the
search box
(say three), and replace something in another column altogether.
Alas, if
everything were so simple. Could you please give me some advice on
how I can
do this, perhaps with a macro of something, though as I said above, I
am a
new user, and I need things explained in easy English? Thanks.



"Roger Govier" wrote:

Hi Rebecca

It is difficult to understand exactly what you are after from your
description here (with various letter combinations) or your posting
in
another ng with real words that Peo responded to.
You talk about 400,000 individual rows, each with a separate word,
but
you then talk about wanting to replace items where each occurrence of
the word appears in various chapters and verses.
For the latter, you would just use a Vlookup formula on your
dictionary
to insert the equivalent English/Hebrew translation.
I think we need a little clearer definition of what you are seeking
in
order to be able to help you.

In the interim, (and hoping I do not cause any offence by referring
you
to a Christian bible), John Walkenbach has set the whole of the King
James version of the Bible in an Excel workbook, with each chapter on
a
separate tab and some Formulae to find occurrences of words and some
VBA
code plus forms to extract verses.
I mention this as you might like to look at his methodology to see if
it
offers you any assistance in designing and implementing what you are
trying to do.
You can find John's file at
http://j-walkblog.com/index.php?/web...ible_in_excel/

--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Mr. Sjoblom, for your much-need help. I apologized for
posting in
another group, but as I said there, I am a new user, but since I am
already
here, well...

I hate to sound like a real dummy, but could you please explain
your
solution step-by-step? Once I put the formula you suggested in the
D
column,
then what do I do? Please read the rest of this post for some
details.

The entire spreadsheet has over 400,000 rows (and as I mentioned,
each
row
has one Hebrew word, one parsing of this word, and the dictionary
definition
of this word -- three columns in all -- plus the empty D column
where
the
English translation goes).

As I go down through the Bible, word by word, when I see the Hebrew
word for
"house," for example, I want to simply fill in the blanks where
this
word
(and its parsing and dictionary form) is located throughout the
various
chapters and verses. It would be nice if I could simply seach on
the
three
columns simultaneously-- A, B, and C -- and then replace in the
empty
D
column. But alas!

"Peo Sjoblom" wrote:

This is what I answered when you posted a similar question in new
users
albeit with different words and A, B and C

In D2 (or whatever row your data starts) put

=IF(AND(A2="hat",B2="bug",C2="flower"),"tree","")


copy down, if you want some more flexibility you can use

=IF(AND(A2=$F$1,B2=$G$1,C2=$H$1),"tree","")

where you would put the 3 words in F1, G1 and H1 respectively




--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Rebecca" wrote in message
...
Greetings, again. I think I asked for help in the wrong place,
so
please
forgive me for posting this again (though I am a new user,
indeed).
By
the
way, this is not a homework assignment, so feel free to give
some
advice
(in
step-by-step easy English, please).

I am using MS Excel 2007 (beta). I have four columns: A, B, C,
and
D (D
is
blank). If the Hebrew word in a particular row in Col. A is,
say,
"gggg,"
the parsing (grammar) in the same row in Col. B for that word is
"nbcpd,"
and
the Hebrew word in the same row in Col. C is "gggdd" (the
dictionary form
for
gggg in Col. A), I want to place the word "tree" in the same row
in
blank
column in Col. D (the English translation of the word in Col. A,
with the
information in Cols. B and C taken into consideration -- tense,
number,
gender, etc.).

I want to go down the spreadsheet one row / word at a time (each
row is
one
Hebrew word) and fill in every blank in Col D., but I want to
remain in
the
same row (or maybe come back to it, however this could be done)
and
translate
all the other rows with the same A, B, and C combinations.

I hope I haven't been too clumsy in explaining what I am trying
to
do. I
don't want to use Access for a reason I won't go into here
(there
are
certain
calculations that I would like to do later in Excel). Could
someone
please
help me with this problem? Thank you.

Thanks, Roger, for your help. I tried as best I could, but I couldn't get it
to work (I'm a new user par excellance). Let me see if I understand your
directions correctly.

First, I should leave a gap in F1 ...
Next, in G1 I should enter =B1&"|"&C1&"|"&D1 ...

(You didn't mention it, but I presume I copy this formula all the way down
the column, right? By the way, what is the best way to copy, something you
also didn't mention, perhaps because you assumed it was too obvious.)

Then I should enter =E1 in column H1, and copy this all the way down the
column ...
Finally, in E1 I should enter =VLOOKUP(B1&"|"&C1&"|"&D1,$G$1:$H$40000,2,0) ...

However, when I type something in say E1, this is copied in H1 but NOT all
the others down the column. Obviously, I'm doing something wrong. Could you
please try one last time, but step-by-step? One problem is that Excel
experts are so familiar with the procedures that they assume too much from
new users. Consider you are explaining everything to a five-year-old.

"Roger Govier" wrote:

Hi Rebecca

This may be a bit unwieldy with the size of the data table you are
using, but it should work.
I left a gap in column F and in G1 I entered
=B1&"|"&C1&"|"&D1
This is just a concatenation of your three entries, with the pipe
character | (shifted backslash) in between.
In H1 I entered
=E1
I then copied this down my sheet
In cell E1 I entered
=VLOOKUP(B1&"|"&C1&"|"&D1,$G$1:$H$40000,2,0) (I couldn't use 400,000
as I am still on XL2003)
and copied this down the sheet

Now, a you enter the English value in E1, it will get copied to H1 and
if there are any entries the same lower down the sheet, they will pick
up the translation.
Cells in column G will contain || and in column H will contain 0 for any
rows where you have not entered any values in B, C and D.
Equally, column E will contain 0, until there is a translation either
from a typed entry by you, or through having been found via the Vlookup
formula.

This will only work provided translations are made in order, i.e. the
translation would have to have been entered in row 1 of your example,
for row 3 to find it automatically.

At some point, when you have values in a contiguous range of cells in
column E, you can copy those cells then Paste SpecialValues over the
top of them to "fix" the values in place of the formulae.

I hope this helps in your mammoth undertaking.
IF you have any further problems, post back.



--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Roger for your useful advice. It is my fault for being so
sloppy in
my explanations. Please bear with me one more time.

My spreadsheet (Excel 2007) has about 400,000 rows, from Genesis to 2
Chronicles in the Hebrew Bible (or Tanach, if you prefer). Each row
has five
columns. See below for an example of a few rows (I can't use Hebrew
fonts
here so I'll just make up some words):

A B C D E
Gen 1:1 gjgj nsf gggj (empty)
Gen 1:1 gss ndm gga (empty)
Gen 1:1 gjgj nsf gggj (empty)

In Col. A is the book, chapter, and verse (for the first three words
in Gen.
1 you can see listed here)
In Col. B is the Hebrew word
In Col. C is the parsing of this word (case, gender, number, etc.)
In Col. D is the dictionary form of this Hebrew word
In Col. E is where the English translation of this word will go.

As you can see the first and third rows contain the same word (word,
parsing, and dictionary form) so as I proceed through the Bible,
translating
into English row-by-row, if I can insert the English translation in
all the
places where there is this (and subsequently others) three-column
combination, I can save a lot of time and insure translation
consistency.
Peo kindly gave me a formula to work with, but I found it too
cumbersome and
confusing to use with such a large spreadsheet. It would be nice if
the
search and replace function could include several columns in the
search box
(say three), and replace something in another column altogether.
Alas, if
everything were so simple. Could you please give me some advice on
how I can
do this, perhaps with a macro of something, though as I said above, I
am a
new user, and I need things explained in easy English? Thanks.



"Roger Govier" wrote:

Hi Rebecca

It is difficult to understand exactly what you are after from your
description here (with various letter combinations) or your posting
in
another ng with real words that Peo responded to.
You talk about 400,000 individual rows, each with a separate word,
but
you then talk about wanting to replace items where each occurrence of
the word appears in various chapters and verses.
For the latter, you would just use a Vlookup formula on your
dictionary
to insert the equivalent English/Hebrew translation.
I think we need a little clearer definition of what you are seeking
in
order to be able to help you.

In the interim, (and hoping I do not cause any offence by referring
you
to a Christian bible), John Walkenbach has set the whole of the King
James version of the Bible in an Excel workbook, with each chapter on
a
separate tab and some Formulae to find occurrences of words and some
VBA
code plus forms to extract verses.
I mention this as you might like to look at his methodology to see if
it
offers you any assistance in designing and implementing what you are
trying to do.
You can find John's file at
http://j-walkblog.com/index.php?/web...ible_in_excel/

--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Mr. Sjoblom, for your much-need help. I apologized for
posting in
another group, but as I said there, I am a new user, but since I am
already
here, well...

I hate to sound like a real dummy, but could you please explain
your
solution step-by-step? Once I put the formula you suggested in the
D
column,
then what do I do? Please read the rest of this post for some
details.

The entire spreadsheet has over 400,000 rows (and as I mentioned,
each
row
has one Hebrew word, one parsing of this word, and the dictionary
definition
of this word -- three columns in all -- plus the empty D column
where
the
English translation goes).

As I go down through the Bible, word by word, when I see the Hebrew
word for
"house," for example, I want to simply fill in the blanks where
this
word
(and its parsing and dictionary form) is located throughout the
various
chapters and verses. It would be nice if I could simply seach on
the
three
columns simultaneously-- A, B, and C -- and then replace in the
empty
D
column. But alas!

"Peo Sjoblom" wrote:

This is what I answered when you posted a similar question in new
users
albeit with different words and A, B and C

In D2 (or whatever row your data starts) put

=IF(AND(A2="hat",B2="bug",C2="flower"),"tree","")


copy down, if you want some more flexibility you can use

=IF(AND(A2=$F$1,B2=$G$1,C2=$H$1),"tree","")

where you would put the 3 words in F1, G1 and H1 respectively




--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Rebecca" wrote in message
...
Greetings, again. I think I asked for help in the wrong place,
so
please
forgive me for posting this again (though I am a new user,
indeed).
By
the
way, this is not a homework assignment, so feel free to give
some
advice
(in
step-by-step easy English, please).

I am using MS Excel 2007 (beta). I have four columns: A, B, C,
and
D (D
is
blank). If the Hebrew word in a particular row in Col. A is,
say,
"gggg,"
the parsing (grammar) in the same row in Col. B for that word is
"nbcpd,"
and
the Hebrew word in the same row in Col. C is "gggdd" (the
dictionary form
for
gggg in Col. A), I want to place the word "tree" in the same row
in
blank
column in Col. D (the English translation of the word in Col. A,
with the
information in Cols. B and C taken into consideration -- tense,
number,
gender, etc.).

I want to go down the spreadsheet one row / word at a time (each
row is
one
Hebrew word) and fill in every blank in Col D., but I want to
remain in
the
same row (or maybe come back to it, however this could be done)
and
translate
all the other rows with the same A, B, and C combinations.

I hope I haven't been too clumsy in explaining what I am trying
to
do. I
don't want to use Access for a reason I won't go into here
(there
are
certain
calculations that I would like to do later in Excel). Could
someone
please
help me with this problem? Thank you.

Hi Rebecca

No you haven't done anything wrong as far as I can see.

Firstly copying formulae down a range, there are various methods.
1. If you hover the mouse over the bottom right corner of a cell, the
cursor changes to a solid black cross - this is known as the "fill
handle". Grabbing this with your left mouse click, and dragging down,
will copy the formula down the through the range of cells until you
release the mouse button.
2. Double clicking the fill handle, will copy the formula down
automatically, for the range of cells that hold values in an adjacent
column.
3. Copy the formula, then select the range where you want to paste the
formula and Paste. You can either select the range using the mouse, or
by typing the range e.g. A1:A50 in the name box (the small white pane
above the row numbers and column A letter)
4. Select the range where you want the formula to go, enter the formula
in the formula bar at the top of screen, and press Ctrl+Enter instead of
just Enter.

Having set the formulae up (as you have described) then when you enter
an English word in E1, it will be repeated in H1, but it will not appear
anywhere else UNTIL you enter the same combination of values in B, C and
D (on a higher row number i.e. further down the sheet), whereupon if the
values match, THEN the corresponding English translation will appear.

If you are still having problems, email me direct and I will send you a
copy of what I set up. To mail direct, remove NOSPAM from my email
address.


--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Roger, for your help. I tried as best I could, but I couldn't
get it
to work (I'm a new user par excellance). Let me see if I understand
your
directions correctly.

First, I should leave a gap in F1 ...
Next, in G1 I should enter =B1&"|"&C1&"|"&D1 ...

(You didn't mention it, but I presume I copy this formula all the way
down
the column, right? By the way, what is the best way to copy,
something you
also didn't mention, perhaps because you assumed it was too obvious.)

Then I should enter =E1 in column H1, and copy this all the way down
the
column ...
Finally, in E1 I should enter
=VLOOKUP(B1&"|"&C1&"|"&D1,$G$1:$H$40000,2,0) ...

However, when I type something in say E1, this is copied in H1 but NOT
all
the others down the column. Obviously, I'm doing something wrong.
Could you
please try one last time, but step-by-step? One problem is that Excel
experts are so familiar with the procedures that they assume too much
from
new users. Consider you are explaining everything to a five-year-old.

"Roger Govier" wrote:

Hi Rebecca

This may be a bit unwieldy with the size of the data table you are
using, but it should work.
I left a gap in column F and in G1 I entered
=B1&"|"&C1&"|"&D1
This is just a concatenation of your three entries, with the pipe
character | (shifted backslash) in between.
In H1 I entered
=E1
I then copied this down my sheet
In cell E1 I entered
=VLOOKUP(B1&"|"&C1&"|"&D1,$G$1:$H$40000,2,0) (I couldn't use
400,000
as I am still on XL2003)
and copied this down the sheet

Now, a you enter the English value in E1, it will get copied to H1
and
if there are any entries the same lower down the sheet, they will
pick
up the translation.
Cells in column G will contain || and in column H will contain 0 for
any
rows where you have not entered any values in B, C and D.
Equally, column E will contain 0, until there is a translation either
from a typed entry by you, or through having been found via the
Vlookup
formula.

This will only work provided translations are made in order, i.e. the
translation would have to have been entered in row 1 of your example,
for row 3 to find it automatically.

At some point, when you have values in a contiguous range of cells in
column E, you can copy those cells then Paste SpecialValues over the
top of them to "fix" the values in place of the formulae.

I hope this helps in your mammoth undertaking.
IF you have any further problems, post back.



--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Roger for your useful advice. It is my fault for being so
sloppy in
my explanations. Please bear with me one more time.

My spreadsheet (Excel 2007) has about 400,000 rows, from Genesis to
2
Chronicles in the Hebrew Bible (or Tanach, if you prefer). Each
row
has five
columns. See below for an example of a few rows (I can't use
Hebrew
fonts
here so I'll just make up some words):

A B C D E
Gen 1:1 gjgj nsf gggj (empty)
Gen 1:1 gss ndm gga (empty)
Gen 1:1 gjgj nsf gggj (empty)

In Col. A is the book, chapter, and verse (for the first three
words
in Gen.
1 you can see listed here)
In Col. B is the Hebrew word
In Col. C is the parsing of this word (case, gender, number, etc.)
In Col. D is the dictionary form of this Hebrew word
In Col. E is where the English translation of this word will go.

As you can see the first and third rows contain the same word
(word,
parsing, and dictionary form) so as I proceed through the Bible,
translating
into English row-by-row, if I can insert the English translation in
all the
places where there is this (and subsequently others) three-column
combination, I can save a lot of time and insure translation
consistency.
Peo kindly gave me a formula to work with, but I found it too
cumbersome and
confusing to use with such a large spreadsheet. It would be nice
if
the
search and replace function could include several columns in the
search box
(say three), and replace something in another column altogether.
Alas, if
everything were so simple. Could you please give me some advice on
how I can
do this, perhaps with a macro of something, though as I said above,
I
am a
new user, and I need things explained in easy English? Thanks.



"Roger Govier" wrote:

Hi Rebecca

It is difficult to understand exactly what you are after from your
description here (with various letter combinations) or your
posting
in
another ng with real words that Peo responded to.
You talk about 400,000 individual rows, each with a separate word,
but
you then talk about wanting to replace items where each occurrence
of
the word appears in various chapters and verses.
For the latter, you would just use a Vlookup formula on your
dictionary
to insert the equivalent English/Hebrew translation.
I think we need a little clearer definition of what you are
seeking
in
order to be able to help you.

In the interim, (and hoping I do not cause any offence by
referring
you
to a Christian bible), John Walkenbach has set the whole of the
King
James version of the Bible in an Excel workbook, with each chapter
on
a
separate tab and some Formulae to find occurrences of words and
some
VBA
code plus forms to extract verses.
I mention this as you might like to look at his methodology to see
if
it
offers you any assistance in designing and implementing what you
are
trying to do.
You can find John's file at
http://j-walkblog.com/index.php?/web...ible_in_excel/

--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Mr. Sjoblom, for your much-need help. I apologized for
posting in
another group, but as I said there, I am a new user, but since I
am
already
here, well...

I hate to sound like a real dummy, but could you please explain
your
solution step-by-step? Once I put the formula you suggested in
the
D
column,
then what do I do? Please read the rest of this post for some
details.

The entire spreadsheet has over 400,000 rows (and as I
mentioned,
each
row
has one Hebrew word, one parsing of this word, and the
dictionary
definition
of this word -- three columns in all -- plus the empty D column
where
the
English translation goes).

As I go down through the Bible, word by word, when I see the
Hebrew
word for
"house," for example, I want to simply fill in the blanks where
this
word
(and its parsing and dictionary form) is located throughout the
various
chapters and verses. It would be nice if I could simply seach
on
the
three
columns simultaneously-- A, B, and C -- and then replace in the
empty
D
column. But alas!

"Peo Sjoblom" wrote:

This is what I answered when you posted a similar question in
new
users
albeit with different words and A, B and C

In D2 (or whatever row your data starts) put

=IF(AND(A2="hat",B2="bug",C2="flower"),"tree","")


copy down, if you want some more flexibility you can use

=IF(AND(A2=$F$1,B2=$G$1,C2=$H$1),"tree","")

where you would put the 3 words in F1, G1 and H1 respectively




--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Rebecca" wrote in message
...
Greetings, again. I think I asked for help in the wrong
place,
so
please
forgive me for posting this again (though I am a new user,
indeed).
By
the
way, this is not a homework assignment, so feel free to give
some
advice
(in
step-by-step easy English, please).

I am using MS Excel 2007 (beta). I have four columns: A, B,
C,
and
D (D
is
blank). If the Hebrew word in a particular row in Col. A is,
say,
"gggg,"
the parsing (grammar) in the same row in Col. B for that word
is
"nbcpd,"
and
the Hebrew word in the same row in Col. C is "gggdd" (the
dictionary form
for
gggg in Col. A), I want to place the word "tree" in the same
row
in
blank
column in Col. D (the English translation of the word in Col.
A,
with the
information in Cols. B and C taken into consideration --
tense,
number,
gender, etc.).

I want to go down the spreadsheet one row / word at a time
(each
row is
one
Hebrew word) and fill in every blank in Col D., but I want to
remain in
the
same row (or maybe come back to it, however this could be
done)
and
translate
all the other rows with the same A, B, and C combinations.

I hope I haven't been too clumsy in explaining what I am
trying
to
do. I
don't want to use Access for a reason I won't go into here
(there
are
certain
calculations that I would like to do later in Excel). Could
someone
please
help me with this problem? Thank you.

Thanks, Roger. It works perfectly. You're a big help, indeed.

"Roger Govier" wrote:

Hi Rebecca

No you haven't done anything wrong as far as I can see.

Firstly copying formulae down a range, there are various methods.
1. If you hover the mouse over the bottom right corner of a cell, the
cursor changes to a solid black cross - this is known as the "fill
handle". Grabbing this with your left mouse click, and dragging down,
will copy the formula down the through the range of cells until you
release the mouse button.
2. Double clicking the fill handle, will copy the formula down
automatically, for the range of cells that hold values in an adjacent
column.
3. Copy the formula, then select the range where you want to paste the
formula and Paste. You can either select the range using the mouse, or
by typing the range e.g. A1:A50 in the name box (the small white pane
above the row numbers and column A letter)
4. Select the range where you want the formula to go, enter the formula
in the formula bar at the top of screen, and press Ctrl+Enter instead of
just Enter.

Having set the formulae up (as you have described) then when you enter
an English word in E1, it will be repeated in H1, but it will not appear
anywhere else UNTIL you enter the same combination of values in B, C and
D (on a higher row number i.e. further down the sheet), whereupon if the
values match, THEN the corresponding English translation will appear.

If you are still having problems, email me direct and I will send you a
copy of what I set up. To mail direct, remove NOSPAM from my email
address.


--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Roger, for your help. I tried as best I could, but I couldn't
get it
to work (I'm a new user par excellance). Let me see if I understand
your
directions correctly.

First, I should leave a gap in F1 ...
Next, in G1 I should enter =B1&"|"&C1&"|"&D1 ...

(You didn't mention it, but I presume I copy this formula all the way
down
the column, right? By the way, what is the best way to copy,
something you
also didn't mention, perhaps because you assumed it was too obvious.)

Then I should enter =E1 in column H1, and copy this all the way down
the
column ...
Finally, in E1 I should enter
=VLOOKUP(B1&"|"&C1&"|"&D1,$G$1:$H$40000,2,0) ...

However, when I type something in say E1, this is copied in H1 but NOT
all
the others down the column. Obviously, I'm doing something wrong.
Could you
please try one last time, but step-by-step? One problem is that Excel
experts are so familiar with the procedures that they assume too much
from
new users. Consider you are explaining everything to a five-year-old.

"Roger Govier" wrote:

Hi Rebecca

This may be a bit unwieldy with the size of the data table you are
using, but it should work.
I left a gap in column F and in G1 I entered
=B1&"|"&C1&"|"&D1
This is just a concatenation of your three entries, with the pipe
character | (shifted backslash) in between.
In H1 I entered
=E1
I then copied this down my sheet
In cell E1 I entered
=VLOOKUP(B1&"|"&C1&"|"&D1,$G$1:$H$40000,2,0) (I couldn't use
400,000
as I am still on XL2003)
and copied this down the sheet

Now, a you enter the English value in E1, it will get copied to H1
and
if there are any entries the same lower down the sheet, they will
pick
up the translation.
Cells in column G will contain || and in column H will contain 0 for
any
rows where you have not entered any values in B, C and D.
Equally, column E will contain 0, until there is a translation either
from a typed entry by you, or through having been found via the
Vlookup
formula.

This will only work provided translations are made in order, i.e. the
translation would have to have been entered in row 1 of your example,
for row 3 to find it automatically.

At some point, when you have values in a contiguous range of cells in
column E, you can copy those cells then Paste SpecialValues over the
top of them to "fix" the values in place of the formulae.

I hope this helps in your mammoth undertaking.
IF you have any further problems, post back.



--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Roger for your useful advice. It is my fault for being so
sloppy in
my explanations. Please bear with me one more time.

My spreadsheet (Excel 2007) has about 400,000 rows, from Genesis to
2
Chronicles in the Hebrew Bible (or Tanach, if you prefer). Each
row
has five
columns. See below for an example of a few rows (I can't use
Hebrew
fonts
here so I'll just make up some words):

A B C D E
Gen 1:1 gjgj nsf gggj (empty)
Gen 1:1 gss ndm gga (empty)
Gen 1:1 gjgj nsf gggj (empty)

In Col. A is the book, chapter, and verse (for the first three
words
in Gen.
1 you can see listed here)
In Col. B is the Hebrew word
In Col. C is the parsing of this word (case, gender, number, etc.)
In Col. D is the dictionary form of this Hebrew word
In Col. E is where the English translation of this word will go.

As you can see the first and third rows contain the same word
(word,
parsing, and dictionary form) so as I proceed through the Bible,
translating
into English row-by-row, if I can insert the English translation in
all the
places where there is this (and subsequently others) three-column
combination, I can save a lot of time and insure translation
consistency.
Peo kindly gave me a formula to work with, but I found it too
cumbersome and
confusing to use with such a large spreadsheet. It would be nice
if
the
search and replace function could include several columns in the
search box
(say three), and replace something in another column altogether.
Alas, if
everything were so simple. Could you please give me some advice on
how I can
do this, perhaps with a macro of something, though as I said above,
I
am a
new user, and I need things explained in easy English? Thanks.



"Roger Govier" wrote:

Hi Rebecca

It is difficult to understand exactly what you are after from your
description here (with various letter combinations) or your
posting
in
another ng with real words that Peo responded to.
You talk about 400,000 individual rows, each with a separate word,
but
you then talk about wanting to replace items where each occurrence
of
the word appears in various chapters and verses.
For the latter, you would just use a Vlookup formula on your
dictionary
to insert the equivalent English/Hebrew translation.
I think we need a little clearer definition of what you are
seeking
in
order to be able to help you.

In the interim, (and hoping I do not cause any offence by
referring
you
to a Christian bible), John Walkenbach has set the whole of the
King
James version of the Bible in an Excel workbook, with each chapter
on
a
separate tab and some Formulae to find occurrences of words and
some
VBA
code plus forms to extract verses.
I mention this as you might like to look at his methodology to see
if
it
offers you any assistance in designing and implementing what you
are
trying to do.
You can find John's file at
http://j-walkblog.com/index.php?/web...ible_in_excel/

--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Mr. Sjoblom, for your much-need help. I apologized for
posting in
another group, but as I said there, I am a new user, but since I
am
already
here, well...

I hate to sound like a real dummy, but could you please explain
your
solution step-by-step? Once I put the formula you suggested in
the
D
column,
then what do I do? Please read the rest of this post for some
details.

The entire spreadsheet has over 400,000 rows (and as I
mentioned,
each
row
has one Hebrew word, one parsing of this word, and the
dictionary
definition
of this word -- three columns in all -- plus the empty D column
where
the
English translation goes).

As I go down through the Bible, word by word, when I see the
Hebrew
word for
"house," for example, I want to simply fill in the blanks where
this
word
(and its parsing and dictionary form) is located throughout the
various
chapters and verses. It would be nice if I could simply seach
on
the
three
columns simultaneously-- A, B, and C -- and then replace in the
empty
D
column. But alas!

"Peo Sjoblom" wrote:

This is what I answered when you posted a similar question in
new
users
albeit with different words and A, B and C

In D2 (or whatever row your data starts) put

=IF(AND(A2="hat",B2="bug",C2="flower"),"tree","")


copy down, if you want some more flexibility you can use

=IF(AND(A2=$F$1,B2=$G$1,C2=$H$1),"tree","")

where you would put the 3 words in F1, G1 and H1 respectively




--

Regards,

Peo Sjoblom

Hi Rebecca

You're very welcome. Glad you got the file OK, and good luck with the
huge project.

--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Roger. It works perfectly. You're a big help, indeed.

"Roger Govier" wrote:

Hi Rebecca

No you haven't done anything wrong as far as I can see.

Firstly copying formulae down a range, there are various methods.
1. If you hover the mouse over the bottom right corner of a cell, the
cursor changes to a solid black cross - this is known as the "fill
handle". Grabbing this with your left mouse click, and dragging down,
will copy the formula down the through the range of cells until you
release the mouse button.
2. Double clicking the fill handle, will copy the formula down
automatically, for the range of cells that hold values in an adjacent
column.
3. Copy the formula, then select the range where you want to paste
the
formula and Paste. You can either select the range using the mouse,
or
by typing the range e.g. A1:A50 in the name box (the small white pane
above the row numbers and column A letter)
4. Select the range where you want the formula to go, enter the
formula
in the formula bar at the top of screen, and press Ctrl+Enter instead
of
just Enter.

Having set the formulae up (as you have described) then when you
enter
an English word in E1, it will be repeated in H1, but it will not
appear
anywhere else UNTIL you enter the same combination of values in B, C
and
D (on a higher row number i.e. further down the sheet), whereupon if
the
values match, THEN the corresponding English translation will appear.

If you are still having problems, email me direct and I will send you
a
copy of what I set up. To mail direct, remove NOSPAM from my email
address.


--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Roger, for your help. I tried as best I could, but I
couldn't
get it
to work (I'm a new user par excellance). Let me see if I
understand
your
directions correctly.

First, I should leave a gap in F1 ...
Next, in G1 I should enter =B1&"|"&C1&"|"&D1 ...

(You didn't mention it, but I presume I copy this formula all the
way
down
the column, right? By the way, what is the best way to copy,
something you
also didn't mention, perhaps because you assumed it was too
obvious.)

Then I should enter =E1 in column H1, and copy this all the way
down
the
column ...
Finally, in E1 I should enter
=VLOOKUP(B1&"|"&C1&"|"&D1,$G$1:$H$40000,2,0) ...

However, when I type something in say E1, this is copied in H1 but
NOT
all
the others down the column. Obviously, I'm doing something wrong.
Could you
please try one last time, but step-by-step? One problem is that
Excel
experts are so familiar with the procedures that they assume too
much
from
new users. Consider you are explaining everything to a
five-year-old.

"Roger Govier" wrote:

Hi Rebecca

This may be a bit unwieldy with the size of the data table you are
using, but it should work.
I left a gap in column F and in G1 I entered
=B1&"|"&C1&"|"&D1
This is just a concatenation of your three entries, with the pipe
character | (shifted backslash) in between.
In H1 I entered
=E1
I then copied this down my sheet
In cell E1 I entered
=VLOOKUP(B1&"|"&C1&"|"&D1,$G$1:$H$40000,2,0) (I couldn't use
400,000
as I am still on XL2003)
and copied this down the sheet

Now, a you enter the English value in E1, it will get copied to H1
and
if there are any entries the same lower down the sheet, they will
pick
up the translation.
Cells in column G will contain || and in column H will contain 0
for
any
rows where you have not entered any values in B, C and D.
Equally, column E will contain 0, until there is a translation
either
from a typed entry by you, or through having been found via the
Vlookup
formula.

This will only work provided translations are made in order, i.e.
the
translation would have to have been entered in row 1 of your
example,
for row 3 to find it automatically.

At some point, when you have values in a contiguous range of cells
in
column E, you can copy those cells then Paste SpecialValues over
the
top of them to "fix" the values in place of the formulae.

I hope this helps in your mammoth undertaking.
IF you have any further problems, post back.



--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Roger for your useful advice. It is my fault for being
so
sloppy in
my explanations. Please bear with me one more time.

My spreadsheet (Excel 2007) has about 400,000 rows, from Genesis
to
2
Chronicles in the Hebrew Bible (or Tanach, if you prefer). Each
row
has five
columns. See below for an example of a few rows (I can't use
Hebrew
fonts
here so I'll just make up some words):

A B C D E
Gen 1:1 gjgj nsf gggj (empty)
Gen 1:1 gss ndm gga (empty)
Gen 1:1 gjgj nsf gggj (empty)

In Col. A is the book, chapter, and verse (for the first three
words
in Gen.
1 you can see listed here)
In Col. B is the Hebrew word
In Col. C is the parsing of this word (case, gender, number,
etc.)
In Col. D is the dictionary form of this Hebrew word
In Col. E is where the English translation of this word will go.

As you can see the first and third rows contain the same word
(word,
parsing, and dictionary form) so as I proceed through the Bible,
translating
into English row-by-row, if I can insert the English translation
in
all the
places where there is this (and subsequently others)
three-column
combination, I can save a lot of time and insure translation
consistency.
Peo kindly gave me a formula to work with, but I found it too
cumbersome and
confusing to use with such a large spreadsheet. It would be
nice
if
the
search and replace function could include several columns in the
search box
(say three), and replace something in another column altogether.
Alas, if
everything were so simple. Could you please give me some advice
on
how I can
do this, perhaps with a macro of something, though as I said
above,
I
am a
new user, and I need things explained in easy English? Thanks.



"Roger Govier" wrote:

Hi Rebecca

It is difficult to understand exactly what you are after from
your
description here (with various letter combinations) or your
posting
in
another ng with real words that Peo responded to.
You talk about 400,000 individual rows, each with a separate
word,
but
you then talk about wanting to replace items where each
occurrence
of
the word appears in various chapters and verses.
For the latter, you would just use a Vlookup formula on your
dictionary
to insert the equivalent English/Hebrew translation.
I think we need a little clearer definition of what you are
seeking
in
order to be able to help you.

In the interim, (and hoping I do not cause any offence by
referring
you
to a Christian bible), John Walkenbach has set the whole of the
King
James version of the Bible in an Excel workbook, with each
chapter
on
a
separate tab and some Formulae to find occurrences of words and
some
VBA
code plus forms to extract verses.
I mention this as you might like to look at his methodology to
see
if
it
offers you any assistance in designing and implementing what
you
are
trying to do.
You can find John's file at
http://j-walkblog.com/index.php?/web...ible_in_excel/

--
Regards

Roger Govier


"Rebecca" wrote in message
...
Thanks, Mr. Sjoblom, for your much-need help. I apologized
for
posting in
another group, but as I said there, I am a new user, but
since I
am
already
here, well...

I hate to sound like a real dummy, but could you please
explain
your
solution step-by-step? Once I put the formula you suggested
in
the
D
column,
then what do I do? Please read the rest of this post for
some
details.

The entire spreadsheet has over 400,000 rows (and as I
mentioned,
each
row
has one Hebrew word, one parsing of this word, and the
dictionary
definition
of this word -- three columns in all -- plus the empty D
column
where
the
English translation goes).

As I go down through the Bible, word by word, when I see the
Hebrew
word for
"house," for example, I want to simply fill in the blanks
where
this
word
(and its parsing and dictionary form) is located throughout
the
various
chapters and verses. It would be nice if I could simply
seach
on
the
three
columns simultaneously-- A, B, and C -- and then replace in
the
empty
D
column. But alas!

"Peo Sjoblom" wrote:

This is what I answered when you posted a similar question
in
new
users
albeit with different words and A, B and C

In D2 (or whatever row your data starts) put

=IF(AND(A2="hat",B2="bug",C2="flower"),"tree","")


copy down, if you want some more flexibility you can use

=IF(AND(A2=$F$1,B2=$G$1,C2=$H$1),"tree","")

where you would put the 3 words in F1, G1 and H1
respectively




--

Regards,

Peo Sjoblom