Prepopulatiing Excel cells with exact matches. Thread poster: George Rabel
|
I'm working on a huge Excel file with a lot of repetitions. I am trying to find a way to prepopulate the rows where a There are only four or five variables, but each of them is repeated multiple times, but not necessarily in the same order. Example: COLUMN A | COLUMN B More information on official site | Más información en el sitio oficial Click here to access your account | Haga clic aquí ... See more I'm working on a huge Excel file with a lot of repetitions. I am trying to find a way to prepopulate the rows where a There are only four or five variables, but each of them is repeated multiple times, but not necessarily in the same order. Example: COLUMN A | COLUMN B More information on official site | Más información en el sitio oficial Click here to access your account | Haga clic aquí para entrar a su cuenta Sign here | Firme aquí Date of Birth | Fecha de nacimiento Click here to access your account | More information on official site | Sign here | Date of Birth | I'm trying to find a way to prepopulate Column B with the corresponding translation every time an exact match is found. There must be a formula. Something like: When A= More information on official site, then B = Más información en el sitio oficial. I know every time you start typing in a cell you get matches, but I'm talking about 800 rows here. Very time consuming to do it that way. There must be a way to make it automatic ▲ Collapse | | | Get CAT software | Sep 5, 2018 |
and Bob's yer Uncle | | | Tony M France Local time: 20:43 Member French to English + ... SITE LOCALIZER A few questions... | Sep 5, 2018 |
First of all, every time Phrase A appears, does its translation ALWAYS exist? If so, can't you use the Excel 'duplicate content' function to simply group all the pre-translated cells together, in order to leave you with the untranslated rows clear to work on below? Also, surely a CAT tool would do this for your automatically? I have to admit, I'm not entirely clear exactly where the problem lies? Are you saying that a translation exists for Phrase A, but there are instan... See more First of all, every time Phrase A appears, does its translation ALWAYS exist? If so, can't you use the Excel 'duplicate content' function to simply group all the pre-translated cells together, in order to leave you with the untranslated rows clear to work on below? Also, surely a CAT tool would do this for your automatically? I have to admit, I'm not entirely clear exactly where the problem lies? Are you saying that a translation exists for Phrase A, but there are instances where it occirs but does not have the translation with it? In which case, surely your CAT tool will take care of it? ▲ Collapse | | |
Copy the contents of column A into column B. Then highlight column B and run your 4-5 search&replace operations. Search "More information on...", replace with "Más información en..." etc. Highlighting the column restricts the search and replace to that column only. I'm posting this at 19:31 - might take a while for it to appear, though. | |
|
|
Daniel Frisano Italy Local time: 20:43 Member (2008) English to Italian + ... Fast semiautomatic procedure | Sep 5, 2018 |
1) Insert blank column before column A. Now B is your source and C your target. 2) Populate column A with row numbers by inserting =ROW() in each cell, then copy column A and paste it AS VALUES over itself to get rid of the formulas. 3) Sort columns A, B, C together alphabetically according to column B. Now all source text is in alphabetical order and you can quickly copy-and-paste over several consecutive rows. 4) Once column C is full, sort columns A, B, ... See more 1) Insert blank column before column A. Now B is your source and C your target. 2) Populate column A with row numbers by inserting =ROW() in each cell, then copy column A and paste it AS VALUES over itself to get rid of the formulas. 3) Sort columns A, B, C together alphabetically according to column B. Now all source text is in alphabetical order and you can quickly copy-and-paste over several consecutive rows. 4) Once column C is full, sort columns A, B, C according to column A to restore the original order. 5) Delete column A. ▲ Collapse | | | George Rabel Local time: 15:43 English to Spanish + ... TOPIC STARTER
Thanks for your answer. There are no prepopulated cells with translations. The English is on one column, and I have to provide the translation in the cell to the right. There are only 4 short phrases, repeated over and over, for a total of 600 rows, with a random number of empty rows between each group of populated cells. It is like this: yes no hello goodbye yes no hello goodbye yes ... See more Thanks for your answer. There are no prepopulated cells with translations. The English is on one column, and I have to provide the translation in the cell to the right. There are only 4 short phrases, repeated over and over, for a total of 600 rows, with a random number of empty rows between each group of populated cells. It is like this: yes no hello goodbye yes no hello goodbye yes no hello goodbye I think Cilian's suggestion may just work here, and save me a lot of time, but I still think there must be a formula. Something like "when A = yes, B = sí" This is only a fraction of the whole project. ▲ Collapse | | | Daniel Frisano Italy Local time: 20:43 Member (2008) English to Italian + ... Another option | Sep 5, 2018 |
1) Copy column A to column D. 2) Select column D, then click on Data > Remove duplicates. Now each unique entry appears exactly once. 3) Translate into column E. 4) In cell B1 type =VLOOKUP(A1;D:E;2;0), then copy and paste down column B as needed. 5) Once column B is full, select it, then copy and paste it over itself as values. 6) Remove all the #N/A values via search-and-replace (search #N/A, replace with nothing). | | | Tony M France Local time: 20:43 Member French to English + ... SITE LOCALIZER Ah well in THAT case...! | Sep 5, 2018 |
Copy source text column into empty target cloumn Hide existing source text column (so it won't get translated) Translate copied column using your CAT tool — it won't care about the row spacing etc. — you could easily do this even using an old, free, demo version of Wordfast Classic! Clean up the translated file Then unhide the original source column, and Bob's your Tio | |
|
|
George Rabel Local time: 15:43 English to Spanish + ... TOPIC STARTER Thanks to all for your help. | Sep 6, 2018 |
I tried several different options, and I'm sure they probably work just fine if executed correctly, but the problem is me. I requested help elsewhere and received a number of suggestions with functions such as IF, CHOOSE and REPLACE, and even a detailed macro. I tried all of them without success. About using TM, this is an Excel file with 7 tabs, and a lot more than neat rows and columns. To complicate things further, only some content (highlighted in yellow) is to be translated. W... See more I tried several different options, and I'm sure they probably work just fine if executed correctly, but the problem is me. I requested help elsewhere and received a number of suggestions with functions such as IF, CHOOSE and REPLACE, and even a detailed macro. I tried all of them without success. About using TM, this is an Excel file with 7 tabs, and a lot more than neat rows and columns. To complicate things further, only some content (highlighted in yellow) is to be translated. When I load that into my Wordfast, it loses all formatting, and I do not have the add on version that works within the application (assuming there is one for Excel, as there is for Word.) The good news is that I'm getting paid by the hour, so I have plenty of time to copy and paste like an idiot and curse the evil being who had the perverse idea of using Excel for translations. ▲ Collapse | | | To report site rules violations or get help, contact a site moderator: You can also contact site staff by submitting a support request » Prepopulatiing Excel cells with exact matches. Wordfast Pro | Translation Memory Software for Any Platform
Exclusive discount for ProZ.com users!
Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value
Buy now! » |
| TM-Town | Manage your TMs and Terms ... and boost your translation business
Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |