Let's compare two tables with almost the same structure. Tables differ in values \u200b\u200bin separate lines; some row names appear in one table, but may not be present in another.
Let on sheets January and February there are two tables with turnovers for the period for the corresponding accounts.
As you can see from the figures, the tables differ:
- The presence (absence) of lines (names of accounts). For example, in a table on a sheet January there is no score 26 (see the example file), and in the table on the sheet February account 10 and its sub-accounts are missing.
- Different values \u200b\u200bin the lines. For example, on account 57, the turnovers for January and February do not coincide.
If the structures of the tables are approximately the same (most of the names of accounts (rows) are the same, the number and names of columns are the same), then you can compare the two tables. Let's compare in two ways: one is easier to implement, the other is clearer.
A simple option for comparing 2 tables
First, let's determine which rows (account names) are present in one table, but absent in another. Then, in a table with fewer rows missing (in the most complete table), we will display a comparison report representing the difference in columns (the difference in turnovers for January and February).
The main disadvantage of this approach is that the table comparison report does not include rows that are missing from the most complete table. For example, in our case, the most complete table is a table on a sheet January,which is missing the score 26 from the February table.
To determine which of the two tables is the most complete, you need to answer 2 questions: What accounts in the February table are missing in the January? and What accounts in the January table are missing from the January one?
This can be done using the formulas (see column E): \u003d IF (UND (VLOOKUP (A7; January! $ A $ 7: $ A $ 81; 1; 0)); "No"; "Yes") and \u003d IF (UND (VLOOKUP (A7; February! $ A $ 7: $ A $ 77; 1; 0)); "No"; "Yes")
Comparison of turnovers by accounts will be performed using the formulas: \u003d IF (UND (VLOOKUP ($ A7; February! $ A $ 7: $ C77; 2; 0)); 0; VLOOKUP ($ A7; February! $ A $ 7: $ C77; 2; 0)) - B7 and \u003d IF (UND (VLOOKUP ($ A7; February! $ A $ 7: $ C77; 3,0)); 0; VLOOKUP ($ A7; February! $ A $ 7: $ C77; 3,0)) - C7
If there is no corresponding row, the VLOOKUP () function returns the # N / A error, which is processed by a combination of the UND () and IF () functions, replacing the error with 0 (if there is no row) or with a value from the corresponding column.
Use to highlight discrepancies (eg in red).
A more visual option for comparing 2 tables (but more complex)
By analogy with the problem solved in the article, you can create a list of account names, including ALL account names from both tables (without repetitions). Then print the difference across the columns.
This requires:
- With \u003d IFERROR (IFERROR (INDEX (January; SEARCH (0; COUNTIF (A $ 4: $ A4; January); 0)); INDEX (February; SEARCH (0; COUNTIF (A $ 4: $ A4; February); 0))) ; "")form in column A a list of accounts from both tables (without repetitions);
- With \u003d IFERROR (INDEX (List; SEARCH (SMALL (COUNTIF (List; "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") where List is
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\u003e 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 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 just 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 comments work. In 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 set up via TC, which allows you to easily manage site files and carry out all the necessary operations directly from your computer, bypassing the hosting account. Only 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, launch 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 file selection. 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 marked with a background color.
To make changes to the file, just click on the button "Edit"... And you can start making changes. Also functions of copy and rollback, search and change of encoding will 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 about the free code editor Notepad ++. 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 file comparison result 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 a 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.
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 also the exact code fragment that differs in a particular line is 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 of the Windows operating system.
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, you can easily do this using one of the proposed methods.
That's all for today, I wish everyone success and good mood, and see you in new articles and video tutorials!
Best regards, Maxim Zaitsev.
Information in the form of tables is much more convenient to analyze and use in various calculations, but when it is necessary to compare data from several similar tables, it is very difficult to do all this visually. The right software can always help out in such a situation, and next we will look at how to compare two tables in Excel using different analysis methods.
Unfortunately, it is not possible to compare tables in Excel with the click of a single button, and moreover, perhaps for comparison, you will also have to prepare the data in some way, as well as write a formula.
The method of comparing data from tables is also selected depending on the required result. The easiest way is to compare two seemingly identical columns to identify rows in which this difference still exists. You can compare in this way both numeric values \u200b\u200band text.
Let's compare two columns of numeric values \u200b\u200bin which only a few cells differ. By writing a simple formula in the adjacent column, the condition of equality of two cells "\u003d B3 \u003d C3", we get the result "TRUE"if the contents of the cells are the same, and "LOD"if the contents of the cells are different. By stretching the formula across the entire height of the column of values \u200b\u200bbeing compared, it will be very easy to find the different cell.
If you just need to make sure of the presence or absence of differences in the columns, you can use the menu item Find and highlight, on the tab "Home"... To do this, you must first select the compared columns, then select the required menu item. In the drop-down list, select "Select a group of cells ...", and in the window that appears, select Line differences.
Conditional Formatting for Differences in Ordered Values
Optionally, you can apply conditional formatting to different cells by filling the cell, changing the text color, etc. In this case, you need to select the item "Conditional Formatting", in the drop-down list of which we select "Rule Management".
In the rules manager, select the item "Create rule", and in creating the rules, select ... Now we can set the formula “\u003d $ B3<>$ C3 " to define the formatted cell, and set the format for it by clicking the button "Format".
We now have a cell selection rule, formatting, and a range of cells to compare. After pressing the button "OK", the rule we set will be applied.
Comparing and Formatting Differences in Unordered Values
Comparing Excel tables is not limited to comparing ordered values. Sometimes it is necessary to compare ranges of mixed values \u200b\u200bin which it is necessary to determine the occurrence of one value in the range of other values.
For example, we have a set of values, formatted as two columns, and another set of values \u200b\u200bof the same. In the first set, we have all values \u200b\u200bfrom 1 to 20, and in the second, some values \u200b\u200bare missing and are duplicated by other values. Our task is to highlight with conditional formatting in the first set of values \u200b\u200bthat are not in the second set.
The procedure is as follows, we select the first data set, which we call "Column 1", and in the menu "Conditional Formatting" select item "Create a rule ..."... In the window that appears, select , enter the required formula "\u003d COUNTIF ($ C $ 3: $ D $ 12; A3) \u003d 0" and choose a formatting method.
Our formula uses the function "COUNTIF"which counts the number of times a value from a specific cell is repeated "A3" in a given range "$ C $ 3: $ D $ 12"which is our second column. The cell to be compared must be the first cell in the range of values \u200b\u200bto be formatted.
After applying the created rule, all cells with non-repeating values \u200b\u200bin another set of values \u200b\u200bwill be highlighted in the specified color.
Of course, there are more complex options for comparing two tables in Excel, such as comparing the cent of goods in the new and old price lists. Suppose there are two tables with prices, and next to the prices in the new table, you need to indicate the old prices for each product, and the order of the products in the lists is not respected.
Next to the prices in the new table in the cell of the next column, you need to write a formula that will select the values. In the formula, we will use the function "VPR"that can return a value from any column in the row where the search condition was met. For the function to work correctly, the column in each row must contain unique values \u200b\u200bto be searched for. If the values \u200b\u200bare repeated, only the first one found will be taken into account.
The formula we need will look like this: "\u003d VLOOKUP (B18; $ B $ 3: $ C $ 10; 2; FALSE)"... First meaning "B18" matches the first cell of the desired product name. Second meaning "$ B $ 3: $ C $ 10" means the constant address of the range of the old price table, the values \u200b\u200bfrom which we need. Third meaning "2" means the number of the column from the selected range, in the cell of which we will take the old price of the product. And the last meaning "FALSE" specifies a search only for an exact match of values. After dragging the formula through the entire column of the new table, we will get the old price values \u200b\u200bin this column for each position in the new table. Opposite the name of the last product, the formula displays an error message "# N / A", which indicates the absence of this name in the old price list.
There can be countless options for comparing tables in Excel, and some of them can only be done using the VBA add-in.
It would seem a simple task - comparing tables. More precisely, a comparison of two columns of a table for coincidences or differences. It is logical to suggest that Excel is an ideal solution to the problem, but alas, I did not find a simple free comparison of tables in Excel, except perhaps for the primitive "row1 \u003d row2". In reality, some processing of strings is necessary before comparison, since they may contain extra spaces, punctuation marks, and so on. As a result, it was decided to write a utility comparing two text files line by line and processing lines at the user's choice ...
Processing strings through files was chosen as universal. It doesn't matter what data source, just a list or an Excel table. Usually everything can be copied to a text file. So let's start directly with the program.
Download and unzip the program. In the original version, it contains three files "Compare.exe" - the program itself. "List 1.txt" and "List 2.txt" are empty text files. They are where you need to insert your strings for comparison. Launch:
By default, in my opinion, there are optimal comparison settings. A window with an example of how table comparisons will work was created only to adjust the settings for your tasks and a general understanding of what is happening. Do not compare real data in the example, since these windows contain no more than 32KB of text, the rest is cut off without warning. You may get wrong results! The program has a hint and when you hover the mouse, a short description of the setting or element is shown in the window.
After you play enough with the comparison example - copy your data for comparison to the files "List 1.txt" and "List 2.txt" and with the previously selected settings, click the "Process files" button. During file processing, the button “Processing in progress” appears in red, wait until the end of this process. At the end, look at the place where you started the program, in the folder with the program, depending on the settings, the files indicated in the comparison example appear. With each new comparison or opening / closing the program, all files except “List 1.txt” and “List 2.txt” are deleted.
And a little about the speed of comparison. Most real-world problems are solved almost instantly. Well, my tests are as follows (Intel Core processor for an LGA 775 socket with a frequency of 2 GHz):
Comparison of 2 lists of 1MB each (25 characters per line and 39 thousand lines in each list), for comparison it is obviously necessary to compare each line of the 1st list with all lines of the other. This gives us a total of 1.521 billion string comparisons. The execution time is about 20 seconds. Memory consumption less than 10MB.
Comparison of 2 lists of 10MB each (25 characters per line and 390 thousand lines in each list). This gives us 152.1 billion string comparisons. Lead time is about an hour. In this case, the program grabs itself about 200MB of RAM. Although such sizes are already the territory of databases. In this program, I have already used all the reasonable ways to increase speed.
Work algorithm and comparison parameters
The algorithm of work is such that with any settings, all characters are removed from the lines, except for Latin and Russian letters, numbers and signs of a period and comma. Of course, all extra spaces between words and spaces around the edges of the line are removed.
Search for matching lines and Search for differing lines - here everything is clear, will be looking for matching lines or different ones. Matches will be written to the "Matches.txt" file. When searching for different lines, they will be written to two files "Mismatches 1.txt" and "Mismatches 2.txt" for Lists 1 and 2, respectively. Also, in the comparison example area, instead of one window, two appear.
ATTENTION! When comparing lists for coincidence, there is a peculiarity, since the same lines are present in both lists, the result includes lines from list 1. Place more neatly formatted text in list 1 when comparing for matches.
Fix keyboard layout errors - Punto Switcher is certainly far from here. This refers to an error in typing similar letters in the wrong layout (C, H, P, etc.). For example Russian "s" and Latin. They are on the same key, and if the word begins with "c", then you can type the first letter in the English layout, and then switch to Russian or vice versa. The replacement algorithm is such that if there are more Russian letters in a word than English ones, then English letters are changed to Russian ones and vice versa.
Correct Yo (e) to E (e) - just all "e" are replaced by "e".
Compare case insensitive - all letters become large.
Compare by unique strings - If the parameter is enabled, the lists are first checked for matching lines. If a string is repeated for example 5 times, then one copy of this string remains in the list for comparison, and the 4th is sent to the list of "repeats". Repetitions for each word list are different.
Without this parameter, the strings are compared in pairs. For example, when comparing on matching lines, if the 1st list contains 2 identical lines, and the 2nd list contains 3 more such lines, then the result will be only two lines, since The 3rd line did not find a pair with which it would match. If for this example we switch comparisons to non-matching lines, then the result of non-matching lines will include one line from list 2, since it doesn't match anything.
By using unique string comparison, you can find duplicate strings in the list. To do this, you can, for example, fill in lines only the file "List 1.txt" and compare it with the empty file "List 2.txt" and then in the file "RepeatСп1.txt" there will be lines from list 1.
Repeats only for rows from the result - only works in conjunction with unique string comparison. Without this parameter, all duplicate lines are included in repeat lists. If enabled, then only lines present in the result will be included in repetitions. The number of lines caught in repetitions is the same and is equal to the number of repetitions in the initial list minus 1.
Remove periods and commas and Remove all spaces - just deleted and that's it.
