Creating a Loop in VBA to clear an Excel Rec of duplicate data

Hi Guys, I need to create a Macro that can Loop down Column E find a Duplicate ID and cut and paste that line onto a different tab. The Duplicate ID must have a 0 or blank space in Column F so the correct ID is left behind. Below you see the column "FO MTM" has a blank space and  2 exact numbers in the "GBO ID" column, the 2nd row need to be cut and paste to a tab called "Exceptions". Can anyone help? I enclose an attachment of the actual Rec


Book      Currency      Trade ID                     GBO ID                   FO MTM                        GL MTM
GFALCOFRAM      GBP      6530805      3811642.28      315,443      315,443
GFALCOFRAM      GBP      6416721      3811642.28            315,443
DummyRec.xlsx
JustincutAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
My previous comment tells how to do this to a tab called Exceptions.
0
 
Saqib Husain, SyedEngineerCommented:
This macro assumes that the duplicate is immediately below the one to be kept.

Sub movedups()
    Dim cel As Range
    Dim i As Long
    For i = Range("E" & Rows.Count).End(xlUp).Row To 4 Step -1
        Set cel = Range("E" & i)
        cel.Select
        If cel.Value <> "" And cel.Value = cel.Offset(-1).Value And (cel.Offset(, 1) = 0 Or cel.Offset(, 1) = "") Then
            cel.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)
            cel.EntireRow.Delete
        End If
    Next i
End Sub
0
 
JustincutAuthor Commented:
Hi, the file is 50,000 rows of data and is not necessarily below but could be 10,000 rows away or could be above the genuine trade ID. Could you adapt your code?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Saqib Husain, SyedEngineerCommented:
Try

Sub movedups()
    Dim cel As Range
    Dim i As Long
    For i = Range("E" & Rows.Count).End(xlUp).Row To 4 Step -1
        Set cel = Range("E" & i)
        cel.Select
        If cel.Value <> "" And WorksheetFunction.CountIf(cel.EntireColumn, cel.Value) > 1 And (cel.Offset(, 1) = 0 Or cel.Offset(, 1) = "") Then
        Stop
            cel.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)
            cel.EntireRow.Delete
        End If
    Next i
End Sub
0
 
JustincutAuthor Commented:
What does "Sheet2Range" refer to? I would like to cut the duplicate Ids and paste it to a tab called "Exceptions"
0
 
Saqib Husain, SyedEngineerCommented:
Change

            cel.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)

to

            cel.EntireRow.Copy Sheets("Exceptions").Range("A" & Rows.Count).End(xlUp).Offset(1)
0
 
JustincutAuthor Commented:
Actually, how can I adapt the code so it Pastes the Duplicates into the Range ("P4") in the
same tab?
0
 
JustincutAuthor Commented:
Either this or insert a tab in the TEXT file and call the Tab "Exceptions". How do I do this?
0
 
JustincutAuthor Commented:
Hi, its a text file so there is no tab so I have to insert a tab and call it "Exceptions" and then paste it.How do I adapt the code?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.