Excel 2003: Is there a way to copy (to translate) certain highlighted rows only?
Thread poster: Claudia Alvis

Claudia Alvis  Identity Verified
Peru
Local time: 15:06
Spanish
+ ...
Jul 10, 2009

I received an Excel (I have Office 2003) file with the strings of a software. The file has several columns, some of them are for references, notes, context, languages, etc., and the translatable strings are in another column. Some of the strings are urgent and I was told to translate them first.

But those urgent strings are scattered throughout the file, and they're marked as urgent by the highlighting of their column notes (not the translatable column). Like this:


I need to select ONLY the 'English US' column of the rows highlighted in green

I want to copy those urgent strings and paste them into a separate file, translate that file and deliver it first. Then I can go on to working on the rest of the strings. But I have thousands of strings and I don't know how to do select JUST those "highlighted" strings.

I guess I could expand the highlighting to the translatable column or something similar, but I have no idea if that's even possible. Does anybody has ANY advice on how can I do this? (I mean ANY) In the meantime I can select the urgent strings manually, but that's gonna take me forever.

Did I say I'd appreciate ANY advice?

Claudia

[Edited at 2009-07-10 13:31 GMT]


Direct link Reply with quote
 
oberonsghost
Local time: 06:06
Greek to English
+ ...
Excel 2003: Using Autofilter filter to copy certain highlighted rows only?" Jul 10, 2009

I'd take this approach.

Firstly, in the Excel file, select all the data in the spread sheet then go to the Data Menu, pull down until you see the Filter sub-menu then select Autofilter.

The Autofilter lets filter the spreadsheet for selected values in a column.

You'll then see a little arrow appear on the right-hand side of each cell that is a column heading for the data in the spreadsheet. This little arrow indicates a pulldown menu of all the different values in that column.

Then look for any common text strings running through the highlighted rows. For example, are they all "voice and text" from column C? or all "flo_present" from column E?. When you have found something that they have in common go to that column heading, pull down until you find the value that you are looking for. When you select it, it will only present rows that include that value. You can then select and copy those rows into a new spreadsheet or document so that you can provide the translation. You may have to repeat this process, depending on how the spreadsheet has been set out.

Cheers

M



Direct link Reply with quote
 

Laura Mussutto  Identity Verified
United Kingdom
Local time: 21:06
Member (2009)
English to Italian
+ ...
try using filters Jul 10, 2009

The only way I can suggest is use filters: select Data from the menu, then Filter > Autofilter
If all the "urgent" strings all have the same string name (e.g. flo_present) you only have to filter by flo_present and that's it, you get your list ready to copy.

If not, you need to do a bit of work but it shouldn't take too long:
- first create a new column next to Strings Name, call it Urgent for argument's sake (don't worry about adding things, just close the doc without saving the changes once you're done)
- then filter by one string name at a time, put any symbol you want in the first cell of the column, let's say you put a Y for yes. To copy the Y down to the last cell, just point your mouse to the bottom right corner of the cell and drag down
- once you are sure you have marked each and every urgent string (good idea to remove the filter and examine the whole doc), then re-apply the filter and go to your Urgent column and filter it by Y.
Now your doc will only show what you need to do urgently, and you can easily copy and paste the data into another spreadsheet.

I hope this doesn't sound untintelligible, I've tried my best to make it sound clear! But do have a go: filters are wonderful once you get to understand how they work!

HTH,
Laura


Direct link Reply with quote
 
Jalapeno
Local time: 22:06
English to German
... Jul 10, 2009

Laura's first option would be ideal, of course, but I doubt all the urgent strings have the same ID. How about the "Text and voice" column, however? Perhaps these strings are so urgent because they need to be recorded. In that case, sorting by the "Text and voice" column will solve your problem immediately.

If you can't sort the file by any column or by using a filter, Laura's second option is also possible but would probably amount to a lot of work since your file is so big.

There's a third option, but it involves the use of VBA, which you might hesitate to use if you've never dabbled around with it. Anyway, if you're up for it, this website tells you how to do it:

http://www.cpearson.com/excel/SortByColor.aspx

Please note that I haven't tried it, but it *should* work.

[Edited at 2009-07-10 14:13 GMT]


Direct link Reply with quote
 

N.M. Eklund  Identity Verified
France
Local time: 22:06
Member (2005)
French to English
+ ...
Yippee I can help!! Jul 10, 2009

I love this thing. I discovered it a while ago and can't wait to pass it on.
It's like waving a magic wand over all those frustrating limitations and irritating repetitive tasks in Excel.

Download ASAP utilities! http://www.asap-utilities.com
It's a free macro for your Excel that gives you superpowers.

For your situation, you can sort by highlighted colors and select to translate those cells you need.

I found it because I was stuck translating over a thousand lines of text from one column, where each excel line contained at least 10 lines of text (using line break Alt+L function in Excel)
It copied over nicely into Word for translation, each cell copied correctly in the right cell in Word. BUT, to copy it back, it didn't work! Excel misread it as 'Enter' and I ended up with one excel line per line of text! It would have taken days to correct the formatting (up the proverbial creek without a paddle).
After digging through programming forums, I found out to my chagrin that there was no Excel option to resolve this. Apparently this is an incompatibility bug inherent in the Excel software.
Fortunately, I found this macro online; and it worked perfectly!!!

It's just full of little goodies, and I haven't finished exploring it yet.

Hope this helps!


Direct link Reply with quote
 

Claudia Alvis  Identity Verified
Peru
Local time: 15:06
Spanish
+ ...
TOPIC STARTER
Thanks! Jul 10, 2009

Thanks! N.M., you just made me so happy Thank you Laura too, but it seems that this macro is the perfect solution. I'll download it and let you know how it went.

Direct link Reply with quote
 
FarkasAndras
Local time: 22:06
English to Hungarian
+ ...
soft line breaks Jul 10, 2009

N.M. Eklund wrote:

I love this thing. I discovered it a while ago and can't wait to pass it on.
It's like waving a magic wand over all those frustrating limitations and irritating repetitive tasks in Excel.

Download ASAP utilities! http://www.asap-utilities.com
It's a free macro for your Excel that gives you superpowers.

For your situation, you can sort by highlighted colors and select to translate those cells you need.

I found it because I was stuck translating over a thousand lines of text from one column, where each excel line contained at least 10 lines of text (using line break Alt+L function in Excel)
It copied over nicely into Word for translation, each cell copied correctly in the right cell in Word. BUT, to copy it back, it didn't work! Excel misread it as 'Enter' and I ended up with one excel line per line of text! It would have taken days to correct the formatting (up the proverbial creek without a paddle).
After digging through programming forums, I found out to my chagrin that there was no Excel option to resolve this. Apparently this is an incompatibility bug inherent in the Excel software.
Fortunately, I found this macro online; and it worked perfectly!!!

It's just full of little goodies, and I haven't finished exploring it yet.

Hope this helps!




I recently had to look up soft line breaks in Excel and found out that it can be handled with search and replace - its character code is 0010. I.e. you can just open your Excel file, hit Ctrl-H, replace character Alt-0010 with, say, *$*, copy to Word, translate, copy back to Excel and replace *$* with Alt-0010 to get your line breaks back.

Still, the macro could come in handy.


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Excel 2003: Is there a way to copy (to translate) certain highlighted rows only?

Advanced search






Protemos translation business management system
Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!

The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.

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

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

More info »



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