Comparing tables of values ​​1c 8. How to compare two columns in Excel for matches

Reading this article will take you about 10 minutes. In the next 5 minutes you can easily compare two columns in Excel and find out if there are duplicates in them, delete them or highlight them with color. So, time has come!

Excel is a very powerful and really cool application for creating and processing large amounts of data. If you have multiple workbooks with data (or just one huge table), then you will probably want to compare 2 columns, find duplicate values, and then do something with them, such as deleting, highlighting, or clearing the contents . Columns can be in the same table, adjacent or non-adjacent, located on 2 different sheets or even in different workbooks.

Imagine we have 2 columns of people's names - 5 names per column A and 3 names in a column B. You need to compare the names in these two columns and find any duplicates. As you understand, this is fictitious data taken for illustrative purposes only. In real tables we are dealing with thousands, or even tens of thousands of records.

Option A: both columns are on the same sheet. For example, column A and column B.

Option B: The columns are located on different sheets. For example, column A on a sheet Sheet2 and column A on a sheet Sheet3.

Excel 2013, 2010 and 2007 have a built-in tool Remove Duplicate(Remove Duplicates) but it is powerless in this situation because it cannot compare the data in 2 columns. Moreover, it can only remove duplicates. There are no other options, such as highlighting or changing colors. And period!

Compare 2 columns in Excel and find duplicate entries using formulas

Option A: both columns are on the same sheet


Clue: In large tables, copying a formula will be faster if you use keyboard shortcuts. Select a cell C1 and press Ctrl+C(to copy the formula to the clipboard), then click Ctrl+Shift+End(to select all non-blank cells in column C) and finally click Ctrl+V(to paste the formula into all selected cells).


Option B: two columns are on different sheets (in different books)

Processing found duplicates

Great, we found entries in the first column that are also present in the second column. Now we need to do something with them. Manually going through all the duplicate entries in a table is quite inefficient and takes too much time. There are better ways.

Show only duplicate rows in column A

If your columns do not have headings, then you need to add them. To do this, place the cursor on the number indicating the first line, and it will turn into a black arrow, as shown in the figure below:

Right-click and select from the context menu Insert(Insert):

Give the columns names, for example, “ Name" And " Duplicate?” Then open the tab Data(Data) and press Filter(Filter):

After that, click the small gray arrow next to “ Duplicate?“ to expand the filter menu; uncheck all items in this list except Duplicate, and press OK.

That's it, now you see only those column elements A, which are duplicated in the column IN. There are only two such cells in our training table, but, as you understand, in practice there will be many more of them.

To display all rows of a column again A, click the filter symbol in the column IN, which now looks like a funnel with a small arrow and select Select all(Select all). Or you can do the same through the Ribbon by clicking Data(Data) > Select & Filter(Sort and Filter) > Clear(Clear) as shown in the screenshot below:

Change color or highlight found duplicates

If the marks “ Duplicate” is not sufficient for your purposes, and you want to mark duplicate cells with a different font color, fill color, or some other way...

In this case, filter duplicates as shown above, select all filtered cells and click Ctrl+1 to open the dialog box Format Cells(Cell Format). As an example, let's change the fill color of the cells in rows with duplicates to bright yellow. Of course, you can change the fill color using the tool Fill(Fill Color) tab Home(Home), but the advantage of the dialog box Format Cells(Format Cells) is that you can configure all formatting options at once.

Now you will definitely not miss a single cell with duplicates:

Removing duplicate values ​​from the first column

Filter the table to show only cells with duplicate values, and select those cells.

If the 2 columns you are comparing are on different sheets, that is, in different tables, right-click the selected range and select from the context menu Delete Row(Delete line):

Click OK when Excel asks you to confirm that you really want to delete the entire worksheet row and then clear the filter. As you can see, only rows with unique values ​​remain:

If 2 columns are located on one sheet, close to each other (adjacent) or not close to each other (not adjacent), then the process of removing duplicates will be a little more difficult. We can't delete the entire row with duplicate values ​​because that would delete cells from the second column too. So, to keep only unique entries in a column A, do the following:

As you can see, removing duplicates from two columns in Excel using formulas is not that difficult.

Sometimes there is a need to compare two MS Excel files. This may be finding discrepancies in prices for certain items or changing any indications, it doesn’t matter, the main thing is that it is necessary to find certain discrepancies.

It would not be amiss to mention that if there are a couple of records in the MS Excel file, then there is no point in resorting to automation. If the file contains several hundred, or even thousands of records, then it is impossible to do without the help of the computing power of a computer.

Let's simulate a situation where two files have the same number of lines, and the discrepancy must be looked for in a specific column or in several columns. This situation is possible, for example, if you need to compare the price of goods according to two price lists, or compare measurements of athletes before and after the training season, although for such automation there must be a lot of them.

As a working example, let's take a file with the performance of fictitious participants: 100-meter run, 3000-meter run, and pull-ups. The first file is a measurement at the beginning of the season, and the second is the end of the season.

The first way to solve the problem. The solution is only using MS Excel formulas.

