Prepopulatiing Excel cells with exact matches. เธรดต่อผู้เขียนข้อความ: George Rabel
|
|---|
George Rabel 
Local time: 17:02
ภาษาอังกฤษ เป็น ภาษาสเปน
+ ...
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
โปรตุเกส
Local time: 22:02
ภาษาดัช เป็น ภาษาอังกฤษ
+ ...
| Get CAT software | Sep 5, 2018 |
and Bob's yer Uncle
| | | | Tony M
ฝรั่งเศส
Local time: 23:02
ภาษาฝรั่งเศส เป็น ภาษาอังกฤษ
+ ...
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
เยอรมนี
Local time: 23:02
ภาษาเยอรมัน เป็น ภาษาอังกฤษ
+ ...
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
อิตาลี
Local time: 23:02
สมาชิก (2008)
ภาษาอังกฤษ เป็น ภาษาอิตาลี
+ ...
| 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: 17:02
ภาษาอังกฤษ เป็น ภาษาสเปน
+ ...
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
อิตาลี
Local time: 23:02
สมาชิก (2008)
ภาษาอังกฤษ เป็น ภาษาอิตาลี
+ ...
| 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
ฝรั่งเศส
Local time: 23:02
ภาษาฝรั่งเศส เป็น ภาษาอังกฤษ
+ ...
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: 17:02
ภาษาอังกฤษ เป็น ภาษาสเปน
+ ...
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. | Pastey |
|---|
Your smart companion app
Pastey is an innovative desktop application that bridges the gap between human expertise and artificial intelligence. With intuitive keyboard shortcuts, Pastey transforms your source text into AI-powered draft translations.
Find out more » |
| | 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! » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
