+ Reply to Thread
Results 1 to 16 of 16

Move duplicates from one worksheet to another

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-10-2020
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    141

    Move duplicates from one worksheet to another

    Hello everyone
    Appreciate anyone who can help me in assisting to create this macro.
    I need a macro to select the duplicate values of the first column
    Then delete the entire row of that duplicate values Then Transfere of rows that were deleted
    from the "Main sheet" to "Show Duplicates" sheet..... Thank you in advance.
    Attached Files Attached Files
    Last edited by JACK JOUSH; 07-09-2024 at 04:25 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,669

    Re: Move duplicates from one worksheet to another

    You indicate that you want the duplicate values identified based upon the first column. That would include 123456789 which you have not included in your mocked up results. Please clarify. Should we be looking only at the first column or the whole row.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    12-10-2020
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    141

    Re: Move duplicates from one worksheet to another

    Thank you for your replies.
    The only column that needs to be checked against is A

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,669

    Re: Move duplicates from one worksheet to another

    Power Query Solution

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"Head1"}, {{"Count", each _, type table [Head1=number, Head2=text, Head3=text, Head4=text, Head5=text, Head6=text, Head7=text, Head8=text, Head9=text, Head10=text, Head11=text, Head12=text, Head13=text, Head14=text, Head15=text, Head16=text]}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Ind",1,1)),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Head1", "Head2", "Head3", "Head4", "Head5", "Head6", "Head7", "Head8", "Head9", "Head10", "Head11", "Head12", "Head13", "Head14", "Head15", "Head16", "Ind"}, {"Head1", "Head2", "Head3", "Head4", "Head5", "Head6", "Head7", "Head8", "Head9", "Head10", "Head11", "Head12", "Head13", "Head14", "Head15", "Head16", "Ind"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Ind] <> 1))
    in
        #"Filtered Rows"
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    12-10-2020
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    141

    Re: Move duplicates from one worksheet to another

    I don't like dealing with Power Query ... Anyway Thank you very much and I welcome any other solutions using vba

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,669

    Re: Move duplicates from one worksheet to another

    I don't like dealing with Power Query
    I am curious. Why? Once I understand, I will stop sending you PQ solutions so as to stop wasting both our time.

  7. #7
    Forum Contributor
    Join Date
    12-10-2020
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    141

    Re: Move duplicates from one worksheet to another

    First of all, You are Respectful person and I respect for your kind time and help.
    In fact I have no big experience in dealing with Power Query Or maybe there is not enough time to study it.
    Thank you very much and have a nice day!

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,669

    Re: Move duplicates from one worksheet to another

    Thank you for your response.

    Man's mind stretched to new horizons, never returns to it original dimension.

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

    Re: Move duplicates from one worksheet to another

    Move duplicates from one worksheet to another
    Sub test()
        Dim r As Range
        Sheets("show duplicates").[a1].CurrentRegion.Clear
        With Sheets("main").[a1].CurrentRegion
            Set r = .Offset(, .Columns.Count + 1).Range("a1:a2")
            r(2).Formula = "=countif(a$2:a2,a2)>1"
            .AdvancedFilter 2, r, Sheets("show duplicates").[a1]: r.Clear
            '.RemoveDuplicates 1, 1  '<--- if you want to remove the duplicate row(s)
        End With
    End Sub
    Last edited by jindon; 07-10-2024 at 01:38 AM.

  10. #10
    Forum Contributor
    Join Date
    12-10-2020
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    141

    Re: Move duplicates from one worksheet to another

    Thank you so much Jindon - this worked But duplicate rows must be deleted from the Main data.
    Thanks a lot for your patience in this issue.

  11. #11
    Forum Contributor
    Join Date
    10-23-2015
    Location
    egypt
    MS-Off Ver
    2010
    Posts
    257

    Re: Move duplicates from one worksheet to another

    maybe
    Sub Duplicates()
     Const shSras As String = "Main"
     Const shDupes As String = "Show Duplicates"
     Dim a, i As Long, myRng As Range
     
     With Worksheets(shSras)
        a = .Range("a1").CurrentRegion
     End With
     With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(a)
            If Not .exists(a(i, 1)) Then
               .Item(a(i, 1)) = a(i, 1)
            Else
                With Worksheets(shSras)
                    If myRng Is Nothing Then
                        Set myRng = .Cells(i, "A")
                    Else
                        Set myRng = Union(myRng, .Cells(i, "A"))
                    End If
                End With
           End If
        Next
     End With
     With Worksheets(shDupes)
        If Not myRng Is Nothing Then
            myRng.EntireRow.Copy .Range("a2")
            myRng.EntireRow.Delete
        End If
     End With
    End Sub

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

    Re: Move duplicates from one worksheet to another

    Remove 1st apostrophe from the line, i.e.
            .RemoveDuplicates 1, 1  '<--- if you want to remove the duplicate row(s)

  13. #13
    Forum Contributor
    Join Date
    12-10-2020
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    141

    Re: Move duplicates from one worksheet to another

    Thank you so much salmasaied..... worked like a charm.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,181

    Re: Move duplicates from one worksheet to another

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

    Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  15. #15
    Forum Contributor
    Join Date
    12-10-2020
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    141

    Re: Move duplicates from one worksheet to another

    Thank you very much jindon for your great efforts

  16. #16
    Forum Contributor
    Join Date
    12-10-2020
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    141

    Re: Move duplicates from one worksheet to another

    @AliGW
    I know that already, ma'am ....Thank you for this kind share
    Best regards for all of you

+ 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. [SOLVED] Find and Move duplicates?
    By dwhite30518 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-07-2016, 02:00 AM
  2. [SOLVED] Need help to move duplicates from a list and paste them in a specific area on worksheet
    By Shellybelly in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-13-2015, 08:21 PM
  3. Move duplicates to a new worksheet
    By Austinkitten in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-30-2013, 01:51 PM
  4. Need to move data that matches criteria in cell and move to new worksheet
    By panagle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2013, 09:23 AM
  5. [SOLVED] Code needed to move duplicates to another worksheet
    By collinlo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-16-2012, 07:19 AM
  6. MACRO Find Duplicates move to new worksheet
    By danellekent in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-21-2011, 04:25 PM
  7. MACRO Find Duplicates move to new worksheet
    By phmic in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-30-2011, 03:52 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