Often, when working, you have to compare various document modifications with each other, for example, the original and modified versions of materials prepared in Word or in the form of PDF documents or presentations, working and updated versions of price lists with changed prices in Excel, different versions of text documents, etc. .NS. At the same time, the question is not which version of the files is more recent (this is already clear from the properties of the files), but it is important what exactly has changed in the documents in terms of content. Comparing documents manually is a thankless task due to the time consuming and the possibility of errors, because it is as easy as shelling pears not to notice some important detail when viewing. It is much more reasonable to delegate the task of comparing files to a computer. In general, in terms of comparing Word documents, everything is quite safe and without the use of auxiliary tools, although in versions of Word 2002 and Word 2003 this feature is reliably hidden from prying eyes, and probably not so many users know about its existence at all. The point is that to compare documents here, you first need to download the source file. Then, from the Tools menu, open the Compare and Merge Fixes command, specify the file to compare with the original, and enable the Black Lines checkbox. Only after these manipulations, the "Combine" button will turn into a "Compare" button, and when you click on this button, the program will compare the files.
The comparison results will be shown in the newly created document in traditional review mode. With the introduction of Word 2007, things have become much easier because you can now switch to the Review tab, click the Compare button, and specify the versions of the document to compare. The result of the comparison will be presented in a new document, where the revised document will be displayed on the left, taking into account the changes, and on the right (one above the other) - the original and modified documents. Theoretically, in Excel it is also possible to compare documents with built-in tools, however, only when working in the mode of committing changes. However, this is inconvenient, since each of the changed cells will have to be viewed by hovering the mouse over it, since changes made to the document are displayed in pop-up windows (similar to ordinary notes).
Secondly, if the named mode is not previously enabled (command "Tools"> "Fixes"> "Select fixes", checkbox "Track fixes"), then it will be impossible to compare XLS files.
As for the quick comparison of PDF documents, such an opportunity, of course, is available in Acrobat 9 Pro and Acrobat 9 Pro Extended, but these solutions are not installed on every computer. Therefore, if you need to quickly compare Excel tables, PDF documents, presentations, as well as documents in other formats, in particular, text files and program codes, you have to resort to using additional tools. There are many options here, and these can be both complex solutions that allow you to work with several file formats, and highly specialized utilities. A considerable part of such solutions are offered for decent money - for example, the price of one of the most famous integrated solutions in this field, Diff Doc, is $ 99.95, and the Araxis Merge utility, which is very popular among programmers, is estimated at € 119. At the same time, on the market there are also quite affordable or even free programs of a similar plan, it is these solutions that we will evaluate in this article. In this case, we will focus on complex solutions, and from highly specialized tools, we will note only utilities for quick comparison of Excel tables, since this is one of the most urgent tasks, and comparing Excel documents with complex solutions, although possible, is less effective than using highly specialized utilities ...
⇡ Comprehensive solutions for quick document comparison
Compare Suite 7.0
Developer: AKS-LabsDistribution size: 3.79 Mb
Spreading: Shareware Compare Suite is a handy tool for quickly comparing text files, MS Office documents, RTF documents, PDF files, web pages (HTM), PowerPoint presentations, binaries and some other file types, as well as files in ZIP and RAR archives and FTP-servers. In the case of comparing program listings in a number of programming languages (Object Pascal, HTML, C / C ++, JavaScript, PHP, etc.), syntax highlighting is provided. There is functionality for synchronizing text files and comparing folders along with subfolders. The demo version of the program (there is a Russian-language localization) is operational for 30 days and is fully functional. The commercial version costs $ 60. The file comparison technology in Compare Suite is simple. First, the file comparison method is selected, for which in the "Compare" menu you need to select one of the options: "character by character", "word by word", "by keywords". The classic character-by-character comparison is useful in situations where you need to capture the slightest differences in the spelling of words - for example, when comparing program listings. A word-by-word comparison of documents is usually used to compare different versions of the same file. Using the keyword comparison method, you can compare dissimilar documents even when a word-by-word comparison is not possible.
After choosing the comparison method, in the "File" menu, select the "New file comparison" command and specify the files for comparison on the left and right panels. The program will analyze the files and highlight all existing differences (added, modified and deleted fragments) with different colors. Additionally, you can create a comparative report with detailed information about the compared files (the "File"> "Report" command). When comparing files other than text files, keep in mind that they are compared in all complex solutions (both in Compare Suite and in other utilities) after conversion to text format. In practice, this means, for example, that if in the compared versions of a Word document the same word in a particular sentence appears on different lines, then it will be added to the list of changes. When comparing XLS documents, data is extracted from tables before analysis, and the differences found are displayed line by line in text format with the sheet name and column names, which allows you to navigate the data. Comparison of PDF documents and PowerPoint presentations follows the same scheme.
Good day!
This article is devoted to solving such a question as how to compare two tables in Excel, well, or at least two columns. Yes, working with tables is convenient and good, but when you need to compare them, it is quite difficult to do it visually. Perhaps a table of up to ten or two, you can sort it visually, but when they exceed thousands, then you will need additional analysis tools.
Alas, there is no magic wand with which in one click everything will be done and the information will be verified, it is necessary to prepare the data, and write formulas, and other procedures that allow you to compare your tables.
Let's consider several options and possibilities for comparing tables in Excel:
The easy way,
This is the simplest and most basic way to compare two tables. It is possible to compare in this way, both numerical values and text values. For example, let's compare two ranges of numerical values, just writing in a neighboring cell the formula for their equality = C2 = E2, as a result, if the cells are equal, we get the answer "TRUE", And if there are no matches, there will be "LYING"... Now, by simple auto copying, we copy it onto our formula that allows us to compare two columns in Excel and see the difference.
Quickly highlight values that differ
This is also not a very cumbersome way. If you just need to find and make sure that there are, well, or no differences between the tables, you need to select the "Find and Select" menu button on the "Home" tab, after selecting the range where you want to compare two tables in Excel. In the menu that opens, select the item Select a group of cells ... and in the dialog that appears, select "Line differences".
Compare two tables in Excel using conditional formatting
Highly good way, in which you will be able to see in highlighted colors the values that differ when comparing two tables. You can apply on the tab "Home" by pressing the button "Conditional Formatting" and in the provided list select "Rule Management".
In the dialog box Conditional Formatting Rules Manager, press the button "Create Rule" and in a new dialog "Creating a Formatting Rule", choose a rule. In field Change Rule Description we enter the formula = $ C2<>$ E2 to define the cell to be formatted and click the button "Format".
We define the style of how our value will be formatted, which meets the criterion. 
Now our newly created rule has appeared in the list of rules, you select it, click "OK".
And the whole rule was applied to our range, where we are trying to check for similarity between two tables, and the differences became visible, to which the conditional formatting was applied.
How to compare two tables in Excel using COUNTIF function and rules
All of the above methods are good for ordered tables, but when the data is not ordered, other methods are needed, one of which we will now consider. Imagine, for example, we have 2 tables, the values of which are slightly different and we need to compare these tables to determine the value that is different. Select the value in the range of the first table and on the tab "Home", menu item "Conditional Formatting" and in the list, click the item "Create a rule ...", choose the rule "Use a formula to determine which cells to format", we enter the formula = ($ C $ 1: $ C $ 7; C1) = 0 and select the conditional formatting format. 
The formula checks the value from a specific cell C1 and compares it to the specified range $ C $ 1: $ C $ 7 from the second column. We copy the rule for the entire range, in which we compare the tables and get the values highlighted in the cells, which are not repeated.
How to compare two tables in Excel using the VLOOKUP function
In this variant we will use which will allow us compare two tables for matches. To compare two bars, enter the formula = VLOOKUP (C2; $ D $ 2: $ D $ 7; 1; 0) and copy it over the entire range to be compared. This formula sequentially begins to check if there are duplicates of the value from column A in column B, and, accordingly, returns the value of the element if it was found there, if the value is not found, we get.
How to compare two tables in Excel IF functions
This option provides for the use of a logical one, and the difference of this method is that not all of it will be used to compare two columns, but only that part of it that is needed for comparison.
For example, let's compare two columns A and B on the worksheet, in the next column C we enter the formula: = IF ((SEARCH (C2, $ E $ 2: $ E $ 7; 0)); ""; C2) and copy it all over. This formula allows you to view sequentially whether there are certain elements from the specified column A in column B and returns the value if it was found in column B.
Compare two tables using VBA macro
There are many ways to check if two tables are similar. , but some options are only possible with VBA macros. Macros for comparing two tables , unifies this process and significantly reduces the time spent on data preparation. Based on the task you are solving and knowledge of VBA macros, you can create any variants of macros. Below I have given the methodology indicated on the official Microsoft page. You need to create a module for VBA code and enter the code:
Sub Find_Matches () Dim CompareRange As Variant, x As Variant, y As Variant "Set CompareRange equal to the compared range Set CompareRange = Range (" B1: B11 ")" If the compared range is on another sheet or workbook, "use the following syntax" Set CompareRange = Workbooks ("Book2"). _ "Worksheets (" Sheet2 "). Range (" B1: B11 ")" "Compare each item in the selection with each item" CompareRange variable For Each x In Selection For Each y In CompareRange If x = y Then x.Offset ( 0, 2) = x Next y Next x End Sub
Sub Find_Matches () Dim CompareRange As Variant, x As Variant, y As Variant "Setting the CompareRange variable equal to the compared range Set CompareRange = Range ("B1: B11") "If the compared range is on another list or book, "use the following syntax "Set CompareRange = Workbooks (" Book2 "). _ Hot shortcut keys Alt + F8... In the new dialog box, select your macro Find_similar and execute it. Comparisonusing the Inquire add-on
This compare option became available with the 2013 release Excel versions, added to the editor Inquire add-on which will analyze and compare two Excel files. This method is good when you need to compare two files, in the case when your colleague was working on a book and introduced some changes. To determine these changes, you need a tool WorkbookCompare in add-inInquire. Well, we looked at 8 ways to compare two tables in Excel, these options will help you solve your analytical tasks and simplify your work. I was glad to help you!
|
Say you want to compare versions of a workbook, analyze a workbook for problems or inconsistencies, or see links between workbooks or worksheets. If Microsoft Office 365 or Office Professional Plus 2013 is installed on your computer, the Spreadsheet Inquire add-in is available in Excel.
You can use the commands in the Inquire tab to do all these tasks, and more. The Inquire tab on the Excel ribbon has buttons for the commands described below.
If you don "t see the Inquire tab in the Excel ribbon, see Turn on the Spreadsheet Inquire add-in.
Compare two workbooks
The Compare Files command lets you see the differences, cell by cell, between two workbooks. You need to have two workbooks open in Excel to run this command.
Results are color coded by the kind of content, such as entered values, formulas, named ranges, and formats. There "s even a window that can show VBA code changes line by line. Differences between cells are shown in an easy to read grid layout, like this:
The Compare Files command uses Microsoft Spreadsheet Compare to compare the two files. In Windows 8, you can start Spreadsheet Compare outside of Excel by clicking Spreadsheet Compare on the Apps screen. In Windows 7, click the Windows Start button and then> All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013.
To learn more about Spreadsheet Compare and comparing files, read Compare two versions of a workbook.
Analyze a workbook
The Workbook Analysis command creates an interactive report showing detailed information about the workbook and its structure, formulas, cells, ranges, and warnings. The picture here shows a very simple workbook containing two formulas and data connections to an Access database and a text file.
Show workbook links
Workbooks connected to other workbooks through cell references can get confusing. Use the to create an interactive, graphical map of workbook dependencies created by connections (links) between files. The types of links in the diagram can include other workbooks, Access databases, text files, HTML pages, SQL Server databases, and other data sources. In the relationship diagram, you can select elements and find more information about them, and drag connection lines to change the shape of the diagram.
This diagram shows the current workbook on the left and the connections between it and other workbooks and data sources. It also shows additional levels of workbook connections, giving you a picture of the data origins for the workbook.
Show worksheet links
Got lots of worksheets that depend on each other? Use the to create an interactive, graphical map of connections (links) between worksheets both in the same workbook and in other workbooks. This helps give you a clearer picture of how your data might depend on cells in other places.
This diagram shows the relationships between worksheets in four different workbooks, with dependencies between worksheets in the same workbook as well as links between worksheets in different workbooks. When you position your pointer over a node in the diagram, such as the worksheet named "West" in the diagram, a balloon containing information appears.
Show cell relationships
To get a detailed, interactive diagram of all links from a selected cell to cells in other worksheets or even other workbooks, use the Cell relationship tool. These relationships with other cells can exist in formulas, or references to named ranges. The diagram can cross worksheets and workbooks.
This diagram shows two levels of cell relationships for cell A10 on Sheet5 in Book1.xlsx. This cell is dependent on cell C6 on Sheet 1 in another workbook, Book2.xlsx. This cell is a precedent for several cells on other worksheets in the same file.
To learn more about viewing cell relationships, read See links between cells.
Clean excess cell formatting
Ever open a workbook and find it loads slowly, or has become huge? It might have formatting applied to rows or columns you aren "t aware of. Use the Clean Excess Cell Formatting command to remove excess formatting and greatly reduce file size. This helps you avoid "spreadsheet bloat," which improves Excel "s speed.
Manage passwords
If you "re using the Inquire features to analyze or compare workbooks that are password protected, you" ll need to add the workbook password to your password list so that Inquire can open the saved copy of your workbook. Use the Workbook Passwords command on the Inquire tab to add passwords, which will be saved on your computer. These passwords are encrypted and only accessible by you.
Friends, I am glad to welcome you on the pages of my blog. Today I want to tell you about 4 ways to compare files by content. The need to find the differences arises quite often. You have to compare files by type or size. Comparing files in Word and Excel is also a very common procedure in offices and offices, where you constantly have to work with text documents and spreadsheets.
But it is often necessary for a webmaster or site owner to compare two files by content. And we are not talking about text files, although they are also parsed in the same way, but about the formats .html, .css, .php and so on. Those files where you need to find changes in the code. And sometimes a change in only one sign can provide a lot of difficulties in achieving a result.
So, the last example, when I used a comparison of two files, is the problem with the work of comments. In my mine, I mentioned this. The reason was just one gap. Just imagine how you can find such an insignificant difference manually.
And, in general, when working with site files, you often have to analyze the code. Doing it manually is unrealistic. It is much easier to use the tools at hand, the tools that every webmaster and site owner should have.
Method 1. Comparing files in Total Commander.
I have been using this program for a long time. For working with files, in my opinion, this is one of the best programs. Total Commander is a paid program, but you can also use a trial version for 30 days, which after the time expires will also work, but with a purchase offer. You can download the trial version.
Earlier, I told you how to configure via TC, which allows you to easily manage site files and carry out all the necessary operations directly from your computer, bypassing your hosting account. Just because of this one possibility, it is already worth using this program.
Now let's take a look at the file comparison tool by content, which also allows you to edit and copy data during the analysis.
So, start Total Commander - select the first file for comparison in one of the panels - open the folder with the second file in the second panel.
A new window will open, also divided into two panels. At the very top, in each panel there will be a time limit for selecting a file. In one of them, the first file will already be selected (but may be empty). To select a file, you need to click on the small button with arrows (>>) .
After selecting the files for comparison, click on the button "Compare"... The contents of the selected file will appear in each panel. Differences will be highlighted with a background color.
To make changes to the file, just click on the button "Edit"... And you can start making changes. The functions of copying and rolling back, searching and changing the encoding will also be available here.
If you have made changes to the file, then after closing the comparison window, you will be prompted to save the result.
Method 2. Comparing files in Notepad ++.
If you've ever had to edit your html, css and php files. You've probably heard of free editor Notepad ++ code. You can download the program.
This is an excellent program, which, to its innumerable advantages, also allows you to compare files by their content.
But this requires installing a small plugin. All this is done directly inside the editor itself.
So, start the editor - go to the menu item "Plugins» — Plugin Manager — Show Plugin Manager.
In the new window, select the plugin « Compare " and press the button « Install ".
After installing the plugin, you can start comparing the contents of the files. To do this, you need to open two files - go to the menu item "Plugins" — « Compare " — « Compare (Alt +D) ".
The result of the file comparison will be presented in separate panels. That is, the first file will be on the left, and the second on the right. Opposite the lines, in which the differences were found, there will be a warning sign and highlighting in the background color.
The line where minor differences are found will be highlighted in yellow and indicated by an exclamation mark.
A line that is completely different will be marked with a red minus on one panel and a green plus on the other. And it is highlighted with the corresponding color.
Of course, once you find a difference, you can immediately make the necessary corrections and save the changes.
You can end the file comparison with the keyboard shortcut Ctrl +Alt +D.
Method 3. Comparing the contents of two files in the WinMerge program.
This program allows you to compare not only the contents of files, but also the contents of entire folders. This is similar to Total Commander, only the program is free. You can download it.
The installation of the program is standard and will not be difficult. Therefore, we will not dwell on this, but immediately proceed to the process of comparing two files.
After launching the program, you need to select the files to compare. Open the menu item "File" — "Open".
The comparison result is presented in two panels, as well as in other examples. Only here the code snippet that differs in a particular line is also highlighted.
You can also edit files in this program. And when you close the comparison window, the program will offer to save the changes in the files.
Method 4. Comparing files using the Windows command line.
This method allows you to compare the contents of files without third-party programs... It is enough to use the standard tool operating system Windows.
Also, this method does not allow editing files. To do this, you need to use other tools. But, nevertheless, you will cope with the task without additional programs.
To do this, open "Start" — "All programs" — "Standard" — « Command line» ... And enter this command:
Fc / N path to first file path to second file
it looks like this:
Fc / N C: \ My Documents \ file1.txt C: \ My Documents \ file2.txt
The finished result will look like this:
When there are few differences, then they can be quickly corrected using editing programs for this. But when there are many differences, it is rather difficult to analyze information using this method.
So, I told you about the methods of comparing files, and now I propose to watch the video tutorial in which I show the comparison of two files in all ways.
I am sure that now if you need to compare files, then you can easily do this using one of the proposed methods.
That's all for today, I wish everyone success and Have a good mood and see you in new articles and video tutorials!
Best regards, Maxim Zaitsev.
Inquire Add-in for Excel 2013 allows you to compare and analyze Excel documents for links between them, for the presence of erroneous formulas and for determining differences between files in the .xlsx format. Let's take a look at the moments when this add-on might come in handy and how to use it.
Launching the Inquire add-in
The Inquire Add-in for Excel comes with the standard Excel 2013 suite and you do not need to download any additional installation packages. It is enough to enable it in add-ins. Earlier versions of Excel do not support this add-in. In addition, at the time of this writing, the add-on was only available in English.
To start Inquire, go to the tab File –> Options... In the dialog box that appears, select the tab Add-ons, in the dropdown menu Control choose Add-onsCOM and click the button Go. A window will appear Component Object Model Add-ins (COM), where you will need to check the box opposite Inquire and press the button OK.
After starting the add-in, a new tab will appear on the ribbon Inquire.
Let's see what benefits this add-on gives us.
Workbook analysis
Workbook analysis is used to reveal the structure of the workbook, formulas, errors, hidden sheets, etc. To use this tool, go to the group Report and click the button Workbook Analysis. The result of the add-in is shown below.
Surely, many paid attention to the item Veryhiddensheets(Very hidden sheets). This is no joke, in Excel it is really possible to "hide" a worksheet "well" using the VisualBasic editor. We will talk more about this in our subsequent articles.
Link to worksheets
In a group Diagram, there are three tools for defining relationships between workbooks, sheets and cells. They allow you to indicate the relationship between Excel items. This functionality can be useful when you have a large number of cells with links to other books. Attempts to untangle this mess can take a significant amount of time, while the Inquire add-in allows you to visualize data dependencies.
To build a dependency diagram, in the group Diagram select one of the items WorkbookRelationship, WorksheetRelationship or CellRelationship. The choice will depend on what kind of dependency you want to see: between books, sheets or cells.
In the picture below, you will see the workbook relationship diagram that Excel built when I clicked the button WorkbookRelationship.
Comparing two files
The following Inquire Add-in tool for Excel is Compare- allows cell by cell to compare two files and indicate any differences between them. You may need this tool when you have multiple revisions of the same file and need to understand what changes have been made in the latest versions.
To use this tool, you need two files. In a group Compare choose CompareFiles. In the dialog box that appears, select the files that we want to compare and click the button Compare.
In our case, these are two identical files, in one of which I deliberately made some changes.
After some deliberation, Excel will display the comparison result, where the differences between the two tables will be indicated in color. In this case, the color of the cell will be different depending on the type of cell difference (differences can be generated due to values, formulas, calculations, etc.).
Clearing unnecessary formatting
This tool allows you to clean up unnecessary formatting of cells in a workbook, for example, cells that are formatted but do not contain values. Tool Clean Excess Cell Formatting will help "amateurs" fill the entire row of the workbook with color, instead of filling certain rows of the table.
To use the tool, go to the tab Inquire to the group Miscellaneous and select Clean Excess Cell Formatting. In the window that appears, you must select the area for cleaning unnecessary formatting - the whole book or the active sheet - click OK.
Cleaning up unnecessary formatting will reduce file size and increase productivity.
Workbook passwords
If you are going to analyze password protected workbooks, you will need to specify them in Workbook Passwords.
Outcome
The Inquire Excel Add-in contains several interesting tools that can help you improve the accuracy and consistency of your workbooks. If you are using Excel 2013, it makes sense to turn your attention to this add-in.
