Excel hide rows
Thread poster: Terry Richards
Terry Richards
Terry Richards
France
Local time: 10:55
French to English
+ ...
Jul 21, 2017

I have an Excel file to translate where everything in column C must be translated into column D. The problem is that some terms are already translated. I can do this with my CAT tool (Wordfast classic) by hiding all the rows where column D is already populated. The problem is that the file is BIG (> 10,000 rows) and the terms already translated are scattered throughout the file. I don't really want to go through the file line by line hiding all the lines where column D is non-blank.

... See more
I have an Excel file to translate where everything in column C must be translated into column D. The problem is that some terms are already translated. I can do this with my CAT tool (Wordfast classic) by hiding all the rows where column D is already populated. The problem is that the file is BIG (> 10,000 rows) and the terms already translated are scattered throughout the file. I don't really want to go through the file line by line hiding all the lines where column D is non-blank.

My first thought was to sort on column D thereby bring all the blank and non-blank lines together but I am not allowed to sort the file - it must remain in the same sequence it is currently in.

My second thought is to add an extra column and (somehow) populate it with the current line number. I could then sort the file how I want it and, when I'm done, sort on this new column to put it back in the original order and then delete the extra column. The problem is that I can't find any way to populate the field with the row number that doesn't change when the file is sorted.

Any changes I make to the file (except my translations) must be reversible.

So, does anybody know how to:

A) Populate a cell with the current row number in such a way that it doesn't change if I sort the file

Or:

B) Hide all rows where column D is not empty

Or:

C) Some other solution I haven't thought of.

TIA
Collapse


 
Tony M
Tony M
France
Local time: 10:55
Member
French to English
+ ...
SITE LOCALIZER
Copy to Word? Jul 21, 2017

I gave up trying to translate directly in Excel a long time ago!

I don't know whether my workflow would work for your particualr document, but I find this works very well for me.

In Excel:
1) Create a column iwth incrementing line numbers, style L = (L–1)+1 using 'fill down'

2) Copy entire sheet into a Word table

3) Sort on your partly-translated column, text, decreasing; this will group all the translated text together at the top. The
... See more
I gave up trying to translate directly in Excel a long time ago!

I don't know whether my workflow would work for your particualr document, but I find this works very well for me.

In Excel:
1) Create a column iwth incrementing line numbers, style L = (L–1)+1 using 'fill down'

2) Copy entire sheet into a Word table

3) Sort on your partly-translated column, text, decreasing; this will group all the translated text together at the top. The line numbers won't change, as you are sorting in Word, not Excel.

4) Set ALL current text as 'untranslatable' (and while you're at it, 'do not spellcheck')

5) Copy (using F8) all the untranslated column 1 text into the blank part of Columne 2, and un-set the 'untranslatable' attribute

6) Translate using Wordfast as normal, and clean up.

7) Removal all highlighting

8) Re-sort your text on the serial numbers you created in Column 0

9) 'Select table' and copy back into your XLS.

10) Delete column containing your serial numbers

... and Bob's your uncle!

Takes a lot longer to explain than to actually do it!

The only snag I've found is IF any of your cells have 'manual line breaks' within them, in which case they will create suprious extra rows when you copy back.

In the Word file, you can search-&-replace for ^l (lower-case letter L) and replace it with some character or string that couldn't possible exist in your XLS, then after copying back into XLS, do a reverse search-&-replace for thet character/string and replace with the manual line break character — which in Excel is I think the code Alt + 10 [num] — but check that by experimenting first! It looks like 'nothing' in the 'replace' field, but if you get the right code, it WILL work in practice.

If you can't copy to word, then create your 'line number' column as normal, THEN copy-&-paste it alongside using 'VALUES ONLY', THEN the numbers won't re-calculate!

[Modifié le 2017-07-21 16:15 GMT]
Collapse


 
Terry Richards
Terry Richards
France
Local time: 10:55
French to English
+ ...
TOPIC STARTER
Thank you Tony. Jul 21, 2017