Since the records are arranged vertically (the most logical arrangement), it is necessary to use the function. If you use horizontal placement of records, you will have to use the function.

To compare 100 meter running performance, the formula is as follows:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,3,TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13,3,TRUE);"No difference")
If there is no difference, a message is displayed that there is no difference; if there is a difference, then the value at the end of the season is subtracted from the value at the beginning of the season.

The formula for the 3000 meter run is as follows:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,4,TRUE)<>E2;"There is a difference";"There is no difference")
If the final and initial values ​​are not equal, a corresponding message is displayed. The formula for pull-ups can be similar to any of the previous ones; there is no point in giving it additionally. The final file with the discrepancies found is shown below.

A little clarification. To make the formulas easier to read, the data from the two files was moved into one (on different sheets), but this could not have been done.

Video comparing two MS Excel files using and functions.

The second way to solve the problem. Solution using MS Access.

This problem can be solved if you first import MS Excel files into Access. As for the method of importing external data itself, there is no difference in finding different fields (any of the presented options will do).

The latter is a connection between Excel and Access files, so when you change data in Excel files, discrepancies will be found automatically when you run a query in MS Access.

The next step after importing is to create relationships between tables. As a connecting field, select the unique field “Item No.”
The third step is to create a simple select query using the Query Builder.

In the first column we indicate which records need to be displayed, and in the second - under what conditions the records will be displayed. Naturally, for the second and third fields the actions will be similar.

Video comparing MS files to Excel using MS Access.

As a result of the manipulations performed, all records are displayed, with different data in the field: “Running 100 meters.” The MS Access file is presented below (unfortunately, SkyDrive does not allow embedding as an Excel file)

These two methods exist for finding discrepancies in MS Excel tables. Each has both advantages and disadvantages. Obviously, this is not an exhaustive list of comparisons between the two Excel files. We are waiting for your suggestions in the comments.

We have two order tables copied into one worksheet. It is necessary to compare data from two tables in Excel and check which positions are in the first table but not in the second. There is no point in manually comparing the value of each cell.

Compare two columns for matches in Excel

How to compare values ​​in Excel of two columns? To solve this problem, we recommend using conditional formatting, which quickly highlights items that are in only one column. Worksheet with tables:

The first step is to name both tables. This makes it easier to understand which cell ranges are being compared:

  1. Select the FORMULAS - Defined Names - Assign Name tool.
  2. In the window that appears, in the “Name:” field, enter the value – Table_1.
  3. Use the left mouse button to click on the “Range:” input field and select the range: A2:A15. And click OK.

For the second list, perform the same steps, only give it a name – Table_2. And specify the range C2:C15 - respectively.

Helpful advice! Range names can be assigned more quickly using the names field. It is located to the left of the formula bar. Simply select ranges of cells, and in the name field, enter the appropriate name for the range and press Enter.

Now let's use conditional formatting to compare two lists in Excel. We need to get the following result:



Items that are in Table_1 but not in Table_2 will be displayed in green. At the same time, positions that are in Table_2, but not in Table_1, will be highlighted in blue.

The principle of comparing data between two columns in Excel

When defining the conditions for formatting the column cells, we used the COUNTIF function. In this example, this function checks how many times the value of the second argument (for example, A2) appears in the list of the first argument (for example, Table_2). If number of times = 0 then the formula returns TRUE. In this case, the cell is assigned the custom format specified in the conditional formatting options.

The link in the second argument is relative, which means that all cells of the selected range will be checked one by one (for example, A2:A15). For example, to compare two price lists in Excel, even on different sheets. The second formula works similarly. The same principle can be applied to various similar tasks.

Perhaps everyone who works with data in Excel is faced with the question of how to compare two columns in Excel for similarities and differences. There are several ways to do this. Let's take a closer look at each of them.

How to compare two columns in Excel row by row

When comparing two columns of data, it is often necessary to compare the data in each individual row for matches or differences. We can do such an analysis using the function. Let's see how this works using the examples below.

Example 1: How to compare two columns for matches and differences in the same row

In order to compare the data in each row of two columns in Excel, let's write a simple formula. The formula should be inserted into each row in the adjacent column, next to the table in which the main data is located. Having created a formula for the first row of the table, we can stretch/copy it to the remaining rows.

In order to check whether two columns of the same row contain the same data, we need the formula:

=IF(A2=B2; “Match”; “”)

The formula that determines the differences between the data of two columns in one row will look like this:

=IF(A2<>B2; "Do not match"; “”)

We can fit the match and difference test between two columns on one line in one formula:

=IF(A2=B2; “Match”; “Do not match”)

=IF(A2<>B2; "Do not match"; "Match up")

An example calculation result might look like this:

To compare data in two columns of the same row in a case-sensitive manner, use the formula:

=IF(MATCH(A2,B2), “Match”, “Unique”)

How to compare multiple columns for matches in one Excel row

Excel allows you to compare data in multiple columns of the same row using the following criteria:

  • Find rows with the same values ​​in all columns of the table;
  • Find rows with the same values ​​in any two columns of the table;

