Prepopulatiing Excel cells with exact matches.
Thread poster: George Rabel
George Rabel
George Rabel  Identity Verified
Local time: 15:43
English to Spanish
+ ...
Sep 5, 2018

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


 
Richard Purdom
Richard Purdom  Identity Verified
Portugal
Local time: 19:43
Dutch to English
+ ...
Get CAT software Sep 5, 2018

and Bob's yer Uncle

 
Tony M
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


 
Cilian O'Tuama
Cilian O'Tuama  Identity Verified
Germany
Local time: 20:43
German to English
+ ...
A workaround Sep 5, 2018

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
Daniel Frisano  Identity Verified
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


Christopher Schröder
 
George Rabel
George Rabel  Identity Verified
Local time: 15:43
English to Spanish
+ ...
TOPIC STARTER
@Tony Sep 5, 2018

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
Daniel Frisano  Identity Verified
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
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
George Rabel  Identity Verified
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 »