Suppose you have two lists of student names in Column A and Column B of a worksheet, and some names are in these two columns, but you want to compare these two columns and insert different names from them.
Table of Contents
- 1 To list unique values from two lists separately, you can apply the following formulas
- 2 Compare Two Columns, Row by Row
- 2.1 Compare cells in the same row
- 2.2 Compare cells in the same row for exact or case-insensitive results (Use the IF function)
- 2.3 Compare cells in the same row and highlight matching or mismatched data (using Conditional Formatting)
- 2.4 Compare two columns row by row and highlight mismatches (using VBA)
To list unique values from two lists separately, you can apply the following formulas
1. Enter this formula in cell D2
(A2 is the cell containing the first name inside column A that you want to compare with column B, and the range $2:$14 refers to the column you want to compare) and then press the Enter key:
2. Drag the fill handle down to the cells where you want to apply this formula, and all names that appear uniquely in column A are inserted only once:
3. Continue to enter this formula: =IF((ISERROR(MATCH(B2,$A$2:$15,0,2))),BNUMX,””) into cell E2 and press Enter:
4. Select cell E2 and drag the fill handle to the cells where you want to contain this formula, then all the names that appear uniquely in column B are extracted as follows:
Now all names that appear uniquely in both columns are listed.
Compare Two Columns, Row by Row
Below is the dataset to check in the same row to see if the name in column A matches the name in column B.
Compare cells in the same row
Normally, if you want to compare two columns row by row for an exact match, you can use the following formula: =B2=C2
Press the Enter key and drag the fill handle down to cell D8. If the formula returns TRUE, then the two columns’ values are exactly the same; if it returns FALSE, they are different.
Compare cells in the same row for exact or case-insensitive results (Use the IF function)
If you want to compare two columns row-by-row in case-insensitive characters or add descriptions like match, don’t match, you can use the IF function.
Compare cells in the same row for an exact match
If you want to use the text “Match” and “Mismatch” to describe the result of the comparison, use the following formula: =IF(EXACT(B2,C2,“Match”,“Mismatch”)
Press the Enter key to get the first result, then drag the AutoFill handle to cell D8.
Compare cells in the same row for a case-insensitive match
If you want to compare cells case insensitive, you can use the below formula: =IF(B2=C2,”Match”,”Mismatch”)
Press the Enter key to get the first result, then drag the AutoFill handle to cell E8.
Compare cells in the same row and highlight matching or mismatched data (using Conditional Formatting)
If you want to distinguish the same or different values, the Conditional Format feature can help you.
1. Select the two columns to be used for comparison (B2:C8, excluding column headers), then click Home > Conditional Formatting > New Rule.
2. In the New Format Rule dialog box that appears, click to use the formula to specify the cells to format in Select a rule type, and then enter =$B2=$C2 in the Format Values text box the formula is correct.
3. Keep clicking a shape to bring up the Format Cells dialog box, then in the Run tab, select a color where you want to highlight the matches.
Or, you can change the font size, font size, cell boundaries, or number format to highlight matches as you want on other tabs.
4. Click OK to close the dialog boxes; cells in the same row will be selected if they are the same.
If you want to highlight mismatched values, you can use =$B2<>$C2 in Format values where this formula is true in the Edit Formatting Rule dialog box.
Then the difference between two columns in the same row will be highlighted with a specific color.
Compare two columns row by row and highlight mismatches (using VBA)
This tutorial is for you if you want to compare two columns row by row with VBA code.
1. Open the Worksheet containing the two columns used for comparison, press the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the pop-up dialog, click Insert > Module.
- Then copy and paste the macro below into the new script module.
- Sub ExtendOffice_HighlightColumnDifferences()
- Dim xRg As Range
- Dim xWs As Worksheet
- Dim xFI As Integer
- On Error Resume Next
- Set xRg = Application.InputBox(“Select two columns:”, “Kutools for Excel”, , , , , , 8)
- If xRg Is Nothing Then Exit Sub
- If xRg.Columns.Count <> 2 Then
- MsgBox “Please select two columns”
- GoTo SRg
- Set xWs = xRg.Worksheet
- For xFI = 1 To xRg.Rows.Count
- If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then
- Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 ‘you can change the color index as you need.
- Next xFI
- End Sub
Press the F5 key to run the code. In the first pop-up dialog, select the column for which you want to compare duplicate values.
Click OK. In the second dialog box, select the column for which you want to highlight duplicate values.
The code that compares columns is case sensitive.
You can change the color shading based on your own needs by changing the color index in the code, referencing the color index: