How To Find Duplicates In 2 Columns In Excel

How to find indistinguishable values in two columns in Excel?

When we use a worksheet, sometimes we demand to compare two columns and find the same values. For case, I take two long columns of student names, and now, I want to compare them and discover out the same names. If I compare them cell by prison cell, it will be tiresome and waste a lot of fourth dimension. Are there any quick ways to terminate this chore?

Observe duplicate values in two columns with Formula

Discover duplicate values in two columns with VBA code

Quickly find indistinguishable values in two columns with Kutools for Excel

Chop-chop find duplicate values in one column with Kutools for Excel

Find duplicate values in two columns with Formula

Look at the following screenshot, I accept cavalcade A (A2:A15) and cavalcade C (C2:C13) which incorporate some duplicate names.

doc find duplicates 1

By using a formula, we can compare two columns and display the duplicate values. Delight do with the following steps:

1. In cell B2, input this formula: =IF(ISERROR(Friction match(A2,$C$2:$C$13,0)),"",A2) .

doc find duplicates 2

Note: In the higher up formula: A2 is the column which you want to be compared. $C$2:$C$13 is the range that you desire to be compared with. You can change the variables for what yous are using.

two. Press the Enter key. Select jail cell B2, and and then drag the make full handle over jail cell B15. And all of the duplicate names will be displayed in column B. See screenshot:

doc find duplicates 3

Easily Combine columns or rows without losing data in Excel

With Kutools for Excel's Combine Columns and Rows utility, you lot can quickly combine multuple cells based on row/column and separated by comma or space, or combine a range of cells into a single prison cell with specific separator.
For instance, hither I practise thses operations: (1.)Select Select Combine columns under To combine selected cells according to following options; (2.)Specify a separator for the combined data, here I select Infinite; (iii.))Specify the cell you desire to place your combined result; (iv.)Specify how y'all want to bargain with the combined cells. Click for full-featured gratuitous trial in 30 days!

Kutools for Excel: with more than 300 handy Excel add together-ins, complimentary to try with no limitation in 30 days.

Find duplicate values in ii columns with VBA code

The following VBA lawmaking can also help you to notice out the indistinguishable values between ii columns.

1. Click Programmer > Visual Basic, a new Microsoft Visual Basic for applications window volition be displayed, click Insert > Module, and input the following code into the Module:

VBA code: discover duplicates from two columns

          Sub Compare() Dim Range1 Equally Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng Equally Range xTitleId = "KutoolsforExcel" Set Range1 = Application.Pick Fix Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8) Application.ScreenUpdating = Fake For Each Rng1 In Range1     xValue = Rng1.Value     For Each Rng2 In Range2         If xValue = Rng2.Value Then             If outRng Is Nothing And then                 Set outRng = Rng1             Else                 Set up outRng = Application.Union(outRng, Rng1)             End If         End If     Next Side by side outRng.Select Awarding.ScreenUpdating = True End Sub

2. Click push to execute the lawmaking, in the popped out box select Range i that y'all desire to compare.

doc find duplicates 4

iii. Click OK and go along selecting Range 2 that you lot will be compared with.

doc find duplicates 5

four. And so click OK, and the duplicate values both in column A and cavalcade C have been selected in column A.

doc find duplicates 6

Notation: If you want to select the duplicates from column C, yous just demand to swap the ii selected columns.

Chop-chop find duplicate values in two columns with Kutools for Excel

With the assistance of Kutools for Excel, you can detect the duplicate values speedily and comfortably.

After free installing Kutools for Excel, delight do every bit below:

Click Kutools > Select > Select Aforementioned & Different Cells .

doc find duplicates 10

1. In the Select Same & Different Cells dialog box, do the following operations:

(one.) Click the outset button to select the source column to exist compared, and so click the second button to select the column you are compared with;

(2.) Choose Each row under the Based on section;

(3.) And then select Same Values fromt he Find section;

(4.) At last, yous can choose 1 background or font colour to highlight the aforementioned cells nether the Processing of resuts section as you demand.

doc find duplicates 8

ii. So click OK, and the same values both in cavalcade A and column B take been selected in column A.

doc find duplicates 9

With this utility, you can too discover the different values betwixt 2 columns.

Notes:

My data has headers : If the information yous are compared has headers, you can cheque this option, and the headers volition not be compared.
Select entire rows : With this pick, the entire rows which comprise the same values will be selected.
Case Sensitive : Checking this choice, you will compare the columns with case sensitive.
The 2 comparing ranges must contain the same number of columns.
If you want to select the duplicates from cavalcade C, yous just need to bandy the two selected columns.

Demo: Speedily find and highlight duplicate values in ii columns

Quickly observe duplicate values in one column with Kutools for Excel

After installing Kutools for Excel, please do equally below: （Free Download Kutools for Excel At present!)

Sometimes, you may desire to find the duplicate values in a column, the Kutools for Excel also can assistance you quickly handle it.

one. Select the column y'all desire to discover duplicate values, click Kutools > Select > Select Duplicate & Unique Cells .
doc find duplicates 11

2. In the Select Duplicate & Unique Cells dialog, check Duplicates or All duplicates selection as you need, and at the same time, you lot can decide to highlight the duplicates with background or font color or non in Processing of results department.
doc find duplicates 12

3. Click Ok, a dialog pops out to remind you the number of selected duplicate cells, click OK to close it, and the duplicates are selected and highlighted.
doc find duplicates 13

Related Article:

How to find unique values between two columns in excel?

The All-time Office Productivity Tools

Kutools for Excel Solves Nigh of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you lot have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (hands edit multiple lines of text and formula); Reading Layout (easily read and edit big numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Information; Divide Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Bare Rows (all cells are empty); Super Discover and Fuzzy Detect in Many Workbooks; Random Select...
Exact Copy Multiple Cells without irresolute formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add together Text, Remove past Position, Remove Space; Create and Print Paging Subtotals; Catechumen Between Cells Content and Comments...
Super Filter (save and utilise filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by assuming, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Piece of cake deploying in your enterprise or organization. Total features 30-day free trial. 60-twenty-four hour period money back guarantee.

kte tab 201905

Role Tab Brings Tabbed interface to Function, and Make Your Work Much Easier

Enable tabbed editing and reading in Give-and-take, Excel, PowerPoint , Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity past 50%, and reduces hundreds of mouse clicks for you every day!

officetab bottom