+ Reply to Thread
Results 1 to 17 of 17

Compare rows and display duplicates in a separate column

  1. #1
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Compare rows and display duplicates in a separate column

    Hello,

    I have a list of 7 numbers C4:I4, and I'd like to compare each row with its previous row and display the duplicates in another column separated by a dash.

    So, C4:I4, will be compared to C3:I3
    C3:I3 will be compared with C2:I2...

    Duplicates
    08 09 29 40 45 58 59
    01 15 21 29 32 67 63 - 29
    12 13 27 31 38 54 55 - 0
    04 21 33 38 45 68 69 - 38
    21 04 33 37 40 67 70 - 21-33
    08 20 27 32 41 54 63 etc
    04 21 19 20 22 39 70
    01 15 21 29 32 67 63

    Thank you in advance

    Sans
    Last edited by sans; 11-03-2011 at 12:13 PM.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Compare rows and display duplicates in a separate column

    A quick bit of VB needed for this one, I think, in the form of a UDF.

    Please Login or Register  to view this content.

    Put this in a module and you can then, say, use the function =ListDuplicates(C4:I4,C3:I3) to compare rows 3 and 4.

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Compare rows and display duplicates in a separate column

    At J2:

    Please Login or Register  to view this content.
    copied down. That leaves blank cells where no matches found.

    If you have to have a zero, then:

    Please Login or Register  to view this content.
    Last edited by NBVC; 11-03-2011 at 11:03 AM. Reason: added alternate formula to show 0 when no matches found
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Compare rows and display duplicates in a separate column

    Thank you very much both for your help!!! NBVC, I don't know how you find me every time.

    Is it possible to get a zero instead of a blank whenever there is no match?

    Again, thank you!

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Compare rows and display duplicates in a separate column

    Please Login or Register  to view this content.



  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Compare rows and display duplicates in a separate column

    Quote Originally Posted by sans View Post
    Thank you very much both for your help!!! NBVC, I don't know how you find me every time.

    Is it possible to get a zero instead of a blank whenever there is no match?

    Again, thank you!

    I added that formula to my last thread above....

  7. #7
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Compare rows and display duplicates in a separate column

    Exactly what I was looking for!!! Thank you all for your replies help!!

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Compare rows and display duplicates in a separate column

    You are welcome.

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Compare rows and display duplicates in a separate column

    or
    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Compare rows and display duplicates in a separate column

    Hello,

    I don't know if this requires a separate thread but is it possible to do a small variation of the above? To also be able to display any duplicates (two or more) in a range? For instance,

    01 02 29 32 63 63 - get 63
    01 01 63 63 20 15 - get 1-63
    01 01 01 63 63 15 - get 1 -63

    So essentially display any numbers that appear twice or more in a range.

    Thank you in advance,
    Sans

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Compare rows and display duplicates in a separate column

    If you try to understand the code you can easily do that yourself:

    Please Login or Register  to view this content.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Compare rows and display duplicates in a separate column

    Or with formula... (not as intuitive a change) assuming first row is in C2:H2:

    Please Login or Register  to view this content.
    copied down.

  13. #13
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Compare rows and display duplicates in a separate column

    Thank you for your reply. I am only just starting to understand formulas, so at the moment trying to create a code to do a particular function is extremely confusing. I have only been using excel since I became a member to the forum.

    About my previous post, the duplicates I'd lke to display do not rely on whether they matched any numbers from the previous row. So in a range of C4:I4 there are 7 numbers and only if in this range there are any duplicates display them.

    For example:

    20-21-22-24-24-25-25 - display 24-25
    10-12-13-14-15-16-16 - display 16
    33-33-33-34-34-35-35 - display 33-34-35

    I see that in the code two ranges are used that's why I am asking.

    Thanks again
    Sans

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Compare rows and display duplicates in a separate column

    See my formula above.

  15. #15
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Compare rows and display duplicates in a separate column

    Thank you for your replies. Exactly what I was looking for. NVBC, I modified your formula in post number 3# before you replied and attempted to do it myself and got somewhat similar results

    Please Login or Register  to view this content.
    The only difference is if in a range of numbers there is two of the same i.e. 23-24-25-26-26-27 I get 26 but if there are three of the same numbers i.e. 23-24-25-26-26-26 I get 26-26-26. Can't really understand why

    Thank you all for your help!

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Compare rows and display duplicates in a separate column

    I you sure you are using my last formula:

    Please Login or Register  to view this content.
    With that formula and your values: 23-24-25-26-26-26 separately in C2:H2, I get just 26 (once)

  17. #17
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Compare rows and display duplicates in a separate column

    No,your formula works perfectly! What I meant was that I tried to do it myself before you replied but I kept getting that error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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