Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Remove Duplicates Leaves the wrong data I need last row not first?

  1. #1
    Board Regular
    Join Date
    Feb 2014
    Posts
    1,715
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Remove Duplicates Leaves the wrong data I need last row not first?

    HI Everyone,

    I have this Macro that removes duplicates great

    Code:
    Sub Macro2()
        ActiveSheet.Unprotect
        ActiveSheet.Range("$A:$N").RemoveDuplicates Columns:=Array(2, 9, 10), _
            Header:=xlYes
           ActiveSheet.Protect
    End Sub
    The only problem is it leaves the first row not the last row of data

    can I get it to leave the last row?

    thanks

    Tony

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    2,193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    I don't have the (working) VBA code for that, but

    maybe you can work from below to the top with removing duplicates.

  3. #3
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,050
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    Maybe add a numerical sequence in a helper column, sort by that column in descending order, delete duplicates, sort by the helper column in ascending order, delete the helper column,.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    34,402
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    Just a comment: If it is important that duplicates are removed accurately, particularly if your data is large where you wouldn't notice any errors, then I would not rely on Excel's built-in Remove Duplicates feature. To see why, put this data on a fresh worksheet and use Remove Duplicates on it.

    Remove Duplicates Failure

     AB
    1Hdr 1Hdr 2
    29c9
    399
    410c10
    5109
    610c10


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007, 2003 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  5. #5
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,050
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    Quote Originally Posted by Peter_SSs View Post
    Just a comment: If it is important that duplicates are removed accurately, particularly if your data is large where you wouldn't notice any errors, then I would not rely on Excel's built-in Remove Duplicates feature. To see why, put this data on a fresh worksheet and use Remove Duplicates on it.

    Remove Duplicates Failure

    A B
    1 Hdr 1 Hdr 2
    2 9c 9
    3 9 9
    4 10c 10
    5 10 9
    6 10c 10


    Excel tables to the web >> Excel Jeanie HTML 4
    I think it works properly if the data is first sorted into sequence

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    34,402
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    Quote Originally Posted by footoo View Post
    I think it works properly if the data is first sorted into sequence
    That may be the case (I don't know for sure) but that might not always suit the user to alter the order of their data, and could be cumbersome if lots of columns are involved. I haven't noticed anything in any Remove Duplicates documentation that says the data must be sorted on the columns you want assessed for duplicates. I just don't trust it given that I have seen it fail on several occasions.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007, 2003 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  7. #7
    Board Regular
    Join Date
    Feb 2014
    Posts
    1,715
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    Thanks everyone,
    I can't change the order or add another column as the file is quite large, Remove duplicates has worked great it just it leave the wrong row sound like its more trouble than I thought! but any ideas would be great.

    thanks

    Tony

  8. #8
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,050
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    [QUOTE=tonywatsonhelp;4888656]
    I can't change the order or add another column as the file is quite large[QUOTE]

    The size of the file does not prevent using a temporary helper column.

  9. #9
    Board Regular
    Join Date
    Feb 2014
    Posts
    1,715
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    Footoo, I just said I can't change the order so what is the point of a helper column? (P.S. I feel this is going off track! so please only reply if you have a solution or something I will find helpful as I have to keep restarting my PC)

  10. #10
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,050
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Duplicates Leaves the wrong data I need last row not first?

    Quote Originally Posted by tonywatsonhelp View Post
    Footoo, I just said I can't change the order so what is the point of a helper column? (P.S. I feel this is going off track! so please only reply if you have a solution or something I will find helpful as I have to keep restarting my PC)
    The helper column will not change the order - it will only reverse the order temporarily.
    I've already provided you with a solution in post #3 . What's the point of my posting if you don't bother to read it properly?
    I leave it to you to write the code.
    Please don't worry - I shall not be posting to this thread again.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com