Sep
5

How to compare two columns in Excel and delete duplicates (highlight, color, move)

It will take you about 10 minutes to read this article and in the next 5 minutes (or even faster if you choose the 2nd solution described in the article) you will easily compare two Excel columns for duplicates and remove or highlight the found dupes. Okay, the countdown is started!

Compare two columns and find duplicates using Excel formulas

Excel is a very powerful and really cool application for creating and processing large arrays of data. Now that you have lots of workbooks with a pool of data, or maybe just one huge table, you may want to compare 2 columns for duplicates and then do something with found entries, for example delete duplicate rows, color dupes or clear the contents of duplicated cells. These two columns may be located in one table, contiguously or non-contiguously, or they may reside in 2 different worksheets or even workbooks.

Say, you have 2 columns with people names - 5 names in column A and 3 names in column B, and you want to compare data between these two columns to find duplicates. As you understand, this is bogus data just for a quick example; in real worksheets you usually have thousands and tens of thousands of entries.

Variant A: Both columns are located on one sheet, in a single table: Column А and Column B
Both columns are located on one sheet

Variant B: Two columns are located on different sheets: Column A in Sheet2 and Column A in Sheet3
Two columns are located on different sheets

The built-in Remove Duplicate tool available in Excel 2013, Excel 2010 and 2007 cannot handle this scenario because it cannot compare data between 2 columns. Furthermore, it can only remove dupes, no other choice such as highlighting or coloring is available, alas :-(.

Further on, I am going to describe 2 possible ways of comparing two Excel columns that let you find and remove duplicate entries:

Compare 2 columns to find duplicates using Excel formulas

Variant A: both columns are on the same list

  1. In the first empty cell, in our example this is Cell C1, write the following formula:

    =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

    Excel formula to compare data between 2 columns and find duplicate and unique entries

    In our formula, A1 is the first cell of the first column that we want to use for comparison. $B$1 and $B$10000 are the addresses of the first and the last cell of the 2nd column that you want to compare against.Pay attention to the absolute cell reference - dollar signs ($) preceding the column letters and row numbers. I use the absolute reference on purpose, in order for the cell addresses to remain unchanged when copying the formula.

    If you want to find dupes in Column B, swap the column names so that the formula looks like this:
    =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

    Instead of "Unique"/"Duplicate" you can write your own labels, e.g. "Not found"/"Found", or leave only "Duplicate" and type "" instead of "Unique". In the latter case, you will have empty cells next to cells for which duplicates were not found, I believe such presentation is more convenient for data analysis.

  2. Now let's copy the formula to all cells of column C, up to the last row that contains data in column A. To do this, put the cursor to the lower right corner of cell С1, and the cursor will change to a black cross, as shown in the image below:
    Put the cursor to the lower right corner of the cell where you have written the formula

    Click the left mouse button and holding it down drag the border downward selecting all cells where you want to copy the formula. When all needed cells are selected, release the left mouse button:
    Copy the formula to other cells

    Tip: In large tables, it is faster to copy the formula using shortcuts. Click on cell C1 to select it and press Ctrl + C (to copy the formula to clipboard), then press Ctrl + Shift + End (to select all non-empty cells in Column C), and finally hit Ctrl + V (to paste the formula into all selected cells).
  3. Awesome, all duplicated cells are flagged as "Duplicate":
    All duplicated cells are flagged as 'Duplicate'

Variant B: two columns are on different worksheets (workbooks)

  1. In the 1st cell of the 1st empty column in Sheet2 (column B in our case), write the formula:

    =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

    Where Sheet3 is the name of the sheet on which the 2nd column is located, and $A$1:$A$10000 are the addresses of the first and last cells of that 2nd column.

  2. Similar to Variant A.
  3. We have the following result:
    Duplicated cells are flagged as Duplicate

Click to download the worksheet with the above examples and the formula to compare 2 columns to find duplicates.

Working with found duplicates

Perfect, we have found the entries in the first column (Column A) that also exist in the second column (Column B). Now we need to do something with them :)

It would be rather ineffective and would take too much time to look through the entire table and review the duplicate entries manually. There are much better ways.

Show only duplicated rows in Column A

If your columns do not have headers, you need to add them. To do this, put the cursor on the number indicating the 1st row and it will change to a black arrow as shown in the screenshot:
The cursor changes to a black arrow indicating that the entire row is selected.

