compare two columns in excel to find matches

Compare two columns in excel to find matches how to#
Compare two columns in excel to find matches code#

The following screenshot shows the results of the case-sensitive string comparison in Excel: =IF(EXACT(A2 ,B2), 'Exactly equal', 'Not equal') If you want the EXACT function to deliver some other results, embed it in an IF formula and type your own text for value_if_true and value_if_false arguments: Where text1 and text2 are the two cells you are comparing.Īssuming your strings are in cells A2 and B2, the formula goes as follows:Īs the result, you get TRUE for text strings match exactly including the case of each character, FALSE otherwise. Case-sensitive text comparison can be done using the Excel EXACT function: In some situations, it may be important not only to compare text values of two cells, but also to compare the character case. For example:Īs you see in the screenshot below, both formulas compare text strings, dates and numbers equally well:Ĭase-sensitive formula to compare strings in Excel If you want to output your own texts for matches and differences, embed the above statement in the logical test of the IF function. The result of the formula are Boolean values TRUE and FALSE. Where A1 and B1 are the cells you are comparing. To compare two cells in Excel ignoring case, use a simple formula like this:

Case-insensitive formula to compare 2 cells There are two different ways to compare strings in Excel depending on whether you seek case-sensitive or case-insensitive comparison. Compare strings by occurrences of specific character.Compare a range of cells to a sample cell.

Compare two columns in excel to find matches how to#

This tutorial will teach you how to automate the tedious and error-prone task of cell comparison and what formulas are best to use in each particular case. It's no big deal to compare two cells manually, but it's next to impossible to spot the differences between hundreds and thousands of text strings. In this case, the only remedy is to check data for accuracy. While Excel formulas are always perfectly true, their results may be wrong because some flawed data penetrated into the system. Incorrect information leads to missed deadlines, misjudged trends, wrong decisions and lost revenues. When using Excel for data analysis, accuracy is the most vital concern. You will learn a number of formulas to compare two cells by their values, string length, or the number of occurrences of a specific character, as well as how to compare multiple cells. Cells(i, 2)).Interior.The tutorial shows how to compare text strings in Excel for case-insensitive and exact match. Highlight differences Sub HighlightColumnDifferences() Remember to save your file in either XLSM or XLAM format to save your VBA code. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts.

Compare two columns in excel to find matches code#

The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Copy and paste the code into the module to run it. Below are some index numbers for colors:įirst, you need to add the module into the workbook or the add-in file to be able to use the code. With statement allows us to not use bothcolumns every time, and the For…Next loop is how we can check for every cell.Īfter you get the range, you can use the ColorIndex property to set that range a color. Selected range is assigned to a range variable bothcolumns, and each cell in that range is compared row by row. The code requires the user to select columns in a single range. vbDatabaseCompare: Performs a comparison based on information in your database.vbTextCompare: Performs a textual comparison.vbBinar圜ompare: Performs a binary comparison.vbUseCompareOption: Performs a comparison using the setting of the Option Compare statement.If not omitted, the Option Compare setting will determine the type of comparison. Note that the third argument is optional. First 2 arguments are the strings to be compared, and the last one is the comparison type. Zero ' 0' means a perfect match, and the sample code we give below highlights the cell if the result is NOT equal to 0.

This function returns an integer based on the result of the comparison.

VBA has a built in function called StrComp, which can compare two separate strings. Let us show you how to compare two columns in Excel using VBA and automate this process. Comparing columns of data manually is not a realistic approach when working with large data sets.