#Tool to compare two columns in excel how to#

To help you learn this technique, sample Excel worksheets are available. The duplicates can be anywhere within the data set and this function will still find them. This method is extremely flexible because the duplicates donвЂ™t have to be in the same row in both data sets. Similarly, youвЂ™d deselect 1 to display your non-duplicate records and select Select All to view them all.

To see the duplicate record(s), select 0, and click OK.

In Excel 2003, choose Filter from the Data menu, and then select AutoFilter.

Click the Data tab and then click Filter in the Sort & Filter group to display dropdowns for each column.

You can use this column to filter the data set, as follows: When the rows arenвЂ™t duplicates, the function returns 0. Explanation : COUNTIF function matches the C2 cell in array2 and returns its occurrence in number. This function returns 1 when the corresponding record in the second data set is a duplicate of the first data set. Now, enter the above function in D2 and copy to the remaining rows in the same data set. In addition, if the data sets are on different sheets, be sure to include the sheet identifier, appropriately. This example includes all three columns for a full-record comparison, but you could compare partial records by including only the columns you want to compare. In this example, countrangex will refer to a column in one data range and criteriax will refer to the first corresponding data field in the same column in the other data range, as follows: =COUNTIFS($A$9:$A$13,A2,$B$9:$B$13, B2, $C$9:$C$13,C2) The COUNTIFS() function uses the following syntax to specify multiple criteria to determine which values in a range to count: COUNTIFS( countrange1, criteria1. The other records share common values in columns A and B, but not C.В To quickly expose any duplicates, you can use COUNTIFS() to compare both data sets. However, using the COUNTIFS() function, you can also compare two data sets for duplicate records.ДЇor instance, the two data sets shown below share only one duplicate record, row 4. The 2010 entry, Use formulas to find multi-column duplicates in Excel, combines IF() and COUNTIF() to find duplicate records within the same data set. Using the COUNTIFS() function, you can compare different data sets for duplicate records. There are lots of ways to find duplicates in the same set of records. Excel selects the difference between the two columns.Use COUNTIFS() to compare two data sets in Excel

In the Go To Special pop-up window, click Row differences, then click OK.

In the file menu at the top of the program window, click Edit and select Go To.

Similarly, you can right-click and select the paint bucket to highlight the cells.

To highlight these differences, leave them selected, and click the paint bucket icon in the font menu and select the color you want to use.I have created a new column in Column C and named it Matches. This technique will compare two columns in Excel and show the unique values as the difference between the columns in a separate column. Alternative Way 6: Compare Two Lists and Return Differences in Excel.

Excel selects the difference between the two columns. 4+ Methods to Filter Duplicate Values in Excel. In the Go To pop-up window, click Row differences, then click OK.In the Ribbon, on the Home tab, go to Find & Select, then click Go To.

#Tool to compare two columns in excel how to#

How to select one or more cells in a spreadsheet program.

Select all the cells in both columns containing the data to be compared.

Open the Excel spreadsheet containing the data you would like to compare.

Alternatively, press the F5 key, then click Special on the pop-up screen. You can use the shortcut Ctrl+ G, then click Special to open the Go To menu.