Right click the selected row and choose "Insert" from the context menu:
Insert a new row

Give names to your columns, e.g. "Name" and "Duplicate?". Then switch to the Data tab and click Filter:
Click 'Filter' on the Data tab in the Sort and Filter group

After that click a tiny grey arrow next to "Duplicate?" to open a drop down list, uncheck all items other than Duplicate in that list, and click OK:
Leave only 'Duplicate' checked in the drop-down list

That's it, now you see only those cells of Column A that have duplicated values in Colum B. There are only three such cells in our test worksheet, as you understand in real sheets there are likely to be more, far more of them:
Only duplicated entries are displayed

In order to display all rows of Column A again, click the filter symbol in Column B that now looks like a funnel with a tiny arrow and check "Select all". Alternatively, you can do the same via Data tab -> Select & Filter -> Clear, as shown in the screenshot:

Clearing the filter

Color or highlight found duplicates

If the "Duplicate" flag does not suffice for your purposes and you want to mark duplicated cells by font color or fill color or in some other way…

Then filter the duplicates as explained above, select all filtered cells and press Ctrl + F1 to open the Format Cells dialog box. As an example, let's change the background color of duplicated rows to bright yellow. Of course, you can change the background color of cells using the Fill color option on the Home tab, but the advantage of the Format Cells dialog box is that it lets you make all formatting changes at a time:
The Format Cells dialog box

Now you definitely won't miss a single duplicated cell:
The background of duplicated cells is changed to yellow

Remove duplicates from the first column

Filter your table so that only cells with duplicated values show up, and select all those cells.

If 2 columns you are comparing are located on different worksheets, i.e. in separate tables, right-click the selected range and choose "Delete Row" from the context menu:
Deleting duplicate rows

Click OK when Excel will ask you to confirm that you really want to "Delete entire sheet row" and then clear the filter. As you can see, only the rows with unique values are left:
Only the rows with unique values are displayed

If 2 columns are located on one worksheet, next to each other (adjacent) or not touching each other (nonadjacent), the removing duplicates is a bit more complex. We cannot delete entire rows that contain duplicate values because this would delete corresponding cells in the 2nd column too. So, in order to leave only unique entries in Column A, you do the following:

  1. Filter the table so that only duplicated cells are displayed and select all those cells. Right click the selection and choose "Clear contents":
    Excel - clear contents
  2. Clear the filter.
  3. Select all cells in Column A starting from cell A1 up to the last cell that contains data.
  4. Go to the Data tab and click Sort A to Z. In the dialog window that opens, choose "Continue with the current selection" and click Sort:
    Sort only column A
  5. Delete the column containing the formula because you do not need it any longer, only "Uniques" are left there by now.
  6. That's all, now Column A contains only unique data that do not exist in Column B:
    All duplicates are removed

As you see, it's not so difficult to remove duplicates between two Excel columns using formulas. Though it's very time-consuming and boring process to write and copy the formula, apply and clear the filter every time you need to compare 2 columns in your worksheets. The other solution I am going to bring to your attention is much simpler and will take just a fraction of time we've spent on the first method. I believe you will find more pleasant things to spend the saved time on ;)

Compare 2 Excel columns for duplicates using a visual wizard

