+ Reply to Thread
Results 1 to 30 of 30

Remove duplicated data and inform user how many data have been removed using macro

  1. #1
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Lightbulb Remove duplicated data and inform user how many data have been removed using macro

    Question Solved
    Last edited by SHELLY567; 01-15-2018 at 12:04 PM.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,095

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Are the duplicate values in column E? Do you want to remove the entire row where duplicates are found?
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Question solved
    Last edited by SHELLY567; 01-15-2018 at 12:32 PM.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,095

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Hopefully, I have understood correctly what you want to do. Try this macro on a copy of your file.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Question Solved
    Last edited by SHELLY567; 01-15-2018 at 12:04 PM.

  6. #6
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    The macro doesn't seem to work

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,904

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Deleted because OP complied to Forum Rules.
    Last edited by bakerman2; 01-13-2018 at 07:30 PM.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,904

    Re: Remove duplicated data and inform user how many data have been removed using macro

    First rule on deleting rows is to start from the bottom up to the top.
    Does it matter which duplicate is removed ?
    If possible post an example file which reflects the current situation so we have something to work with.

  9. #9
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Question Solved
    Last edited by SHELLY567; 01-15-2018 at 12:05 PM.

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Mumps' code runs like this:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  11. #11
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    What if I do not want the entire row to be deleted? Meaning only duplicated rows in column E should be deleted.

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Meaning only duplicated rows in column E
    Do you mean just the cells?

    What does your data look like?

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Quote Originally Posted by SHELLY567 View Post
    Column E is a mix of duplicated values and unique values. I have to remove duplicated values and just leave the unique values
    Try
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Question Solved
    Last edited by SHELLY567; 01-15-2018 at 12:06 PM.

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Remove duplicated data and inform user how many data have been removed using macro

    If it took long, you must have many formula.
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Question Solved
    Last edited by SHELLY567; 01-15-2018 at 12:03 PM.

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Remove duplicated data and inform user how many data have been removed using macro

    OK, then try this one
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Question Solved
    Last edited by SHELLY567; 01-15-2018 at 12:03 PM.

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Remove duplicated data and inform user how many data have been removed using macro

    That's impossible.

    They looks duplicates, but I don't think they are.

    Extra space or something.

    If you uplocad that workbook, it will be cleare.

  21. #21
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Question Solved
    Last edited by SHELLY567; 01-15-2018 at 12:01 PM.

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Remove duplicated data and inform user how many data have been removed using macro

    This is my first time that it doesn't work...
    Seems need to run twice...
    Please Login or Register  to view this content.

  23. #23
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Question Solved
    Last edited by SHELLY567; 01-15-2018 at 12:00 PM.

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Try this one
    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Question Solved
    Last edited by SHELLY567; 01-15-2018 at 12:00 PM.

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Change to
    Please Login or Register  to view this content.
    Edit: one line deleted.
    Last edited by jindon; 01-15-2018 at 08:54 AM.

  27. #27
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Yea it works! Thank you! I wonder if there is a simpler code?

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Remove duplicated data and inform user how many data have been removed using macro

    1) prepare helper column
    2) concatenate A,C,D,E,F,G,H and I
    3) Use Countif function
    4) Autofilter by helper column to pick 2
    5) hirite one of the visible cell and right click "Delete entirerow".

    May take a long time,

  29. #29
    Registered User
    Join Date
    01-13-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    16

    Re: Remove duplicated data and inform user how many data have been removed using macro

    What if I sort data into duplicated and non duplicated first and delete the duplicated data? I just want to make sure the macro is as short and simple as possible.

  30. #30
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Remove duplicated data and inform user how many data have been removed using macro

    Shelly, please do NOT delete your posts in this - or any other thread. The forum is about people mutually helping each other, but now, any other member looking for assistance on a similar issue, will not be able to get any help from this thread.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Replies: 1
    Last Post: 01-19-2017, 08:02 AM
  2. Remove duplicates but consolidate data from removed
    By LHerndon in forum Excel General
    Replies: 3
    Last Post: 01-06-2017, 01:11 PM
  3. Replies: 0
    Last Post: 08-17-2014, 10:00 AM
  4. [SOLVED] avoiding the excel pop up to inform a user that the sheet is protected.
    By emilyloz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-07-2013, 01:02 PM
  5. [SOLVED] Find the lowest value of stats selected and inform the user
    By ste67@me.com in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-11-2013, 02:28 AM
  6. Remove duplicates and consolidate data in duplicated fields.
    By stevo999 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-23-2012, 09:50 AM
  7. Macro Help - Delete Duplicated Data
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-02-2005, 09:05 AM

Tags for this Thread

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