International Translation Day 2017

Join ProZ.com/TV for a FREE event on September 26-27th celebrating International Translation Day! 50+ hours of content, Chat, Live Q&A & more. Join 1,000's of linguists from around the globe as ProZ.com/TV celebrates International Translation Day.

Click for Full Participation

How to have content of x spreadsheets in only one?
Thread poster: Nicolas Bonsignore

Nicolas Bonsignore  Identity Verified
Local time: 12:50
Member (2005)
English to French
+ ...
Jun 12, 2007

Hi all,

I have a two workbooks with 10 spreadsheets I have to use for terminology.

It's taking me ages to search all for them.
I would like to combine all the content of the sheets in one single sheet in order to be able to do a quicker search.

The sheets have several column with several languages.
column A = source
B = target 1
C = target 2
and so on for the 10 spreadsheets.

I just would like to have columns A and C of every sheet in a new sheet (same workbook or another).

Is that do-able?

Thanks,

Nicolas


Direct link Reply with quote
 

Capesha  Identity Verified
Local time: 12:50
Member (2006)
English to German
+ ...
I don't see the problem Jun 12, 2007

You could do that in excel or alternative in a word table.
I would even prefer word, because of the spelling-check.

Just prepare the table - you will once have the work to copy the spreadsheet content into the table. But thereafter you will only have to update the list.


Direct link Reply with quote
 

Nicolas Bonsignore  Identity Verified
Local time: 12:50
Member (2005)
English to French
+ ...
TOPIC STARTER
Any macro or formula? Jun 12, 2007

Well, I guess it's no problem to do that manually... but is there any formula or macro allowing me to do that quickly?
Or any tool able to do a quick search in several workbooks?


Direct link Reply with quote
 

Capesha  Identity Verified
Local time: 12:50
Member (2006)
English to German
+ ...
good question Jun 12, 2007

I always do such a work manually and then I see, that I keep it updated.
Sure, this is not the quickest solution.

Maybe here is somebody, who is well familiar with makros and who can help?


Direct link Reply with quote
 

Henrik Pipoyan  Identity Verified
Local time: 14:50
Member (2004)
English to Armenian
Hi Nicolas Jun 12, 2007

There are different ways to make this process faster, but I don't know if they are what you want.

Here is one of them. You can gather all the sheets in one workbook and search the workbook with one command. Here's how you can do that:

Open both workbooks. In any of them select all the sheets (you can do it by holding down the Ctrl key and clicking on each sheet-tab one by one, or by holding down the Shift key and clicking on the first and then on the last sheet-tabs. When all the tabs are selected, right click on any of them and in the menu select "Move or Copy". In the dialog-box that appears you'll see the name of the second file, so you can move all the spreadsheets to that file. If you want to leave them also in the older file, just check the "Create a copy" checkbox. Once you have done so, close this file, and go to the other file which has all the spreadsheets. To search the whole workbook, when opening the Find dialog box hit the "Options" button to expand it. After it expands, just select "Workbook" in the "Within" field, and Excel will search the whole workbook.

I hope this helps.

Henry


Direct link Reply with quote
 

Nicolas Bonsignore  Identity Verified
Local time: 12:50
Member (2005)
English to French
+ ...
TOPIC STARTER
sounds good Jun 12, 2007

I'll try that.

Thanks Henry


Direct link Reply with quote
 

Philippe Etienne  Identity Verified
Spain
Local time: 12:50
Member
English to French
Consider Search and Replace for Windows Jun 12, 2007

from http://funduc.com/search_replace.htm. Besides replacing, this tool can search through batches of txt or csv files and displays a list of all lines containing the search term/phrase in those files.

In such instances, here it goes:
-Save each sheet in csv format and put each csv file in one folder
-Set the file mask to *.* or *.csv
-Point Search and Replace (SR) to that folder's path

When you copy-paste a term/phrase in the Search field, you get a list of all entries of that term/phrase in all your sheets, ie all Excel lines containing it, including column C.

When there is a lot of reference material involved where source and target terms are on the same line, SR really makes a difference: I convert all files (Word, Excel) to csv or txt to have them all searched by SR in one go.

This is one of the tools I could hardly efficiently work without.

Happy searching,
Philippe


Direct link Reply with quote
 

Nicolas Bonsignore  Identity Verified
Local time: 12:50
Member (2005)
English to French
+ ...
TOPIC STARTER
Have it! Jun 12, 2007

Hi Philippe,
I have that tool but they said it's no good for .xls.
I'll try with the .csv file as you suggest.

Thanks for the tip !

Nicolas


Direct link Reply with quote
 
xxxNMR
France
Local time: 12:50
French to Dutch
+ ...
The other solution is Jun 13, 2007

..to leave it at it is and search with a desktop search tool such as Copernic.

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 »

How to have content of x spreadsheets in only one?

Advanced search






CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use SDL Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

More info »
Déjà Vu X3
Try it, Love it

Find out why Déjà Vu is today the most flexible, customizable and user-friendly tool on the market. See the brand new features in action: *Completely redesigned user interface *Live Preview *Inline spell checking *Inline

More info »



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