How to compare the content of two excel sheets
Thread poster: xxxBrandis
xxxBrandis
Local time: 19:10
English to German
+ ...
Mar 29, 2009

Hi! Does anyone know to compare the content of two excel sheets. The scenerio is this. I have done a project about 2 years ago and the current project is simiar in size and content, now the question keeps on going up in mind that there should be somewhere some difference or there is a catch somewhere only i am too dumb to find it. The situation is the OS is from altogether different continent and the finishing timeframe and other details are also very different. Now I should know very fast how to compare and find out how much content had been validated two years ago and what is now different. Thank you much in advance. BR Brandis - As the proverb goes, someone confronted with a time framed trouble is always dumb, i am not getting the right idea. Hence seeking external help and free minds.

[Subject edited by staff or moderator 2009-03-29 21:45 GMT]


Direct link Reply with quote
 

Jabberwock  Identity Verified
Poland
Local time: 19:10
Member (2004)
English to Polish
A little trick... Mar 29, 2009

You can use a little Trados trick:

Analyse first file against an empty TM. You will get zero matches (maybe some repetitions) and the total number of words. Then clear the file list, check the option "Use TM from previous analysis" and analyse the second file. If you get only 100% matches, the files are identical. If there are different numbers, then the files are also different - the percentages will give you an idea how similar they are.


Direct link Reply with quote
 

Elías Sauza  Identity Verified
Mexico
Local time: 12:10
Member (2002)
English to Spanish
+ ...
Besides this good option Mar 30, 2009

Jabberwock wrote:

You can use a little Trados trick:

Analyse first file against an empty TM. You will get zero matches (maybe some repetitions) and the total number of words. Then clear the file list, check the option "Use TM from previous analysis" and analyse the second file. If you get only 100% matches, the files are identical. If there are different numbers, then the files are also different - the percentages will give you an idea how similar they are.


This is really a good option.

Also, depending on the extent of the text in the sheets, I think about copying the text of each sheet in two different MS Word files. Then compare and combine both files and that will give you an idea of the similarity.


HTH


Direct link Reply with quote
 

Soledad Caño  Identity Verified
United States
Local time: 13:10
Member (2005)
English to Spanish
+ ...
Remove duplicates Mar 30, 2009

Here is what I suggest. Copy both sheets into a new one. Change the color font of the fields of the new file. Once you have a new sheet with all the fields you just click on "Remove duplicates" (you can find this option on the DATA menu). You can specify which columns you want to check to delete duplicates. When you finish, if you still have fields in the new color, it means they are different from the original sheet.

Hope it helps.


Direct link Reply with quote
 

Eduardo López Herrero  Identity Verified
Japan
Local time: 02:10
Member (2007)
Japanese to Spanish
+ ...
Diff software Mar 31, 2009

Since nobody suggested the obvious...

You can use some dedicated diff software. ExamDiff is my favorite; the Pro version accepts binary files. Depending on the format of your files, some preprocessing might improve the diff results.

Hope this helps,
Eduardo


Direct link Reply with quote
 

Jabberwock  Identity Verified
Poland
Local time: 19:10
Member (2004)
English to Polish
Diff and Excel Mar 31, 2009

Eduardo López Herrero wrote:

Since nobody suggested the obvious...



Maybe because it is not so obvious...

To use diff software you have to convert Excel files into text format (e.g. csv). This requires manual saving of each sheet - unless you have a specialized macro, which is quite convenient, by the way:

http://www.mrexcel.com/forum/showthread.php?t=341733

Even then a small shift in the data location might give big text differences...



Binary diff is practically meaningless, if the files are not identical - it is very easy to produce workbooks with the same content and quite different binary files.

There is, of course, specialized Excel comparison software:

http://www.comparesuite.com/solutions/compare_utilities_review/compare-files-excel.htm

but it costs money... (some of the programs have trial versions, though).


Direct link Reply with quote
 
Charlie Bavington  Identity Verified
Local time: 18:10
French to English
Rudimentary Excel function Mar 31, 2009

Depending on the contents, you might be able to use Excel's VLOOKUP function to determine which cells are identical.
If I remember correctly, it won't tell you much more than that, but it might be a start.


Direct link Reply with quote
 
xxxBrandis
Local time: 19:10
English to German
+ ...
TOPIC STARTER
A very good suggestion indeed.. Mar 31, 2009

Jabberwock wrote:

You can use a little Trados trick:

Analyse first file against an empty TM. You will get zero matches (maybe some repetitions) and the total number of words. Then clear the file list, check the option "Use TM from previous analysis" and analyse the second file. If you get only 100% matches, the files are identical. If there are different numbers, then the files are also different - the percentages will give you an idea how similar they are.
Hi! But the trados variant does not show the repeats that I can save in a separate file. It shows only the repeats and the differences. Even if I set the percentage size of repeat above 25% it will be same. Trados solutions shows statistics and does not indicate the content location of the repeat. I thank you BR Brandis


Direct link Reply with quote
 
xxxBrandis
Local time: 19:10
English to German
+ ...
TOPIC STARTER
thank you. Mar 31, 2009

Eduardo López Herrero wrote:

Since nobody suggested the obvious...

You can use some dedicated diff software. ExamDiff is my favorite; the Pro version accepts binary files. Depending on the format of your files, some preprocessing might improve the diff results.

Hope this helps,
Eduardo
Now where do I download a test version. Your favourite, binary files sounds equally very good. You see I have a couple of files going into over a million strings and I am very doubtful about the repeats, since the outsourcer is not willing to even part of the repeat work as per trados principle. BR Brandis


Direct link Reply with quote
 

Jabberwock  Identity Verified
Poland
Local time: 19:10
Member (2004)
English to Polish
Refocus Mar 31, 2009

Brandis wrote:
But the trados variant does not show the repeats that I can save in a separate file. It shows only the repeats and the differences. Even if I set the percentage size of repeat above 25% it will be same. Trados solutions shows statistics and does not indicate the content location of the repeat. I thank you BR Brandis


Well, the question stated in the first post was "how much?". Extracting the differing segments in a way that can be CAT processed is a totally different matter...

Edit: have you tried "Export unknown segments" option?


[Edited at 2009-03-31 12:13 GMT]


Direct link Reply with quote
 
cndlgs
Local time: 01:10
a link of macro Jul 8, 2009

Could you please use below macro.

http://cndlss.100webspace.net/Compare_two_file_free.xls


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 compare the content of two excel sheets

Advanced search






WordFinder
The words you want Anywhere, Anytime

WordFinder is the market's fastest and easiest way of finding the right word, term, translation or synonym in one or more dictionaries. In our assortment you can choose among more than 120 dictionaries in 15 languages from leading publishers.

More info »
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 »



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