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

Comments page 2. Total comments: 74

1. 

   Kumar Shashi says:
   2016-09-22 at 12:05 pm

   Thank you so much for posting this info.

2. 

   needed help says:
   2016-07-13 at 10:44 am

   Thanks.. it served my purpose.

3. 

   Jane says:
   2016-04-23 at 12:57 pm

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

4. 

   Josh says:
   2016-03-10 at 8:48 pm

   So incredibly helpful! Thank you!

5. 

   sabu says:
   2016-03-07 at 6:00 pm

   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.

6. 

   Roshan says:
   2016-01-19 at 1:23 pm

   Thanks for explanation

7. 

   Rj says:
   2015-12-16 at 6:52 am

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

8. 

   Robert says:
   2015-11-24 at 6:25 pm

   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?

9. 

   Saman says:
   2015-08-13 at 9:24 pm

   how delete even numbers in raw in excel such as:

   A B C
   1
   2
   3
   4
   1001
   1002
   .
   .

10. 

    Pat says:
    2015-06-04 at 3:26 am

    Thanks a lot buddy. Cheers

11. 

    anshul says:
    2015-05-08 at 2:37 am

    Great Help.... Thanks a Lot

12. 

    Ranjith J says:
    2015-04-15 at 1:54 pm

    Great! Helped me :)

13. 

    Hamid says:
    2015-03-27 at 8:40 pm

    Thank you millions. Helped me a lot

14. 

    Angel says:
    2015-02-28 at 3:09 pm

    Great...Really helped

15. 

    srinivasarao says:
    2015-02-08 at 2:22 pm

    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

    1. 

       Alexander says:
       2015-02-10 at 1:46 pm

       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.

16. 

    Saravanan RK says:
    2015-01-09 at 7:03 am

    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.

17. 

    Robert Hicks says:
    2014-12-31 at 6:47 pm

    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?

18. 

    Shubha Vishwanath says:
    2014-12-08 at 2:23 pm

    Hi Alex,

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

    Thanks

19. 

    Parijat Luthra says:
    2014-10-26 at 1:05 pm

    How do i find duplicate value of mixed up alphabets say

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

    1. 

       Alexander says:
       2014-10-28 at 1:16 pm

       You need a special macro to fulfill your task. You can ask for it in the VBA sections on http://www.mrexcel.com or http://www.excelforum.com

20. 

    sakthi says:
    2014-09-24 at 10:34 am

    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?

    1. 

       Alexander says:
       2014-10-06 at 4:22 pm

       I am sorry for a delay in response. Please have a look at the variant described in this blog post, it may help:
       https://www.ablebits.com/office-addins-blog/prevent-duplicates-excel-column/

       If you want to quickly find the previous occurrence, the easiest way is apply Autofilter to your table and filter according to the necessary value when it is needed. This variant is more convenient then using Find All in the column.

21. 

    Augusta says:
    2014-09-23 at 5:11 am

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

22. 

    Walaki says:
    2014-06-12 at 11:28 am

    Ok, now this is the working one:

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

23. 

    Heather says:
    2014-06-12 at 10:55 am

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

24. 

    Walaki says:
    2014-06-12 at 9:51 am

    It highlights the B1,$A$1 part.

25. 

    Walaki says:
    2014-06-12 at 9:50 am

    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")

    1. 

       Vikas says:
       2018-12-19 at 9:38 am

       Hi, I completely understand your question. This was the same problem with me in excel.

       And then I got a YouTube channel ( Learn With Lokesh Lalwani) I always learn everything about the excel this channel. I would like to suggest that you can also learn from this channel.

       Thanks

26. 

    Compiler says:
    2014-05-26 at 11:33 am

    You just saved me a ton of stress.

    I really really appreciate you for this.

    From Nigeria, Regards!

    1. 

       Bobbi says:
       2014-06-12 at 4:47 pm

       It didn't work for me either. But this did
       =IF(ISERROR(MATCH(B2|$A$2:$A$10000|0))|"Unique"|"Duplicate")

27. 

    Mohamed says:
    2014-05-18 at 6:51 am

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

28. 

    Alex T says:
    2014-05-15 at 11:55 pm

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

    Thanks!
    Alex

29. 

    Ryan Johnsen says:
    2014-05-06 at 12:51 pm

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

30. 

    Ritu Solanki says:
    2014-03-14 at 5:08 pm

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

31. 

    Ravi says:
    2014-03-12 at 9:39 am

    Thanks for sharing.

32. 

    Anu says:
    2014-02-13 at 5:59 am

    Thanks for sharing. Its very useful :)

33. 

    Ramakrishnan says:
    2014-01-22 at 6:50 am

    Really this article is very useful one.

34. 

    Paul says:
    2014-01-13 at 3:23 am

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

35. 

    Ritu says:
    2013-12-03 at 3:53 pm

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

36. 

    Malene says:
    2013-11-29 at 10:55 pm

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

37. 

    Nick says:
    2013-11-22 at 1:04 pm

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

38. 

    Sandeep says:
    2013-11-20 at 10:40 am

    Thanks this puts me in safe hands of the excel

39. 

    Pawan Mall says:
    2013-11-08 at 6:37 pm

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

40. 

    Mani says:
    2013-11-08 at 5:25 am

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

41. 

    srikanth says:
    2013-10-31 at 3:06 pm

    Awesome, very helpful.
    Thanks for posting.