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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

LloydFinancials

Well-known Member
Joined
Apr 24, 2015
Messages
518
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?
 

fowzan

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

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Sorry bad copying, Place the "End With " at the end as below:-

Code:
End if
[B][COLOR=#ff0000]End With  [/COLOR][/B]
End Sub
 

fowzan

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

Watch MrExcel Video

Forum statistics

Threads
1,102,661
Messages
5,488,163
Members
407,628
Latest member
Faceless Judge

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top