Excel hide rows
Thread poster: Terry Richards

Terry Richards
France
Local time: 04:37
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.

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


 

Tony M  Identity Verified
France
Local time: 04:37
Member
French to English
+ ...
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 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]


 

Terry Richards
France
Local time: 04:37
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 muchlyicon_smile.gif


 

Shouguang Cao
China
Local time: 10:37
Member (2007)
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 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


 

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

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

 

Emma Duffield  Identity Verified
United Kingdom
Local time: 03:37
Member (2016)
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 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


 

Terry Richards
France
Local time: 04:37
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
Spain
Local time: 04:37
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

Advanced search






Anycount & Translation Office 3000
Translation Office 3000

Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.

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