You are watching : Compare Excel columns
Compare two columns for matches and differences in Excel
This tutorial will show you how to compare two columns in Excel. This is a normal Excel job in our daily work. Comparing two columns can be done in different ways in Excel such as: E.g. comparing two columns row by row or cells by cells, comparing two columns to highlight match or difference etc. This article will cover most possible scenarios of comparing two columns, so you can meet and hope that it can help you.
Note
In this tutorial, some sample data is provided to better explain the methods of comparing two columns in different cases. Depending on your dataset, you may need to change or adjust some content (references). However, the basic principles remain the same. Or download the examples of each case directly if you just want to check if the methods work or not.
1. Compare two columns row by row
Below is a record where I need to check in the same row whether the names in column A match those in column B or not.
Click here to download the sample file
1.1 Compare cells in the same row for an exact match
In general, if you want to compare two columns row by row to get an exact match, you can use the following formula:
= B2 = C2
Press Next key and drag the fill handle down to cell D8. If the formula returns TRUE, the values of two columns are exactly the same. If FALSE is returned, they are different.
1.2 Compare cells in the same row for an exact match that is case-insensitive (using the IF formula).
If you want to compare two columns row by row to be case insensitive or get more descriptions like “match”, “not match”, you can use 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 comparison results, please use the following formula:
=IF(EXACT(B2,C2),”Match”,”Mismatch”)
Press Next Press the key to get the first result, then drag the auto-fill handle to cell D8.
Compare cells in the same row for a case-insensitive match
If you want to compare cells that are not case-sensitive, you can use the following formula:
=IF(B2=C2,”Match”,”Mismatch”)
Press Next Press the key to get the first result, then drag the auto fill handle to cell E8.
< /p>
Note
In the formulas above, you can change the text “Match” and “Mismatch” to your own description.
1.3 Compare cells in the same row and highlight matching or mismatched data (using conditional formatting).
If you want to highlight the matching or different values, click Conditional Formatting feature can help you.
1. Select the two columns to compare against (B2:C8, without column headers), then click Beautify > Conditional Formatting >New rule.
2. In the popping New Formatting Rule dialog, click to select Use a formula to determine which cells to format in Choose a rule type section, then type =$B2=$C2 in the text box of Format values where this formula is true.
3. Now click Format to display theFormat Cells dialog, then under Fill tab, select a color you want to use to highlight the Need matches.
You can also change font size, font size, cell borders or number format to match the matches you need in other tabs.
4 Click OK > OK To close the dialogs, cells in the same row will be highlighted if they are identical.
If you want to highlight the mismatch values you can use this in the =$B2 <> $ C2 in the Format values where this formula is true text box in the Edit formatting rule dialog.
Then the differences between two columns in the same row are highlighted with a specific color.
1.4 Compare two columns row by row and highlight non-matching values (using VBA).
If you want to compare two columns row by row with VBA code, this tutorial will satisfy you.
1. Activate the worksheet containing the two columns used for comparison and press Alt + F11 keys to activate the Microsoft Visual Basic for Applications window.
2. In the pop-up dialog box, click Insert > Module.
3. Then copy and paste the following macro into the new module script.
VBA: Compare two columns line by line and highlight the differences
Sub ExtendOffice_HighlightColumnDifferences()
'Update by Kutools20201016
Dim xRg As Range
Dim xWs As Worksheet
Dim xFI As Integer
On Error Resume Next
SRg:
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
End If
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
xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.
End If
Next xFI
end sub
4. press F5 When you press the key to run the code, a dialog box will appear for you to select the two columns.
5 Click OK. Then the differences of two columns were highlighted with a background color.
Note
You can change the highlight color to your liking by changing the color index in the code, the color index reference:
2． Compare two columns in cells and select or highlight duplicate or unique dates
In this part, the record is displayed as follows. You want to find all values that are in column B and column C at the same time, or the values in column B only.
Click here to download the sample file
2.1 Compare two columns cell by cell and display the comparison results in another column (using formula).
Here you can use the formula combined with IF and COUNTIF function to compare two columns and find the values in column B but not in column C.
=IF(COUNTIF($C$2:$C$8, $B2)=0, “No in C”, “Yes in C”)
Press Next Press the button and drag the handle to cell D8 to autofill.
Note
1. This formula compares two columns without case.
2. You can change the description “No in C” and “Yes in C” to others.
3. If you want to compare two whole columns, change the fixed range $C$2:$C$8 to $C:$C
2.2 Compare and highlight duplicate or unique data (using conditional formatting)
Conditional Formatting Feature in Excel is powerful. Here you can compare two columns cell by cell and then highlight the differences or matches as needed.
Mark all duplicates or unique values in two columns
1. Select two columns to compare against, then click Beautify > Conditional Formatting > Highlight cell rules > Duplicate values.
2. In the pop up Duplicate Values dialog box, select a highlight format you need from the drop down list of values using
3 Click OK. Then the duplicates were highlighted in two columns.
Note
If you want to highlight the unique values (the differences) in two columns, also click Beautify > Conditional Formatting > Highlight cell rules > Duplicate Values To display theDuplicate Values dialog, change the Duplicate to Unique Click in the left drop-down List a different format from the Values drop-down list and click OK.
The unique values are highlighted.
Find and highlight the values in column B if they are also in column C
If you want to highlight the values in column B that are also in column C, C.extra formatting function can also do you a favor.
1. Select column B2:B8 and click Beautify > Conditional Formatting > New rule.
2. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format from the Choose a rule type section, then type =COUNTIF($C$2:$C$8,$B2)> 0 in the text field of Format values where this formula is true.
3. Click Format on the GoFormat Cells dialog, under Fill tab, select a color to highlight the matches.
You can use other formats to highlight the values on the Font, Number, Border tabs.
4 Click OK > OK. Then the values in column B that also exist in column C have been highlighted with the specified color.
If you want to highlight the values that are only in column B but not in column C, repeat the above steps but change the formula in step 2 to =COUNTIF($ C$2: $C$8, $B2) = 0 inNew formatting rule dialog.
Then choose a different color to format the values.
Note
Here, the conditional formatting compares two columns in a case-insensitive way.
2.3 Compare and select or highlight duplicate or unique data (with a handy tool)
Sometimes after comparing two columns you can perform other actions on the matches or differences, e.g. B. Selection, Delete, Copy, etc. In this case, a handy tool – Select Same and Different CellsofKutools for Excel can directly select the matches or differences to get the to better perform next operation can also highlight the values directly.
After free installation Kutools for Excel, please do as follows:
1 click Kutools > Click (so that a tick appears) on the rectangle > Select Same and Different Cells.
2. Then in the Select Same and Different Cells dialog, please do as follows:
1) In the Find values in and According to two columns, select two columns to compare against.
2) Select Each row option;
3) Select Same Values or Different Values as you need;
4) If you want to highlight the values, choose the color you want.
3 Click OkClick OK to display a dialog box reminding you the number of values found OK around the to close the dialog. At the same time, the values were selected. Now you can delete, copy or perform other operations.
If you check the Fill the background color andFill the font color checkboxes, the result is shown as follows:
Note
If you want to compare case sensitive, check case sensitive .
This tool supports comparing two columns in different worksheets.
2.4 Compare two columns and list exact duplicates in another column (using VBA code).< /strong>
If you want to list the matching values in another column after comparing two columns cell by cell, the following macro code may help you.
1. Activate the sheet you want to compare two columns against, then press Alt + F11 keys to display the Microsoft Visual Basic for Applications window.
2 click Insert > Module in Microsoft Visual Basic for Applications window.
3. Then copy and paste the following code into the new blank module script.
VBA: List duplicates in adjacent column after comparing two columns
Sub ExtendOffice_FindMatches()
'Update by Kutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
Next xRgF2
Next xRgF1
end sub
4. Press F5 To run the code, two dialog boxes will appear one after the other, allowing you to select two columns separately.
Note: Please select the left column first and then the right column in the second dialog. Otherwise, the duplicates will replace the original data in the second column.
5 Click OK > OKThen the matches have been automatically listed in the right column of the two columns.
Note
The VBA code compares two columns case-sensitive.
2.5 Compare two columns and mark duplicates (using VBA code).
If you want to compare two columns cell by cell and then highlight the matches, you can try the following code.
1. Activate the sheet you want to compare two columns against, then press Alt + F11 keys to display the Microsoft Visual Basic for Applications window.
2 click Insert > Modul in Microsoft Visual Basic für Applikationen Fenster.
3. Kopieren Sie den folgenden Makrocode und fügen Sie ihn in das neue Leerzeichen ein Modul Skripte.
VBA: Vergleichen Sie zwei Spalten und markieren Sie Duplikate
Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
SRg:
Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SsRg
End If
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then
xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)
End If
Next
Next
End Sub
4. Drücken Sie F5 Schlüssel zum Ausführen des Codes. Wählen Sie im ersten Dialogfeld die Spalte aus, nach der Sie doppelte Werte vergleichen möchten.
5 Klicken OK. Wählen Sie im zweiten Dialogfeld die Spalte aus, in der Sie doppelte Werte hervorheben möchten.
6 Klicken OK. Jetzt wurden die Duplikate in der zweiten Spalte nach dem Vergleich mit der ersten Spalte mit einer Hintergrundfarbe hervorgehoben.
Anmerkung
1. Der Code vergleicht Spalten mit Groß- und Kleinschreibung.
2. Sie können die Hervorhebungsfarbe nach Ihren Wünschen ändern, indem Sie den Farbindex im Code, der Farbindexreferenz, ändern:
3. Vergleichen Sie mehrere Spalten in derselben Zeile
Manchmal möchten Sie möglicherweise mehr als zwei Spalten in derselben Zeile vergleichen, z. B. den in der Abbildung unten gezeigten Datensatz. Hier in diesem Abschnitt werden verschiedene Methoden zum Vergleichen mehrerer Spalten aufgelistet.
Klicken Sie hier, um die Beispieldatei herunterzuladen
3.1 Finden Sie vollständige Übereinstimmungen in allen Zellen in derselben Zeile (mithilfe der IF-Formel).
Um vollständige Übereinstimmungen zwischen Spalten in derselben Zeile zu finden, kann Ihnen die folgende IF-Formel helfen.
=IF(AND(B2=C2, B2=D2), “Full match”, “Not”)
Wenn die Zellen in derselben Zeile übereinstimmen, wird “Vollständige Übereinstimmung” oder “Nicht” angezeigt.
Presse Weiter Drücken Sie die Taste, um das erste Vergleichsergebnis zu erhalten, und ziehen Sie dann den automatischen Füllgriff auf Zelle E7.
Anmerkung
1. Die Formel vergleicht Spalten ohne Groß- und Kleinschreibung.
2. Wenn Sie mehr oder gleich drei Spalten vergleichen müssen, können Sie die folgende Formel verwenden:
=IF(COUNTIF($B2:$D2, $B2)=3, “Full match”, “Not”)
In der Formel ist 3 die Anzahl der Spalten. Sie können sie ändern, um sie Ihren Anforderungen anzupassen.
3.2 Suche nach Übereinstimmungen in zwei beliebigen Zellen in derselben Zeile (unter Verwendung der IF-Formel)
Manchmal möchten Sie herausfinden, ob zwei Spalten in derselben Zeile übereinstimmen. Verwenden Sie dazu die folgende IF-Formel.
=IF(OR(B2=C2, C2=D2, B2=D2), “Match”, “Not”)
In dieser Formel müssen Sie ein beliebiges Zellenpaar in derselben Zeile vergleichen. “Übereinstimmung” zeigt an, dass zwei Zellen übereinstimmen. Wenn keine Zellen übereinstimmen, zeigt die Formel “Nicht” an. Sie können die Texte nach Bedarf ändern.
Presse Weiter Taste und ziehen Sie den Füllgriff auf Zelle E7.
Anmerkung
1. Diese Formel unterstützt keine Groß- und Kleinschreibung.
2. If many columns are required to compare, comparing a pair of cells in the formula may be too long. In this case, you can use the following formula, which combines the IF and COUNTIF functions.
=IF(COUNTIF(C11:E11,B11)+COUNTIF(D11:E11,C11)+(D11=E11)=0,”Not”,”Match”)
3.3 Find complete matches in all cells in the same row and highlight them (using conditional formatting).
If you want to highlight the rows that all cells match with each other, you can use the Conditional Formatting function in Excel.
1. Select the area you are using, then click Beautify > Conditional Formatting > New rule.
2. In the New formatting rule dialog, select Use a formula to determine which cells to format from the Select a rule type section, then you can use any of the following formulas in the Format values where this formula is true text box.
=AND($B2=$C2, $B2=$D2)
Or
=COUNTIF($B2:$D2, $B2)=3
Note: If the number of columns is more than three, assuming 5, the formulas should be changed to:
=AND($B2=$C2, $B2=$D2, $B2=$E2, $B2=$F2)
Or
=COUNTIF($B2:$F2, $B2)=5
3 Click Format button to go to the Format Cells dialog box, and then select a fill color or other cell formatting to customize the Lines to highlight.
4 Click OK > OKNow only rows where all cells match will be highlighted.
Note
The above formulas are not case sensitive.
3.4 Compare multiple columns and highlight row differences
If you want to highlight the row differences, that is, compare column cells one by one and find the different cells according to the first column, you can use Excel’s built-in function.Go to Special.
1. Select the area where you want to emphasize line differences and click Beautify > Search & Select > Go to Special.
2. In the popping Go to Special dialog, check Line Differences.
3 Click OK. Now the line differences have been selected.
4. Now keep the cells selected and click Beautify > Fill Color to select a color from the drop down menu.
Note
This method compares cells without case.
4. Compare two columns and look for missing data points
Suppose there are two columns, column B is longer and column C is shorter (see screenshot below). Compared to column B, how to find out the missing data in column C?
Click here to download the sample file
4.1 Compare and find the missing data points (using the VLOOKUP or MATCH formula).
If you only want to determine which data is missing after comparing two columns, you can use one of the following formulas:
=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))
Or
=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0))))
Press Next Drag the autofill button over cell D10. If the data is in both columns B and C, the formula returns FALSE. If the data is only in column B but missing from column C, the formula returns TRUE.
Note
The above two formulas compare case-insensitive data.
4.2 Compare two columns and list the missing data below (using the INDEX formula).
If you want to list the missing data under the shorter column after comparing two columns, INDEX array formula can help you.
In the following cell of the shorter column, assuming cell is C7, enter the following formula:
=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0) )
Press Shift + Ctrl + Enter to get the first missing data, then drag the autofill handle down until the Error value #N/A is returned.
Then you can remove the error value and all the missing data has been listed under the shorter column.
Note
This formula compares cells without case.
4.3 Compare and find the missing data points and list them in another column (with a handy tool).< /strong>
If you want to do a post operation on the missing data after comparing two columns, e.g. B. want to list the missing data in another column, or fill in the missing data under the shorter column, you can try a handy tool.Select Same and Different Cells of Kutools for Excel.
1 click Kutools > Click (so that a tick appears) on the rectangle > Select Same and Different Cells.
2. In the Select Same and Different Cells dialog, do the following:
1 in Find values in In the section, select the longer column that contains the complete list.
In According to In the section, select the shorter column that is missing some data.
2) Select Every row .
3) Select Different Values.
3 Click Okto display a dialog box reminding you the number of missing data, click OK to it close. Then the missing data was selected.
Now you can press Ctrl + C keys to copy the selected missing data and paste it by pressing Ctrl + V keys under the shorter column or another new column as needed.
Note
Checking case insensitive The option in the Select Same and Different Cells dialog box compares two columns case-sensitive.
4.4 Compare two columns and list missing data below (using VBA).
Here is a macro code that can fill in the missing data under the two columns.
1. Open the used sheet and press Alt + F11 keys to open theMicrosoft Visual Basic for Applications window.
2 click Insert > Module to create a new module to insert below VBA code.
VBA: Compare two columns and fill in missing data
Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021
Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range
Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer
Dim xWs As Worksheet
On Error Resume Next
SRg:
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 as a range"
GoTo SRg
End If
Set xWs = xRg.Worksheet
xIntSC = xRg.Column
xIntEC = xRg.Columns.Count + xIntSC - 1
xIntSR = xRg.Row
xIntER = xRg.Rows.Count + xIntSR - 1
Set xRg = xRg.Columns
Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))
Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))
xIntR = 1
For Each xFRg In xRgC1
If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then
xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
xIntR = 1
For Each xFRg In xRgC2
If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then
xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
End Sub
3. Drücken Sie dann F5 Mit der Taste zum Ausführen des Codes wird ein Dialogfeld zur Auswahl der beiden Vergleichsspalten angezeigt.
4 Klicken OK. Jetzt wurden die fehlenden Daten unter den beiden Spalten aufgelistet.
Anmerkung
Der Code vergleicht Zellen ohne Groß- und Kleinschreibung.
5． Vergleichen Sie zwei Spalten (Datumsangaben), wenn sie größer oder kleiner als sind
Wenn zwei Spalten mit Datumsangaben vorhanden sind (siehe Abbildung unten), möchten Sie möglicherweise vergleichen, welches Datum später in derselben Zeile liegt.
Klicken Sie hier, um die Beispieldatei herunterzuladen
5.1 Vergleichen Sie zwei Spalten, wenn sie größer oder kleiner als sind (unter Verwendung der Formel)
Mit der einfachen Formel können Sie schnell feststellen, ob das Datum 1 in jeder Zeile nach dem Datum 2 liegt.
=IF(B2>C2,”Yes”,”No”)
Presse Weiter Drücken Sie die Taste, um das erste verglichene Ergebnis zu erhalten, und ziehen Sie dann den automatischen Füllgriff auf Zelle C6, um alle Ergebnisse zu erhalten.
Anmerkung
1. In Excel werden Datumsangaben als Zahlenreihen gespeichert, es handelt sich tatsächlich um Zahlen. Daher wenden Sie die Formel an, um Daten direkt zu vergleichen.
2. Wenn Sie vergleichen möchten, ob Datum 1 in jeder Zeile vor Datum 2 liegt, ändern Sie das Symbol > zu < in der Formel.
5.2 Vergleichen Sie zwei Spalten, wenn sie größer oder kleiner als das Format sind (unter Verwendung der bedingten Formatierung).
Wenn Sie die Zellen in Spalte Datum 1 markieren möchten, wenn sie größer als Datum 2 sind, können Sie die verwenden Bedingte Formatierung Funktion in Excel.
1. Wählen Sie die Daten in Spalte B (Datum1) aus und klicken Sie auf Verschönern Sie > Bedingte Formatierung > Neue Regel.
2. In dem Neue Formatierungsregel Dialog auswählen Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen in Wählen Sie einen Regeltyp Abschnitt, dann geben Sie Formel ein = $ B2> $ C2 in das Textfeld von Formatieren Sie Werte, bei denen diese Formel wahr ist.
Wenn Sie die Zellen in Spalte B hervorheben möchten, die kleiner als die in Spalte C sind, verwenden Sie die Formel = $ B2 <$ C2.
3 Klicken Format Taste zum Öffnen der Format Cells Wählen Sie dann den gewünschten Formattyp aus.
4 Klicken OK > OK. Dann wurden die Zellen in Spalte Datum1 hervorgehoben, die größer als die in Spalte Datum2 sind.
6． Vergleichen Sie zwei Spalten und geben Sie den Wert in einer anderen Spalte zurück
Zum Beispiel gibt es zwei Tabellen. Jetzt möchten Sie Spalte B und Spalte E vergleichen, dann die relativen Preise aus Spalte C ermitteln und in Spalte F zurückgeben.
Click here to download the sample file
6.1 Pull the exact match data (with a formula or a handy tool).
Here are some helpful formulas and a tool to solve this problem.
Formula method
In cell F2 (the cell where you want to place the returned value), use one of the following formulas:
=VLOOKUP(E2,$B$2:$C$8,2,0)
Or
=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)
Press Next key, and the first value has been found. Then drag the auto fill handle down to cell F6. All values have been extracted.
Note
1. The formulas are not case sensitive.
2. The number 2 in the formula indicates that you will find the matching values in the second column of the table array.
3. If the formulas cannot find the relative value, it returns the #N/A error value.
A handy tool method
If you get confused with formulas, you can try the handy tool – Formula Helper of Kutools for Excel, which has multiple formulas for solution to most problems in Excel. With this, you only have to select the range, but you don’t have to remember how to use the formulas.
After free installation Kutools for Excel, please do as follows:
1. Select cell F2 (the cell where you want to place the return value) and click Kutools > Formula Helper > Lookup & Reference > Search for a value in the list.
2. In the Formula Helper dialog on the rightEntering Arguments In the section, select the table array range and the search value, and specify which column you want to search for the return value.
Tip: Remember to change the absolute reference in the Lookup value field to relative, or you can just use the first one Find value.
3 Click OkWhen the first value is found, drag the auto fill handle down to cell F6.
6.2 Pull the partial match data (using the formula).
If there is a slight difference between the two compared columns (see image below), the above methods may not work.
Please choose one of the following formulas to solve this job:
=VLOOKUP(“*”&E2&”*”,$B$2:$C$8,2,0)
Or
=INDEX($B$2:$C$8,MATCH(“*”&E2&”*”,$B$2:$B$8,0),2)
Press Next key, then drag auto fill handle down to cell F5. All values were found.
Note
1. The formulas are not case sensitive.
2. The number 2 in the formula indicates that you can find the return values in the second column of the table array.
3. If the formulas cannot find the relative value, it returns the #N / A error value.
4. * in the formula is a wildcard used to specify characters or strings.
7． Compare two columns and count matches or differences
The following dataset provides an example of comparing and counting matches or differences.
Click here to download the sample file
7.1 Compare two columns and count matches (using the SUMPRODUCT formula).
The SUMPRODUCT formula can quickly count the matches in two columns.
=SUMPRODUCT(–(ISNUMBER(MATCH(B2:B8,C2:C8,0))))
Press Next key to get the result.
Note
The formula counts cells without case.
7.2 Compare two columns and count matches or differences (with a handy tool).
If you want to count matches or differences between two columns, the handy tool is – Select Same and Different Cells.
After free installation Kutools for Excel, please do as follows:
1 click Kutools > Click (so that a tick appears) on the rectangle > Select Same and Different Cells.
2. In the Select Same & Difference Cells In the dialog box, select the two column ranges Find values in and According to sections separately, then select Each row, and select Same Values or Different Values option as required.
3 Click Ok. A dialog box displays how many matching or different cells are selected.
Matching cells
Different cells
8． Compare two columns using the placeholder
Suppose here is a list of data in column B and you want to count the cells that contain “Apple” or “Candy” in column D as below screenshot shown:
Click here to download the sample file
To count whether a cell contains one or more values, you can use a formula with wildcards to solve this problem.
=SUM(COUNTIF(B2,”*” & $D$2:$D$3 & “*”))
Press Shift + Ctrl + Enter to perform the first check, then drag the auto-fill handle down to cell F8.
If the associated cell contains one or more values in column D, the result will display a number greater than 0. If it does not contain a value in column D, 0 is returned.
If you want to count the total number of cells that contain the values in column D, use the following formula in cell F8:
Note
1. You can also use the formula to count if the cell contains values in another column
=SUMPRODUCT(COUNTIF(B2,”*” &$D$2:$D$3& “*”))
This formula just needs to press the Next button and then drag the auto-fill handle.
2. In the formulas, * is the wildcard that matches any character or string.
9. Delete differences or matches
If you want to clear the differences or matches after comparing two columns as below screenshot shown:
Click here to download the sample file
You can use the formula to find the difference or matches first:
=IF(ISERROR(MATCH(B2,$D$2:$D$8,0)),”Difference”,”Match”)
Press Next key and drag auto fill handle down to cell D8.
Then apply the Filter function in Excel to filter out the differences or match the value.
Select the formula column and click Dates and Numbers > Filters.
Then the filter button will appear in column C, click the filter button to expand the drop-down menu and select the difference or match as you need it. Then click OK to exit.
Now only the differences or matches have been filtered out. You can select them and press Delete key to remove them.
Now click Dates and Numbers > Filter again to clear the filter.
Remove the formula column when you no longer need it.
10. Compare two columns and calculate the percent change between
There are two columns, one contains the original prices and the other contains the sales prices. Now, this part introduces a formula to compare these two columns and calculate the percent change between the two columns.
Click here to download the sample file
You can use the following formula to find the percentage change between the two prices on the same row.
=(C2-B2)/B2
Press Next Press the key to get a number, then drag the auto fill handle down to cell D7.
Then format the formula result as a percentage. Select the results and click Beautify tab and go to Number group to click Percent Style.
The formula results have been formatted as percentages.
11. Compare two areas
Now you know how to compare two columns after reading above methods. However, in some cases you may want to compare two ranges (two rows with multiple columns). You can use the above methods (formulas or conditional formatting) to compare them column by column. However, here will introduce a handy tool: Kutools for Excel can quickly solve this job with formula-free in various cases.
Click here to download the sample file
11.1 Compare two ranges by cell
Here are two ranges of cells that need to be compared. You can use the Select Same And Different Cells utility of Kutools for Excel to deal with it.
After free installation Kutools for Excel, please do as follows:
1 click Kutools > Click (so that a tick appears) on the rectangle > Select Same and Different Cells.
2. In the popping Select Same and Different Cells dialog, do as follows:
1) In the Find values in section, after comparing two ranges, select the range that you want to find the matches or differences.
2) In the According section, select the other range that will be used to compare the range.
3 in Based on In sectionSelect Single Cell.
4) Then in the FIND section, select the type of cell you want to select or highlight.
5) In the Results processing section, you can highlight the cells by filling in the background color or the font color. If you don’t need highlighting, leave the checkboxes unchecked.
3 Click Ok. A dialog box will pop up reminding you how many cells/rows have been selected. Click OK to close it. Now the cells that are different from those in the other pane have been selected and highlighted.
Mark the same values
Note
If you want to compare two ranges line by line, you can also use theChoose Same and Different Cells function, but in this case choose the Each row .
11.2 Compare two ranges when the data is in the same order
If you want to compare two ranges row by row, the Compare Cells feature of Kutools for Excel can help you.
After free installation Kutools for Excel, please do as follows:
Suppose the range F2:H7 is a model. Now you want to find out if the data in the range B2:D7 is in the right order according to the range F2:H7.
1 click Kutools > Compare Cells.
2. In the Compare Cells dialog, set as follows:
1) Select the two ranges in the Find values in and According to boxes separately.
2) Select the cell type you want to highlight in the FIND section.
3) Select the highlighting type in theProcessing results section.
3 Click Ok. A dialog box will appear reminding you how many cells have been selected. Click OK to close it. Now the cells that are different from those in the other pane have been selected and highlighted.
See more information related to the topic compare columns in excel
Compare Two Columns in Excel (for Matches & Differences)
- Author: TrumpExcel
- Post date: 2019-10-19
- Ratings: 4 ⭐ ( 3694 Ratings )
- Match search results: In this video, I will show you six examples to compare two columns in Excel for matches and differences.
——————————————————————————————————————————————
00:00 Intro
00: 10 Compare two columns based on row values
01:04 Compare and highlight values that are in both the columns (using conditional formatting)
01:42 Compare and highlight values that are in one column and not in the other column
02:17 Find names that are in column 1 and not in column 2 (using VLOOKUP)
04:05 Lookup and get matching data point
04:37 Pull matching data point when there is a partial match
——————————————————————————————————————————————Based on your dataset, you may need to change or adjust the method. However, the basic principles would remain the same.
You can read more about this here: https://trumpexcel.com/compare-two-columns/
Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel
This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel.
It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.
You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/
Free Excel Course – https://trumpexcel.com/learn-excel/
Paid Online Training – https://trumpexcel.com/excel-training/
Best Excel Books: https://trumpexcel.com/best-excel-books/⚙️ Gear I Recommend:
Camera – https://amzn.to/3bmHko7
Screen Recorder – techsmith.z6rjha.net/26D9Q
USB Mic – https://amzn.to/2uzhVHd
Wireless Mic: https://amzn.to/3blQ8uk
Lighting – https://amzn.to/2uxOxRvSubscribe to get awesome Excel Tips every week: https://www.youtube.com/user/trumpexcel?sub_confirmation=1
Note: Some of these links here are affiliate links!
Excel ExcelTips ExcelTutorial
How to Compare Two Columns in Excel
- Author: spreadsheeto.com
- Ratings: 3 ⭐ ( 6574 Ratings )
- Match search results: Learn how to easily compare 2 columns in Excel. You also learn how to make a list and highlight the differences between the columns. Read now!
How to Compare Two Columns in Excel (for matches & differences)
- Author: trumpexcel.com
- Ratings: 4 ⭐ ( 7396 Ratings )
- Match search results: In this tutorial, I’ll show you various ways to compare two columns in Excel. The techniques shown can be used to find/highlight matches and differences.
How to Compare Two Columns in Excel for Matches and Differences?
- Author: testbook.com
- Ratings: 3 ⭐ ( 8435 Ratings )
- Match search results: Compare two columns in MS Excel for Matches and Differences. Compare rows, check for duplicate values or find missing data with conditional formatting.
How to Compare Three Excel Columns
- Author: basicexceltutorial.com
- Ratings: 4 ⭐ ( 1121 Ratings )
- Match search results:
How to Compare Two Columns in Excel For Finding Differences
- Author: www.exceldemy.com
- Ratings: 4 ⭐ ( 1827 Ratings )
- Match search results: There are so many processes in which Excel compare two lists & return differences. So, let’s learn the ways on how to compare two columns in Excel for differences.
Compare Two Columns in Excel
- Author: www.wallstreetmojo.com
- Ratings: 3 ⭐ ( 3344 Ratings )
- Match search results: Guide to compare two columns in excel. We discuss the top 4 methods – simple, IF, Exact function & Conditional Formatting with examples.
See more articles in this category: Computer tips