+ Reply to Thread
Results 1 to 11 of 11

Remove duplicates from rows

  1. #1
    Registered User
    Join Date
    04-02-2020
    Location
    Bratislava
    MS-Off Ver
    2016
    Posts
    80

    Remove duplicates from rows

    Hi,
    I'd want to remove duplicates for every single row of my file. I need a macro that evaluates row by row and compares only the values in the active row.

    So far, I got this:
    Please Login or Register  to view this content.
    but it does not work, how can I do it?
    I have attached a template.

    thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Remove duplicates from rows


    Hi,

    unclear ! What are your criterias to detect any duplicate row ?

  3. #3
    Registered User
    Join Date
    04-02-2020
    Location
    Bratislava
    MS-Off Ver
    2016
    Posts
    80

    Re: Remove duplicates from rows

    Hi,
    the criteria is to detect duplicate cells in a row. For instance, in row number 5 we have two times "item_type_keyword" and I'd want to remove that duplicate. Then the macro has to evaluate the next row independently.

    THanks

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Remove duplicates from rows

    (removed as per next post)
    Last edited by Marc L; 05-12-2020 at 12:11 PM.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Remove duplicates from rows


    Now I better imagine your need, attach at least the expected result …

  6. #6
    Registered User
    Join Date
    04-02-2020
    Location
    Bratislava
    MS-Off Ver
    2016
    Posts
    80

    Re: Remove duplicates from rows

    Hi,
    I tried with the macro recorder but the "remove duplicates" default option in Excel does not suit my needs.

    I have attached a file, maybe it could be better explained now

    Thank you :D
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Remove duplicates from rows

    Perhaps next code in the sheet module
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by PCI; 05-12-2020 at 12:37 PM. Reason: Select code removed
    - Battle without fear gives no glory - Just try

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Re: Remove duplicates from rows


    Yes, the Excel Remove duplicates belongs to smart sheets only …

    According to your initial attachment as a VBA beginner starter :

    PHP Code: 
    Sub Demo1()
            
    Dim R&, C%
            
    Application.ScreenUpdating False
        With ActiveSheet
    .UsedRange.Rows
            
    For 1 To .Count
            
    For = .Cells(R, .Columns.Count 1).End(xlToLeft).Column To 5 Step -1
                
    If Application.Match(.Cells(RC), .Item(R), 0) <> C Then .Cells(RC).Delete xlShiftToLeft
            Next C
    R
        End With
            Application
    .ScreenUpdating False
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon « Add Reputation » ! ◄ ◄

  9. #9
    Registered User
    Join Date
    04-02-2020
    Location
    Bratislava
    MS-Off Ver
    2016
    Posts
    80

    Re: Remove duplicates from rows

    Thanks very much guys,
    both solutions work perfectly. You saved my day

    Regards

  10. #10
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Remove duplicates from rows

    You're welcome, glad to help you.
    Thx for the Rep.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb For the fun …


    Thanks for the rep' !

    According to your attachment a VBA variation just with a single loop :

    PHP Code: 
    Sub Demo2()
            
    Dim N&
            
    Application.ScreenUpdating False
        With 
    [A1].CurrentRegion.Columns(5).Resize(, [A1].CurrentRegion.Columns.Count 4).Cells
            
    For = .Count To 1 Step -1
                
    If Not IsEmpty(.Item(N)) Then If Application.Match(.Item(N), .Rows(.Item(N).Row), 0) < .Item(N).Column 4 _
                                         Then 
    .Item(N).Delete xlShiftToLeft
            Next
        End With
            Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon « Add Reputation » ! ◄ ◄
    Last edited by Marc L; 05-12-2020 at 01:45 PM. Reason: optimization …

+ 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. How to remove rows that are NOT duplicates
    By ecruz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-23-2015, 04:51 PM
  2. [SOLVED] Find duplicates rows, sum cells then remove duplcate rows
    By Wagstaff in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-22-2013, 09:37 AM
  3. Remove rows without Duplicates
    By Apple1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2013, 10:12 AM
  4. Remove Duplicates from Rows
    By dwc in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-24-2013, 06:47 AM
  5. Replies: 1
    Last Post: 10-23-2012, 09:12 AM
  6. Remove unique rows and keep duplicates
    By tmduffy in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 10-30-2010, 08:31 AM
  7. Replies: 1
    Last Post: 04-17-2008, 03:15 PM

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