How To Find Duplicate Entries 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 example, I have two long columns of educatee names, and now, I desire to compare them and find out the same names. If I compare them cell by jail cell, it will exist boring and waste matter a lot of fourth dimension. Are there whatever quick ways to cease this task?

Find duplicate values in ii columns with Formula

Find duplicate values in two columns with VBA code

Quickly find indistinguishable values in 2 columns with Kutools for Excel

Quickly discover duplicate values in one column with Kutools for Excel

Discover duplicate values in two columns with Formula

Expect at the following screenshot, I take cavalcade A (A2:A15) and column C (C2:C13) which contain some duplicate names.

doc find duplicates 1

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

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

doc find duplicates 2

Annotation: In the above formula: A2 is the column which you want to be compared. $C$2:$C$13 is the range that you want to be compared with. You lot tin can change the variables for what y'all are using.

2. Press the Enter cardinal. Select cell B2, and then drag the fill handle over cell B15. And all of the indistinguishable names will exist displayed in cavalcade B. See screenshot:

doc find duplicates 3

Easily Combine columns or rows without losing data in Excel

With Kutools for Excel'due south Combine Columns and Rows utility, you lot tin can quickly combine multuple cells based on row/column and separated past comma or space, or combine a range of cells into a single jail cell with specific separator.
For case, here I do thses operations: (one.)Select Select Combine columns under To combine selected cells according to post-obit options; (2.)Specify a separator for the combined information, here I select Infinite; (3.))Specify the prison cell you desire to place your combined consequence; (4.)Specify how you want to deal with the combined cells. Click for full-featured gratis trial in thirty days!

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

Find duplicate values in 2 columns with VBA code

The following VBA code can too aid y'all to find out the duplicate values between two columns.

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

VBA code: observe duplicates from two columns

          Sub Compare() Dim Range1 As Range, Range2 Every bit Range, Rng1 As Range, Rng2 As Range, outRng As Range xTitleId = "KutoolsforExcel" Set Range1 = Application.Selection Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8) Set Range2 = Awarding.InputBox("Range2:", xTitleId, Type:=eight) Awarding.ScreenUpdating = False For Each Rng1 In Range1     xValue = Rng1.Value     For Each Rng2 In Range2         If xValue = Rng2.Value Then             If outRng Is Nix And so                 Gear up outRng = Rng1             Else                 Fix outRng = Application.Union(outRng, Rng1)             Cease If         End If     Adjacent Next outRng.Select Application.ScreenUpdating = True Finish Sub

2. Click button to execute the code, in the popped out box select Range 1 that you want to compare.

doc find duplicates 4

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

doc find duplicates 5

4. Then click OK, and the indistinguishable values both in column A and cavalcade C have been selected in column A.

doc find duplicates 6

Note: If you want to select the duplicates from column C, you lot simply demand to bandy the two selected columns.

Speedily find indistinguishable values in two columns with Kutools for Excel

With the help of Kutools for Excel, you can discover the duplicate values quickly and comfortably.

Later on gratuitous installing Kutools for Excel, please practice as beneath:

Click Kutools > Select > Select Aforementioned & Different Cells .

doc find duplicates 10

ane. In the Select Aforementioned & Different Cells dialog box, practise the following operations:

(i.) Click the first button to select the source cavalcade to be compared, then click the second push button to select the column you are compared with;

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

(3.) And then select Aforementioned Values fromt he Observe section;

(4.) At concluding, you can cull ane background or font color to highlight the same cells under the Processing of resuts section as you need.

doc find duplicates 8

2. And 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 also find the different values between two columns.

Notes:

My data has headers : If the data you are compared has headers, yous can cheque this option, and the headers will not exist compared.
Select unabridged rows : With this selection, the entire rows which contain the same values will be selected.
Instance Sensitive : Checking this option, y'all will compare the columns with case sensitive.
The ii comparing ranges must contain the aforementioned number of columns.
If you want to select the duplicates from column C, you lot only need to swap the two selected columns.

Demo: Quickly notice and highlight duplicate values in 2 columns

Quickly find duplicate values in one column with Kutools for Excel

After installing Kutools for Excel, please practice every bit beneath: （Complimentary Download Kutools for Excel Now!)

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

1. Select the column y'all want to notice indistinguishable 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 choice as you lot need, and at the same time, y'all tin decide to highlight the duplicates with groundwork or font colour or not in Processing of results section.
doc find duplicates 12

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

Related Commodity:

How to find unique values between 2 columns in excel?

The Best Office Productivity Tools

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

Reuse: Apace insert complex formulas, charts and anything that you lot have used earlier; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily 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 Data; Split up Cells Content; Combine Duplicate Rows/Columns... Prevent Indistinguishable Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Bare Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Verbal Copy Multiple Cells without irresolute formula reference; Machine Create References to Multiple Sheets; Insert Bullets, Bank check Boxes and more than...
Excerpt Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (relieve and apply filter schemes to other sheets); Avant-garde Sort by month/calendar week/day, frequency and more than; Special Filter by bold, 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 Function/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

kte tab 201905

Office Tab Brings Tabbed interface to Part, and Make Your Piece of work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Admission, Visio and Projection.
Open up and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for y'all every mean solar day!

officetab bottom