what would be the best way to cut/paste duplicate rows to the next sheet
Results 1 to 8 of 8

Thread: what would be the best way to cut/paste duplicate rows to the next sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default what would be the best way to cut/paste duplicate rows to the next sheet

    hi,
    i have an item list on excel. 42k rows and 15 columns.
    alot of items have been listed twice and some even thrice. as the product is same but the seller is different.

    what i want to do is check only 8 columns (a,b,c,d,f,h,i,k) to decide if the row is duplicate or not. if it is has an exact match with another row for the respective 8 columns, i want to cut paste both/all the matching rows into the next sheet (all 15 columns), where i can manually merge/delete/seperate depending on the item

  2. #2
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: what would be the best way to cut/paste duplicate rows to the next sheet

    bump... anyone??

  3. #3
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,820
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: what would be the best way to cut/paste duplicate rows to the next sheet

    Try this:-
    Results sheet2
    NB:- This code will modify your data
    Code:
    Sub MG10Oct47
    Dim Rng As Range, Dn As Range, c As Long, nRng As Range
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
       oTxt = Join(Application.Index(Dn.Resize(, 11).Value, 0, Array(1, 2, 3, 4, 6, 8, 9, 11)), ",")
        If Not .Exists(oTxt) Then
            .Add oTxt, Dn
        Else
            If nRng Is Nothing Then Set nRng = Union(.Item(oTxt), Dn) Else Set nRng = Union(nRng, .Item(oTxt), Dn)
        End If
    Next
    If Not nRng Is Nothing Then
    For Each Dn In nRng
        c = c + 1
        Sheets("sheet2").Cells(c, 1).Resize(, 15).Value = Dn.Resize(, 15).Value
    Next Dn
    nRng.EntireRow.Delete
    End If
    End Sub
    Regards Mick

  4. #4
    Board Regular
    Join Date
    Apr 2015
    Posts
    502
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: what would be the best way to cut/paste duplicate rows to the next sheet

    1. Why not sort on A:K?

    2. If A2:H2 is found somewhere else in the sheet, copy A2:O2 to first row next sheet and copy found row to row 2 next sheet. Continue comparing Sheet1!A2:H2 to rest of data, copying and pasting?

    3. Then move to Sheet1!A3:H3 copying and pasting in next available row in Sheet2 and so forth?

  5. #5
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: what would be the best way to cut/paste duplicate rows to the next sheet

    got this error on first compile "Compile Error: Expected End with"

    i put "End With" after ".CompareMode = vbTextCompare" line
    and now i am getting
    Compile error: Invalid or unqualified reference on this line
    If Not .Exists(oTxt) Then

  6. #6
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,820
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: what would be the best way to cut/paste duplicate rows to the next sheet

    Sorry bad copying, Place the "End With " at the end as below:-

    Code:
    End if
    End With  
    End Sub

  7. #7
    Board Regular
    Join Date
    Aug 2015
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: what would be the best way to cut/paste duplicate rows to the next sheet

    works absolutely flawless. this probably saved me a complete week of manual work.
    THANK YOU SO MUCH.
    just so that i can reuse the code in other cases could you just correct me if i am wrong

    Value, 0, Array(1, 2, 3, 4, 6, 8, 9, 11) - the numbers 1, 2, 3, ......, 11 indicate the column numbers
    Dn.Resize(, 11) - and the number 11 here represents the last column that has to be checked
    Sheets("sheet2").Cells(c, 1).Resize(, 15) - and here 15 indicates the total number of columns.

    Once again thanks alot. this code saved me alot of time.

  8. #8
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,820
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: what would be the best way to cut/paste duplicate rows to the next sheet

    Your assumtions are correct and you're very welcome

Some videos you may like

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
  •