Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is This POSSIBLE
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|