+ Reply to Thread
Results 1 to 8 of 8

Comparing 2 columns to see if it is duplicated

  1. #1
    Registered User
    Join Date
    12-15-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    26

    Comparing 2 columns to see if it is duplicated

    Hi All!

    I want to see duplicates when 2 columns have 2 different values. So basically comparing 2 rows. I have attached an excel for reference.

    Thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Comparing 2 columns to see if it is duplicated

    one solution would be a helper column - see attachment
    in your sample your match is wrong. You could not see it but one "Apple" has a space after the name.
    you could ignore it if you use the trin- function additionally

    cheers
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Comparing 2 columns to see if it is duplicated

    Hi,

    in order to find the match in 2 columns - I created column E to combine both C&D into the same cell.

    in the "Match" column I applied the following formula:

    =+IF(COUNTIF($E$5:$E$9,E5)>1,"Yes", "No")

    You can also apply conditional formatting onto column E (format onlu unique or duplicate values), so the duplicate cells will be colored.
    Attached Files Attached Files

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Comparing 2 columns to see if it is duplicated

    You may do it also without helper column. the formula for E5 (and copy down):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Comparing 2 columns to see if it is duplicated

    Nicem Kaper. can you please explain the formula? what is the role of "--" that you have put after the sumproduct?

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Comparing 2 columns to see if it is duplicated

    The result of ($C$5:$C$9 & $D$5:$D$9=C5 & D5) will be an array of boolean (False/True) values. -- forces excel to convert them to array of (1/0) values and then sumproduct just sums 1's in this array

    write in a cell =true (you will see TRUE as result)
    then =-true (and result will not be FALSE as one could expect, but -1)
    then =--true and you will see 1
    of course other methods to convert array of boolean values into array of numbers (0 for false and 1 for true) could be used, like: N($C$5:$C$9 & $D$5:$D$9=C5 & D5) or 1*($C$5:$C$9 & $D$5:$D$9=C5 & D5)

  7. #7
    Registered User
    Join Date
    12-15-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    26

    Re: Comparing 2 columns to see if it is duplicated

    thank you!!!!!!!!

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Comparing 2 columns to see if it is duplicated

    Thanks Kaper!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro shows duplicated entries but sometimes they are not duplicated due to data.
    By tweacle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2020, 04:47 PM
  2. [SOLVED] Columns to be merged if value is same ( duplicated )
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-30-2019, 08:34 AM
  3. to count duplicated cells in multiple columns
    By karyx in forum Excel General
    Replies: 3
    Last Post: 01-17-2017, 05:24 AM
  4. Find duplicated value by comparing textbox and column
    By behedwin in forum Excel General
    Replies: 6
    Last Post: 01-15-2017, 01:23 AM
  5. Subtracting duplicated text between cells of two columns
    By sp86 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 10-09-2013, 10:26 AM
  6. [SOLVED] delete duplicated columns from left to right
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2013, 07:47 AM
  7. [SOLVED] Finding Duplicated Text within Columns
    By Jen in forum Excel General
    Replies: 1
    Last Post: 02-15-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1