Comparison of files in Excel on coincidence. How to compare two columns in Excel on coincidence. Search and highlighting coincidences in several columns in Excel

Often the task is to compare two list of items. Do this manually too tiring, and besides, it is impossible to exclude the likelihood of errors. Excel simplifies this operation. This Council describes the method using conditional formatting.

In fig. 164.1 An example of two multi-line name lists is given. The application of conditional formatting can make differences in the lists apparent. These sample lists contain text, but the method under consideration works with numeric data.

First List - A2: B31, This range is called OldList.. Second list - D2: E31, Range called NEWLIST.. The ranges were named using the command. Formulas specific names to assign a name. Give names to the ranges optionally, but it facilitates work with them.

Let's start with the addition of conditional formatting to the old list.

  1. Select the range of range OldList..
  2. Select.
  3. In the window Creating a formatting rule Select the item called Use formula
  4. Enter this formula in the window field (Fig. 164.2): \u003d Schedules (NewList; A2) \u003d 0.
  5. Press the button Format And set the formatting that will be applied when the condition is true. It is best to choose different colors of the fill.
  6. Click OK.

Cells in the range NEWLIST. Use such a formula for conditional formatting.

  1. Select the range of range NEWLIST..
  2. Choose Main conditional formatting Create Rule To open the dialog box Creating a formatting rule.
  3. In the window Creating a rule Formatting Select Use formula To determine the formatted cells.
  4. Enter this formula in the window field: \u003d OLDLIST; D2) \u003d 0.
  5. Press the button Format And set the formatting that will be applied when the condition is true (another color of the fill).
  6. Click OK.

As a result, the names that are in the old list, but not in the new, will be dedicated (Fig. 164.3). In addition, the names in the new list that are not in the old one are also highlighted, but in other color. Names that appear in both lists are not highlighted.

Both conditional formatting format use function Countess. It calculates how many times a definite value appears in the range. If the formula returns 0, it means that the element is not included in the range. Thus, conditional formatting is taken for business, and the color of the cell's background is changing.

Need to compare two files Microsoft Excel.? Here are two simple way to do this.

There are many reasons for which you may need to take one excel document And compare it with another. It can be a time-consuming task

it requires great concentration, but there are ways to facilitate life.

Do you need to carefully watch manually or you want Excel to perform some hard work

from your behalf, here are two simple ways to compare several sheets.

How to compare Excel files

Excel allows users to immediately display two versions of the document to quickly set differences between them:

  1. First open the working books to compare.
  2. Switch to View\u003e Window\u003e View near.

Eye Excel Files Comparison

First, open Excel and all the working books you want to compare. We can use the same technique to compare sheets in one document.

or completely different files.

If more than one sheet is obtained from the same book, it must be separated in advance. To do this, go to View\u003e Window\u003e New Window.

This will not divide individual sheets forever, just will open a new instance of your document.

In this menu, all tables will be listed that this moment Open. If you have only two open, they will be selected automatically.

Make your choice and click Okay. You will see that both tables will appear on the screen.

If it is more convenient, you can use Put everything Button to switch between vertical and horizontal configuration.

One important option to know is Synchronous scrolling Switch.

The inclusion of this parameter ensures that when scrolling one window, the other will move synchronously. It is important if you work with a big table

and you want to continue checking one against the other. If for any reason two sheets are not aligned, just click Reset window position.

Comparison of Excel files using conditional formatting

In many cases the best way Comparison of two spreadsheets may be to simply display them on the screen at the same time. However, it is possible to automate several automate.

Using conditional formatting

We can check Excel for the presence of discrepancies between the two sheets. It can save a lot of time if everything you need to find is the differences between one version and the other.

For this method, we need to make sure that two sheets we work are part of one working book. To do this, click right-click Mouse List Name you want to transfer and select Move or copy.

Here you can use the drop-down menu to decide which document it will be inserted.

Select all the cells that are filled with the sheet where you want any differences to be highlighted. Fast way Make it - click the cell in the upper left corner, and then use a shortcut

Ctrl + Shift + End.

Switch to Home\u003e Styles\u003e Conditional Formatting\u003e New Rule.

Choose Use the formula to determine which cells format And enter the following:

A1Sheet_Name! A1

Just do not forget to lay out "Sheet_name" for any name of another sheet. This formula only performs a check when the cell on one sheet does not exactly correspond to the corresponding cell on another sheet, and makes each instance.

Above you can see the results. All cells containing changes were highlighted in red, which allows you to quickly and easily compare two sheets.

Let Excel do hard work

The above technique demonstrates one way you can allow Excel to cope with some serious tasks. Even if you are paying close attention, there is a possibility that you can skip the change if you will perform this task manually. Thanks to the conditional formatting, you can be sure that nothing slips into the network.

Excel is good in monotonous and in detail oriented works. As soon as you will master its capabilities, you can save some time and effort using such a technique as conditional formatting and a little ingenuity.

Do you have advice on the comparison of documents in Excel? Or do you need help with the processes described in this manual? In any case, why not join the conversation in the comments section below?

Question from the user

Hello!

I have one task, and for the third day I break my head - I do not know how to perform it. There are 2 tables (approximately 500-600 lines in each), you need to take a column with the name of the goods from one table and compare it with the name of the goods from the other, and if the goods coincide - copy and substitute the value from Table 2 in Table 1. Confurteated explained But I think, on the photo the task will understand ( approx. : photo cut out censorship, after all, personal information).

Thank you in advance. Andrei, Moscow.

Good day to everyone!

What you described - refers to fairly popular tasks that are relatively simple and quickly solving with using Excel. It is enough to drive two of your tables into the program, and use the FPR function. About her work below ...

An example of working with the function of the enterprise

As an example, I took two small plates, they are presented in the screenshot below. In the first table (columns A, B. - product and price) no data on a column B.; In the second - both columns are filled (product and price). Now you need to check the first columns in both tables and automatically, with the coincidence found, copy the price in the first sign. It seems, the task is simple ...

How to do it...

Put the mouse pointer to the cell B2. - I mean in the first cell column, where we do not have the meaning and writing the formula:

\u003d APP (A2; $ E $ 1: $ F $ 7; 2; lie)

A2. - value from the first column of the first table (what we will look for in the first column of the second table);

$ E $ 1: $ F $ 7 - Fully allocated second table (in which we want to find something and copy). Pay attention to the "$" icon - it is necessary that the cells of the selected second table should be changed when copying the formula;

2 - The number of the column from which will be able to copy the value (Note that we have a dedicated second table has only 2 columns. If it had 3 columns - then the value could be copied from the 2nd or 3rd column);

FALSE - We are looking for an exact coincidence (otherwise the first similar is substituted, which is clearly not suitable for us).

Actually, you can make a ready-made formula for your needs, slightly changing it. The result of the formula is presented in the picture below: the price was found in the second table and substituted in auto mode. Everything is working!

In order for the price to be affixed and for other product items - just stretch (copy) to the formula to other cells. Example below.

After that, as you see, the first columns in the tables will be compared: from the rows where the values \u200b\u200bof the cells coincided - the necessary data will be copied and substituted. In general, it is clear that the tables can be much more!

Note : I must say that the PRD function is quite demanding to computer resources. In some cases, with an overly large document, to compare tables may need quite a long time. In these cases, it is worth considering either other formulas or completely different solutions (each case is individual).

That's all, good luck!

Sometimes you want to view in the Access table only those records that are complied with the records from another table containing fields with the coincident data. For example, you may need to see the records of employees who have treated at least one order to determine which of them deserves encouragement. Or you may need to view the contact details of customers living in one city with an employee to organize a personal meeting.

If you need to compare two ACCESS tables and find the coinciding data, two options are possible.

    Create a request that combines fields from each table that contains the appropriate data using an existing link or a combination created for the request. This method is optimal in the speed of returning the results of the query, but does not allow combining fields with different types of data.

    Create a query to compare fields in which one field is used as a condition for the other. This method usually requires more time, because when merging the line is excluded from the query results before reading the basic tables, while the conditions apply to the query results after reading these tables. But the field can be used as a condition for comparing fields with different types of data, which cannot be done when using associations.

This article discusses the comparison of two tables to identify the coincidence data and a sample of data that can be used in the examples examples is given.

In this article

Comparison of two tables using associations

To compare the two tables using merges, you need to create a selection request, including both tables. If there is no connection between the tables in the fields containing the necessary data, you need to create associations on them. Associations can be created as much as possible, but each pair of combined fields must contain the data of the same or compatible type.

Suppose you work at the university and want to learn how recent changes in curriculum in mathematics influenced students' assessments. In particular, you are interested in estimating those students who have a form of mathematics. You already have a table containing data on profiling subjects, and a table containing the data on students that are studied. Evaluation data is stored in the "Students" table, and data on profiling items in the Specialization Table. To see how after recent changes in the curriculum, estimates have changed from those who specialize in mathematics, you need to view records from the "Students" table corresponding to the records in the Specialization Table.

Preparation of Data Example

IN this example You create a request that determines how recent changes in the curriculum in mathematics influenced students with the appropriate profiling subject. Use the two tables below: "specializations" and "students". Add them to the database.

Access provides several ways to add these tables a sample database. You can manually enter data, copy each table into the program spreadsheet (for example, Microsoft Office. Excel 2007) and then import sheets in Access or you can insert data in text editor, for example, notepad and then import data from the resulting text files.

IN step-by-step instructions This section is explained how to enter data manually on an empty sheet, as well as how to copy examples of tables in Excel and then import them into Access.

Specialization

Student code

Specialization

Students

Student code

Semester

Syllabus

Subject number

Evaluation

If you are going to enter an example of data in a spreadsheet, you can.

Entering data examples manually

If you are not interested in creating a sheet based on the data example, skip the following partition ("Creating sheets with data examples").

Creating sheets with data examples

Creating sheet database tables based on sheets


Comparison of table samples and search for relevant entries using associations

Now everything is ready for comparing the "Students" and "specialization" tables. Since the connections between the two tables are not defined, you need to create merges the corresponding fields in the request. Tables contain several fields, and you will need to create an association for each pair of shared fields: "student code", "year", as well as a "curriculum" (in the "Student Table") and "specialization" (in the Specialization Table) . In this case, we are only interested in mathematics, so you can limit the results of the query using the field condition.

    On the tab Creature Press the button Designer of requests.

    In the dialog box Adding a table Double-click the table that contains the necessary records ( Students), and then double-click the table with which it compares it ( Specialization).

    Close the dialog box Adding a table.

    Drag the field Student code from the table Students in field Student code Tables Specialization. A line will appear in the query form between the two tables, which shows that the union is created. Double-click the line to open the dialog box. Union parameters.

    Pay attention to the three options in the dialog box. Union parameters. The default option 1. In some cases, add to the union parameters additional strings from one table. Since you need to find only the coinciding data, leave for merging the value 1. Close the dialog box Union parametersBy pressing the button Cancel.

    You need to create two more associations. To do this drag the field Year from the table Students in field Year Tables Specializationand then - field Syllabus from the table Students in field Specialization Tables Specialization.

    Table Students Double click Star ( * ) To add all the fields of the table in the request form.

    Note: Students. *.

    Table Specialization Double-click the field SpecializationTo add it to the blank.

    Show column Specialization.

    In line Selection condition column Specialization Enter Mat.

    On the tab Constructor in a group results Press the button Perform.

Comparison of two tables using the field as a condition

Sometimes it is necessary to compare tables based on fields with coincident values, but different types data. For example, a field in one table may have a numeric data type, and it is necessary to compare it with a field from another table that has text Type data. Fields containing similar data from different types may appear when saving numbers as text (for example, when importing data from another program). Since it is impossible to create field combining fields with different types of data, you will need to use another way. To compare two fields with different type data, you can use one field as a condition for the other.

Suppose you work at the university and want to learn how recent changes in curriculum in mathematics influenced students' assessments. In particular, you are interested in estimating those students who have a form of mathematics. You already have the "specialization" and "students" tables. Evaluation data is stored in the "Students" table, and data on profiling items in the Specialization Table. To see how the assessments have changed from those who specialize in mathematics, you need to view recordings from the "Students" table, corresponding to the records in the Specialization Table. However, one of the fields you want to use to compare tables, the data type is not like the field with which it is compared.

To compare the two tables using the field as a condition, you need to create a selection request, including both tables. Turn on the field request you want to display, as well as the field corresponding to the field that will be used as a condition. Create a condition for comparing tables. You can create as many conditions as the fields are required for comparison.

To illustrate this method, we use, but in the "Student Code" field, the "specialization" table will change the number of data on text. Since it is impossible to create a combination of two fields with different types of data, we will have to compare two fields "student code" using one field as a condition for the other.

Changing the data type in the "Student Code" field "Specialization"

    Open the database in which you saved examples of tables.

    IN areas of navigation Click the "specialization" table with the right mouse button and select item. Constructor.

    The "specialization" table will open in the constructor mode.

    In column Data type Change for field Student code Data type Number on the Text.

    Close the "specialization" table. Press the button YesWhen you are prompted to save changes.

Comparison of table examples and search for relevant entries using field condition

Below is shown how to compare the two fields "student code" using the field from the "Students" table as the condition for the field from the Specialization Table. Via keywords Like. You can compare two fields, even if they contain different types of data.

    On the tab Create in a group Other Press the button Designer of requests.

    In the dialog box Adding a table Double-click Table Studentsand then table Specialization.

    Close the dialog box Adding a table.

    Drag the field Year from the table Students in field Year Tables Specializationand then - field Syllabus from the table Students in field Specialization Tables Specialization. These fields contain the data of the same type, so for their comparison, you can use associations. To compare fields with a single type data, it is recommended to use merges.

    Double click Star ( * ) Table StudentsTo add all the fields of the table in the request form.

    Note: When using an asterisk to add all fields in the form, only one column is displayed. The name of this column includes the name of the table, followed by the point (.) And asterisk (*). In this example, the column gets the name Students. *.

    Table Specialization Double-click the field Student codeTo add it to the blank.

    In the query form, uncheck the box in the string Show column Student code. In line Selection condition column Student code Enter Like [Students]. [Student code].

    Table Specialization Double-click the field SpecializationTo add it to the blank.

    In the query form, uncheck the box in the string Show column Specialization. In line Selection condition Enter Mat.

    On the tab Constructor in a group results Press the button Perform.

    The request is executed, and evaluations are displayed in mathematics of only those students who have this subject profiling.

The article gives any answers to the following questions:

  • How to compare two tables in Excel?
  • How to compare complex tables in Excel?
  • How to make a comparison of tables in Excel using the VD () function?
  • How to form unique lines identifiers if their uniqueness is initially determined by the set of values \u200b\u200bin several columns?
  • How to fix the values \u200b\u200bof cells in formulas when copying formulas?

When working with large amounts of information, the user may encounter such a task as a comparison of two tabular data sources. When storing data in unified system accounting (for example, the system based on 1C enterprise, systems using SQL base data), for comparison of data, it can be used built into the system or DBMS possibilities. As a rule, this is enough to attract a programmer that will write a request to the database, or the reporting mechanism of the report. An experienced user who owns the skill of writing requests 1c or SQL can cope with the query.

Problems begin when it is necessary to perform the task of the comparison of the data urgently, and the programmer attracting and writing them the query or program report may exceed the time limit set for solving the task. Another equally widespread problem is the need to compare information from various sources. In this case, the setting of the task for the programmer will sound as the integration of two systems. The solution to such a task will require a higher qualification of the programmer and will also take longer than the development in a single system.

To solve the designated problems, the ideal reception is to use to compare the data tabular editor Microsoft. Excel. Most common management and regulated accounting systems maintain unloading to Excel format. This task will require only a certain qualification of the user to work with this. office package And it will not require programming skills.

Consider the solution of the task of comparing tables in Excel on the example. We have two tables containing lists of apartments. Sources of unloading - 1C Enterprise (construction accounting) and a table in Excel (sales accounting). Tables are located in the Excel workbook on the first and second sheets, respectively.

We have a task to compare these lists at. In the first table - all apartments at home. In the second table - only sold apartments and the name of the buyer. The ultimate goal is to display in the first table for each apartment the name of the buyer (for those apartments that were sold). The task is complicated by the fact that the address of the apartment in each table is construction and consists of several fields: 1) Case address (at home), 2) Section (entrance), 3) Floor, 4) Room on the floor (for example, from 1 to 4) .

For comparison of two excel tables We need to ensure that in both tables each line it would be identified with one field, and not four. You can get such a field by connecting the values \u200b\u200bof four address fields by the Capture Function (). Purpose Function Catch () - Combining multiple text values \u200b\u200bto one line. Values \u200b\u200bin the functions are listed through the ";" symbol. As values, both cell addresses and arbitrary text specified in quotes can appear as values.

Step 1. Insert the empty column "A" at the beginning of the first table and in the cell of this column opposite the first line with the data formula:
\u003d Catch (B3; "-"; C3; "-"; D3; "-"; E3)
For the convenience of visual perception between the values \u200b\u200bof the cells of the cells, we set the characters "-".

Step 2. Copy the formula in the following cells of the column A.

Step 4. To compare Excel tables by values, use the PRD () function. Assigning the PRD () function - search for values \u200b\u200bin the extreme left column of the table and return the value of the cell located in the specified column of the same string. The first parameter is the desired value. The second parameter is a table in which the search will be found. The third parameter is the column number, from the cell of which the value will be returned in the found line. The fourth parameter is the type of search: a lie - the exact coincidence, the truth is an approximate coincidence. Since the output should be placed in the first table (it was necessary to add buyers' names), then we will prescribe the formula in it. We form in the free column to the right of the table opposite the first line of these formula:
\u003d VD (A3; List2! $ A $ 3: $ F $ 10; 6; lie)
When copying the Smart formula, Excel automatically changes the addressing cells. In our case, the desired value for each row will be changed: A3, A4, etc., and the address of the table in which the search is conducted should remain unchanged. To do this, fix the cells in the address parameter of the table "$" symbols. Instead of "sheet2! A3: F10" Make "list2! $ A $ 3: $ F $ 10".