Here are 10 quick and easy steps to compare two columns in your Excel worksheet to find duplicates:

  1. Download the Duplicate Remover add-in for Excel.
  2. Close all open Excel windows, extract the files from a zip folder, run setup.exe and the Setup Wizard will walk you through the installation:
    Install the Duplicate Remover add-in
  3. Open the worksheet (or worksheets) where the columns you want to compare are located.
  4. Select any cell within the 1st column, switch to the newly added Ablebits Data tab and click the "Compare Two Tables" button to start the wizard:
     Click the Compare Two Tables button to start the wizard
  5. On step 1 of the wizard, you will see that your first column is already selected, so simply click Next.
    Note. If you want to compare not simply 2 columns, but 2 tables by two or more matching columns, you need to select the entire first table on this step.
  6. On step 2 of the wizard, select the 2nd column that you want to compare against, we choose Sheet3 from the list of sheets. The smart wizard will select the 2nd column automatically, if for some reason this does not happen, click on the Select range icon. (If you are comparing entire tables, select the whole 2nd table):
    Select the 2nd column that you want to compare against
  7. Choose to Find Duplicate values:
     Choose to find duplicate values
  8. Select the pairs of columns you want to compare. Check the checkbook next to Column A and select Column A from the drop-down list in the right-hand part of the wizard, under Table 2 columns:
    Choose matching columns that you want to compare

    Tip. You can compare not only 2 columns but 2 tables with multiple columns. At that, you can compare by several pairs of columns at the same time, e.g. Column A in the 1st table against Column A in the 2nd table, and Column B in the 1st table against Column D in the 2nd table, etc. For more details, please see How to remove duplicates from two Excel spreadsheets.
  9. And finally, you are to decide what you want to do with found dupes. You can choose to delete the duplicate entries, move or copy them to another worksheet, add a status column (the result will be similar to our first solution with Excel formulas), highlight duplicates, or simply select all cells with duplicated values:
    Choose what to do with found duplicates

    Tip. Do not choose to delete duplicates especially if you are using the wizard for the first time. You'd better choose to move dupes to another worksheet, because when you work with real data you may want to cast at least a quick glance at the entries recognized as duplicates, especially if you are comparing by several columns in large tables. It may happen that you accidentally forgot to select a certain column when specifying matching columns, and if that column contained unique data, it would be really frustrating to irretrievably lose it.
  10. Click Finish and enjoy the result. What we have now is a nice, clean table with no duplicates:
    All duplicates were removed by the Duplicate Remover add-in

Remember the previous solution and feel the difference :) It is fast and easy indeed to dedupe your worksheets using the Duplicate Remover add-in. In fact, it will take you less time than you've spent on reading this article. The clock is ticking, so hurry up and download Excel Duplicate Remover right now!

If you have questions or something is left unclear, please drop me a comment and I'll happily elaborate further. Thank you for reading!