Tony M wrote:
If you can't copy to word, then create your 'line number' column as normal, THEN copy-&-paste it alongside using 'VALUES ONLY', THEN the numbers won't re-calculate!


That's what I needed! Ta muchly


 
Shouguang Cao
Shouguang Cao  Identity Verified
China
Local time: 17:55
English to Chinese
+ ...
Solution A Jul 21, 2017

Solution A is great.

1. Suppose you will pick "column E" to hold line numbers. Type manually the first three line numbers in the first three rows in column E.
If the row number starts with 3, for example, type 3 in cell E3, 4 in Cell E4, 5 in cell E5.

2. Select E3, E4 and E5, and move your mouse cursor to the bottom right of your selection until your mouse cursor because solid + .

3. Press and hold the left mouse button and drag all the way down to t
... See more
Solution A is great.

1. Suppose you will pick "column E" to hold line numbers. Type manually the first three line numbers in the first three rows in column E.
If the row number starts with 3, for example, type 3 in cell E3, 4 in Cell E4, 5 in cell E5.

2. Select E3, E4 and E5, and move your mouse cursor to the bottom right of your selection until your mouse cursor because solid + .

3. Press and hold the left mouse button and drag all the way down to the last row, then the column is populated with row sequential numbers.

Let me know if you need more help. I will make you a video.

Dallas
Collapse


 
Terry Richards
Terry Richards
France
Local time: 10:55
French to English
+ ...
TOPIC STARTER
Thanks Jul 22, 2017

I used Tony's solution but that would work too.

 
Emma Duffield
Emma Duffield  Identity Verified
United Kingdom
Local time: 09:55
French to English
+ ...
Just Add a Filter Jul 22, 2017

Hi Terry

Just for future reference, if your file has headings you can easily add a filter in Excel so you can see the blank lines only:

- Highlight the top row of headings.
- Click on the drop down arrow on the Sort & Filter tab, select 'Filter'.
- Little arrows will appear alongside all your headings.
- Click on the arrow on the column you want to filter, untick 'Select All' to untick all boxes, then tick 'Blanks'.
- Click 'OK' and voilà, your
... See more
Hi Terry

Just for future reference, if your file has headings you can easily add a filter in Excel so you can see the blank lines only:

- Highlight the top row of headings.
- Click on the drop down arrow on the Sort & Filter tab, select 'Filter'.
- Little arrows will appear alongside all your headings.
- Click on the arrow on the column you want to filter, untick 'Select All' to untick all boxes, then tick 'Blanks'.
- Click 'OK' and voilà, your file will be filtered to show the blank cells only.
- When you're done, you can simply click on the arrow at the top of the column and tick 'Select All' and 'OK' and all your rows will reappear in their original order.

Don't worry, this action only filters your data, it does not sort it.

Hope this is useful for you in the future!


Emma
Collapse


 
Terry Richards
Terry Richards
France
Local time: 10:55
French to English
+ ...
TOPIC STARTER
Thanks again! Jul 22, 2017

For some reason, the filter button was grayed out - possibly because part of the worksheet is protected. I'm sure this approach would work too, if that button was available.

It just goes to show that being tired on a Friday afternoon is not conducive to clear thinking - all of my solutions were amazingly complex (and probably wouldn't have worked anyway) and here are 3 simple but effective ways to do it.


 
Oscar Martin
Oscar Martin
Spain
Local time: 10:55
English to Spanish
+ ...
Easy solution Jul 24, 2017

There is a filter in memoQ, multilingual tab-delimited text filter, that allows you to import an Excel and set the C column as source and the D column as target. The rows that are already translated can be imported, confirmed and locked so that they are not considered for the wordcount.

This link show how it works: https://www.youtube.com/watch?v=7KQ3TtnDFUE.

Oscar.


 


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


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

Excel hide rows






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 »
Trados Studio 2022 Freelance
The leading translation software used by over 270,000 translators.

Designed with your feedback in mind, Trados Studio 2022 delivers an unrivalled, powerful desktop and cloud solution, empowering you to work in the most efficient and cost-effective way.

More info »