Prepopulatiing Excel cells with exact matches.
Thread poster: George Rabel

George Rabel  Identity Verified
Local time: 01:17
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  Identity Verified
Portugal
Local time: 06:17
Dutch to English
+ ...
Get CAT software Sep 5, 2018

and Bob's yer Uncle

 

Tony M
France
Local time: 07:17
Member
French to English
+ ...
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  Identity Verified
Local time: 07:17
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
Switzerland
Local time: 07:17
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


Chris S
 

George Rabel  Identity Verified
Local time: 01:17
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
Switzerland
Local time: 07:17
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: 07:17
Member
French to English
+ ...
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  Identity Verified
Local time: 01:17
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.

Advanced search






SDL MultiTerm 2019
Guarantee a unified, consistent and high-quality translation with terminology software by the industry leaders.

SDL MultiTerm 2019 allows translators to create one central location to store and manage multilingual terminology, and with SDL MultiTerm Extract 2019 you can automatically create term lists from your existing documentation to save time.

More info »
PerfectIt consistency checker
Faster Checking, Greater Accuracy

PerfectIt helps deliver error-free documents. It improves consistency, ensures quality and helps to enforce style guides. It’s a powerful tool for pro users, and comes with the assurance of a 30-day money back guarantee.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search