Example 1. How to find matches in one row in multiple columns of a table

Let's imagine that our table consists of several columns of data. Our task is to find rows in which the values ​​​​match in all columns. Excel and functions will help us with this. The formula for determining matches will be as follows:

=IF(AND(A2=B2,A2=C2); “Coincide”; “ “)

If our table has a lot of columns, then it will be easier to use the function in combination with:

=IF(COUNTIF($A2:$C2,$A2)=3;”Match”;” “)

In the formula, “5” indicates the number of columns of the table for which we created the formula. If your table has more or less columns, then this value should be equal to the number of columns.

Example 2: How to find matches of the same row in any two columns of a table

Let's imagine that our task is to identify from a table with data in several columns those rows in which the data is the same or repeated in at least two columns. The and functions will help us with this. Let's write a formula for a table consisting of three columns of data:

=IF(OR(A2=B2,B2=C2,A2=C2);”Match”;” “)

In cases where there are too many columns in our table, our formula with the function will be very large, since in its parameters we need to specify the matching criteria between each column of the table. An easier way, in this case, is to use the .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Unique string”; “Not unique string”)

=IF(COUNTIF($B:$B,$A5)=0; “There are no matches in column B”; “There are matches in column B”)

This formula checks the values ​​in column B to see if they match the cells in column A.

If your table consists of a fixed number of rows, you can specify a clear range in the formula (for example, $B2:$B10). This will speed up the formula.

How to compare two columns in Excel for matches and highlight them in color

When we look for matches between two columns in Excel, we may need to visualize the found matches or differences in the data, for example using color highlighting. The easiest way to highlight matches and differences in color is to use Conditional Formatting in Excel. Let's look at how to do this using the examples below.

Finding and highlighting matches in color in multiple columns in Excel

In cases where we need to find matches in several columns, then for this we need:

  • Select the columns with data in which you want to calculate matches;
  • On the “Home” tab on the Toolbar, click on the menu item “Conditional Formatting” -> “Rules for highlighting cells” -> “Repeating values”;
  • In the pop-up dialog box, select “Repeating” in the left drop-down list, and in the right drop-down list, select what color repeating values ​​will be highlighted in. Click “OK” button:
  • After this, matches will be highlighted in the selected column:

Find and highlight matching rows in Excel

Finding matching cells with data in two or more columns and searching for matches of entire rows with data are different concepts. Please note the two tables below:

The tables above contain the same data. Their difference is that in the example on the left we were looking for matching cells, and on the right we found entire repeating lines with data.

Let's look at how to find matching rows in a table:

  • To the right of the table with the data, we will create an auxiliary column in which, opposite each row with the data, we will put a formula that combines all the values ​​of the table row into one cell:

=A2&B2&C2&D2

In the auxiliary column you will see the combined table data:

Now, to determine the matching rows in the table, do the following steps:

  • Select the area with data in the auxiliary column (in our example this is a range of cells E2:E15 );
  • On the “Home” tab on the Toolbar, click on the menu item “Conditional Formatting” -> “Rules for highlighting cells” -> “Repeating values”;
  • In the pop-up dialog box, select “Repeating” in the left drop-down list, and in the right drop-down list, select what color the repeating values ​​will be highlighted in. Click “OK” button:
  • After this, duplicate lines will be highlighted in the selected column:


Often the task is to compare two lists of elements. Doing this manually is too tedious, and also the possibility of errors cannot be ruled out. Excel makes this operation easy. This tip describes a method using conditional formatting.

In Fig. Figure 164.1 shows an example of two multi-column lists of names. Using conditional formatting can make differences in lists obvious. These list examples contain text, but the method in question works with numeric data as well.

The first list is A2:B31, this range is called OldList. The second list is D2:E31, the range is called NewList. Ranges were named using the command Formulas Defined names Assign a name. It is not necessary to name the ranges, but it makes working with them easier.

Let's start by adding conditional formatting to the old list.

  1. Select cells in a range OldList.
  2. Select.
  3. In the window Create a formatting rule select the item called Use formula
  4. Enter this formula in the window field (Fig. 164.2): =COUNTIF(NewList;A2)=0.
  5. Click the button Format and specify the formatting that will be applied when the condition is true. It is best to choose different fill colors.
  6. Click OK.

Cells in range NewList use a similar conditional formatting formula.

  1. Select cells in a range NewList.
  2. Select Home Conditional Formatting Create a Rule to open a dialog box Create a formatting rule.
  3. In the window Create a rule formatting select item Use formula to define the cells to be formatted.
  4. Enter this formula in the window field: =COUNTIF(OldList;D2)=0 .
  5. Click the button Format and set the formatting to be applied when the condition is true (different fill color).
  6. Click OK.

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

Both conditional formatting formulas use the function COUNTIF. It calculates the number of times a certain value appears in a range. If the formula returns 0, it means the item is not in the range. This way, conditional formatting takes over and the background color of the cell changes.



Share with friends:
Related publications