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

fowzan

Board Regular
Joined
Aug 5, 2015
Messages
59
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Try this:-
Results sheet2
NB:- This code will modify your data
Code:
[COLOR="Navy"]Sub[/COLOR] MG10Oct47
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   oTxt = Join(Application.Index(Dn.Resize(, 11).Value, 0, Array(1, 2, 3, 4, 6, 8, 9, 11)), ",")
    [COLOR="Navy"]If[/COLOR] Not .Exists(oTxt) [COLOR="Navy"]Then[/COLOR]
        .Add oTxt, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Union(.Item(oTxt), Dn) Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, .Item(oTxt), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng
    c = c + 1
    Sheets("sheet2").Cells(c, 1).Resize(, 15).Value = Dn.Resize(, 15).Value
[COLOR="Navy"]Next[/COLOR] Dn
nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Sorry bad copying, Place the "End With " at the end as below:-

Code:
End if
[B][COLOR=#ff0000]End With  [/COLOR][/B]
End Sub
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top