54 Responses to "How to compare two columns in Excel and delete duplicates (highlight, color, move)"

  1. srikanth says:

    Awesome, very helpful.
    Thanks for posting.

  2. Mani says:

    Ur post is superb. Thanks for doing it and educating us.

  3. Pawan Mall says:

    Thanks for sharing it. It is a great help to me in fulfill a project which is based on excel sheet. Thanks once again.

  4. Sandeep says:

    Thanks this puts me in safe hands of the excel

  5. Nick says:

    Big Thankyou for the clear and immensly helpful tips. Also thanks to Google for searching your link for me :)

  6. Malene says:

    Thank you so much... saved me hours of work.
    Malene

  7. Ritu says:

    Thanku so much , Really save lot of work and efforts..

  8. Paul says:

    This is a great well written guide. Thanks for the help.

  9. Ramakrishnan says:

    Really this article is very useful one.

  10. Anu says:

    Thanks for sharing. Its very useful :)

  11. Ravi says:

    Thanks for sharing.

  12. Ritu Solanki says:

    To the point information, very nice, thx for sharing this information.

  13. Ryan Johnsen says:

    Thanks for the help. This worked like a charm!!!

  14. Alex T says:

    I can't believe you taught me how to use Excel. Usually it seems so opaque that I give up before trying.

    Thanks!
    Alex

  15. Mohamed says:

    Awesome, just as you said took few min to read the article and my job was completed in less than a min.

  16. Compiler says:

    You just saved me a ton of stress.

    I really really appreciate you for this.

    From Nigeria, Regards!

  17. Walaki says:

    First of all, thanks for the article, but...
    This formula doesn't work for me:

    =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

  18. Walaki says:

    It highlights the B1,$A$1 part.

  19. Heather says:

    oh thank god for this article. Can't believe how hard it is to find info online on comparing two columns and deduping.

  20. Walaki says:

    Ok, now this is the working one:

    =IF(ISERROR(MATCH(B1;$A$1:$A$10000;0));”Unique”;”Duplicate”)

  21. Augusta says:

    Merely wanted to mention I am lucky that i stumbled upon your site.

  22. sakthi says:

    How can i find same values in same coloumn i inserted. (i.e)if i create one column for customer phone number, in tha same column if enter one number again how can i find that?

  23. Parijat Luthra says:

    How do i find duplicate value of mixed up alphabets say

    abc ( Dup)
    cba (Dup )
    def
    ghi
    bcd
    bca (DUp )

  24. Shubha Vishwanath says:

    Hi Alex,

    Thank you so much for sharing this. It really helped me so much.

    Thanks

  25. Robert Hicks says:

    Hello Alex,

    Thank you for this guide. Using the formulas, is it possible to after comparing the two columns and identifying that the value is unique, instead of returning a text value of "unique" or "duplicate" to return the unique value itself?

  26. Saravanan RK says:

    Hi,
    I want compare two sheets data(ALL Columns) into another sheet. I don't know how it is possible please can anyone solve it.

    Sheet1:

    ID SALARY CLEAVE SLEAVE
    12 1000 8 5
    11 2000 5 6
    10 1500 4 7

    Sheet2:

    ID SALARY CLEAVE SLEAVE
    12 1000 8 5
    11 2000 9 6
    10 2500 4 7

    I want the result like this given below,If both of the sheet have same data that will not display into the result sheet otherwise if there any difference.

    Sheet 3:RESUT
    RESULT
    11 2000 5 6
    11 2000 9 6

    I want to the result like above.

  27. srinivasarao says:

    Dear All,

    Could you please help me below scenario :

    I need to compare data from first sheet & second sheet and get data column A from first sheet .. for example :
    First Sheet :
    Original Search key & Code Id

    Second Sheet :
    Old Search key & Code Id

    I need to compare First & Second sheet Code Id's and return Original Search key in second sheet column "C".

    I tried VLOOPUP .. I am getting "#NA" value

    Regards,
    SSRAO

    • Alexander says:

      I am sorry, it is difficult to recommend you something without seeing your data.

      For us to be able to assist you better, please post a small sample workbook with your data and formula on our forums.
      We will look into the issue and try to help.

  28. Angel says:

    Great...Really helped

  29. Hamid says:

    Thank you millions. Helped me a lot

  30. Ranjith J says:

    Great! Helped me :)

  31. anshul says:

    Great Help.... Thanks a Lot

  32. Pat says:

    Thanks a lot buddy. Cheers

  33. Saman says:

    how delete even numbers in raw in excel such as:

    A B C
    1
    2
    3
    4
    1001
    1002
    .
    .

  34. Robert says:

    these formulas are very helpful, but does anyone have a way to find duplicates in two columns when the data isn't an exact match? I'm comparing ISBN's, and column a contains multiple values in each cell. Column b contains one value (ISBN) per cell. So, I really need to find cells in column a that partially match the value of cells in column b. Make sense?

  35. Rj says:

    hey what is the process for the same issue in excel 2007 ?

  36. Roshan says:

    Thanks for explanation

  37. sabu says:

    Hi,
    I have two columns as below. Would like to know how I can get the unique data from these two columns in a third column. I have provided below the sample data for merging and the expected data as well. Sample data is in Column A & B while unique data should be in Column C.

    Below is the data

    Column A
    Apple
    Orange
    Banana
    Strawberry
    Grapes

    Column B
    Plums
    Apple
    Banana
    Grapes
    Blueberry

    Iam looking at the below data in COlumn C

    Column C
    Apple
    Orange
    Banana
    Strawberry
    Grapes
    Plums
    Blueberry

    Would be good if you could share me the excel formula for this.

  38. Josh says:

    So incredibly helpful! Thank you!

  39. Jane says:

    You are amazing! I've been looking for this information for a while now, so a huge thank you!!! :-)

  40. needed help says:

    Thanks.. it served my purpose.

  41. Kumar Shashi says:

    Thank you so much for posting this info.

  42. Michael says:

    Thank you for posting! Great formula and thinking!

  43. Emmanuel says:

    How do I flag out duplicate data in multiple columns instead of just two columns?

  44. Monica says:

    Thank you!!!You are a Champ!

  45. Co says:

    Just what I needed. Thanks

  46. Julie says:

    Exactly what I needed. I tried so many other formulas and wasted so much time.
    I simply copied and pasted and it worked!
    Thank you!

  47. sunil says:

    When I try to find the question mark character (?) and hidden space in an Excel sheet, Excel cannot locate the cells containing them. How can I search for ? in Excel?

  48. Ajay says:

    Really helpful, thank you so much

  49. Naresh says:

    How To remove both duplicate ?

  50. joe says:

    Doesnt work. Identifies 3 Duplicates and all the rest it claims are unique.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard