Need help Deleting Duplicate Data!

J

Jogi

New Member
Joined
Aug 2, 2002
Messages
19
I've tried searching for a solution to this, but can't quite seem to find a right one.

I've a worksheet with many rows of data, but some of the rows I would like to delete via a macro if possible.

Unfortunately, colo's utility didn't work for me so I can't paste a copy of the work sheet, but it looks like following:

row1:(titles).....a/c....fltin....timein....fltout....timeout
row2:(data)......201..100...08:00.....101....08:30
row3:(data)......201............................101....08:30
row3:(data)......202............................102....09:00
row4:(data)......203..103...09:30.....104....10:00
row5:(data)......203............................104....10:00
row5:(data)......203..105...10:30...........................

I get this data from a text file and is sorted by fltout + flitin columns. I need to be able to delete row 3 and row 5. (There are about 100 rows for this data.)

I need a macro as this data changes everyday and it would definately make my job easier as I currently have to enter this data by hand. I have recorded a macro to get the data in above format but now I'm just a step away from the desired fromat.

Thanks in advance for all your help!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Need help deleting duplicate data.

The finished product shoud only show unique flts in the "fltout" column.

In the above sample data, row2 shows a flt coming in and going out, but row3 is still showing the flt going out. This row3 is what I'm trying to get rid off as I already have this data in row2.

Have I confused you more?
 
Upvote 0
Hi Jogi:

Please look at whether the following simulation is correct representation of your data ...
Book1
ABCDEF
1row1:(titles)a/cfltintimeinfltouttimeout
2row2:(data)2011008:001018:30
3row3:(data)2011018:30
4row3:(data)2021029:00
5row4:(data)2031039:3010410:00
6row5:(data)20310410:00
7row5:(data)20310510:30
Sheet3


Why do the rows 3 and 5 appear twice? Please clarify what is the criteria based on which a row should be deleted.
 
Upvote 0
Need Help Deleting Duplicate Data.T

Following is what the worksheet looks like once I manipulate the data from a text file:
Book1
ABCDE
1a/cfltintimeinfltouttimeout
22011008:001018:30
32011018:30
42021029:00
52031039:3010410:00
620310410:00
720310510:30
Sheet1


Basically, the data represents flts completed by an a/c from a city. For example, a/c 201 comes into city as flt 100 and goes out as flt 101.

The data is sorted by column D(fltout). Rows 3 and 6 now contain duplicate/useless data and I would like to be able to delete is using a macro.

Please let me know if this clarifies it or not. Thanks!
 
Upvote 0
Hi,

I Assume the Duplicates Column (FltOut) is Column D.

Place a CommandButton From the ToolBox ToolBar on your worksheet and assign the code below to its Click Event:

Code: 
Private Sub CommandButton1_Click()
    On Error Resume Next
    Dim AR() As Variant
    Dim Collec As New Collection
    ' Assumes the Column With Duplicates Is Column (4) ;Col "D"
    Set R = Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp)).Cells
    I = 1
    For Each CEL In R
        If Not IsEmpty(CEL) Then
            Collec.Add CEL.Value, CStr(CEL.Value)
                    ' If Duplicate.
                    If Err <> 0 Then
                            ' Then If First Duplicate, Initialize Array.
                            If UBound(AR) = 0 Then
                                        ReDim Preserve AR(0 To 0)
                                        Set AR(0) = CEL
                                        ReDim Preserve AR(0 To I)
                                        Err.Clear
                            ' If Not First Duplicate, Then Unite Duplicate Ranges.
                            Else
                                        ReDim Preserve AR(0 To I)
                                        Set AR(I) = Union(CEL, AR(I - 1))
                                        I = I + 1
                                        Err.Clear
                           End If
                    End If
        End If
    Next
    If IsEmpty(AR(1)) Then ReDim Preserve AR(0 To 0)
    AR(UBound(AR)).EntireRow.Delete
End Sub

Let me know if it works for you.
 
Upvote 0
I couldn't figure out how to use rafaaj2000's macro, maybe somebody can provide a step by step set of instructions for dummies.

However, I did find this macro (in the forum) that works in deleting all rows that have duplicate data in col D:



Sub Duplicate_Row_Delete()

'Sort worksheets with a header row

Worksheets("Sheet1").Cells.Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'Delete duplicates

Set currentCell = Worksheets("Sheet1").Range("D2")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Loop
Range("D2").Select
End Sub
 
Upvote 0
Hi Jogi:

You may also want to try the following macro to delete the duplicate rows based on your specification
Code: 
Sub yDeleteDuplicatesInColumnDWithCEmpty()
    For Each cell In [D:D]
        If cell = "" Then Exit Sub
        If cell.Row < 3 Then GoTo Continue1
        If cell = cell.Offset(-1, 0) _
            And cell.Offset(0, -1) = "" Then
            cell.EntireRow.Delete
        End If
Continue1:
    Next cell
End Sub
Please post back if it works for you -- otherwise explain a little further and let us take it from there.
 
Upvote 0
Yet another code which is similar to the previous one I posted But without having to use an array,which makes it Simpler ,Faster and more Readable.

Code: 
Private Sub CommandButton1_Click()

    On Error Resume Next
    Dim AR() As Variant
    Dim Collec As New Collection
    Dim R, cel, UnionRange As Range
    ' Assumes the Column With Duplicates Is Column (4) ;Col "D"
    On Error Resume Next

    Set R = Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp)).Cells
    Set UnionRange = Nothing
    For Each cel In R
        If Not IsEmpty(cel) Then
            Collec.Add cel.Value, CStr(cel.Value)
                    ' If Duplicate.
                    If Err <> 0 Then
                            If UnionRange Is Nothing Then
                                        Set UnionRange = cel
                                        Err.Clear
                           ' If Not First Duplicate, Then Unite Duplicate Ranges.
                            Else
                                       Set UnionRange = Union(cel, UnionRange)
                                       Err.Clear
                           End If
                    End If
        End If
    Next
    UnionRange.EntireRow.Delete
    
End Sub

Newshound12,To make this code work,you have to create a CommandButton from the ToolBox ToolBar.Right Click the worksheet Tab,Choose View Code and Paste it on the Blank Code Pane.
Hope this helps.
 
Upvote 0
Hi Jogi:

I applied the code to the following worksheet simulation ...
Book1
ABCDE
1A/Cfltintimeinfltouttimeout
22011008:001018:30
32011018:30
42021029:00
52031039:3010410:00
620310410:00
720310510:30
Sheet1


On running the following macro ...
Code: 
Sub yDeleteDuplicatesInColumnDWithCEmpty()
    For Each cell In [D:D]
        If cell = "" Then Exit Sub
        If cell.Row< 3 Then GoTo Continue1
        If cell = cell.Offset(-1, 0) _
            And cell.Offset(0, -1) = "" Then
            cell.EntireRow.Delete
        End If
Continue1:
    Next cell
End Sub

The following simulation shows the worksheet after running the macro ...
Book1
ABCDE
1A/Cfltintimeinfltouttimeout
22011008:001018:30
32021029:00
42031039:3010410:00
520310510:30
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Need a formula.
Replies
1
Views
295
Peter_SSs
Peter_SSs
G
  • Solved
Split items into Rows
Replies
2
Views
340
GreyFox8991
G
M
  • Locked
  • Question
Start and End
Replies
1
Views
461
Peter_SSs
Peter_SSs
sanantonio
  • Solved
VBA Loop Help - A Fresh one!
2
Replies
15
Views
1K
NateSC
NateSC
M
  • Question
Help converting 1-min data to 15-min data
Replies
4
Views
2K
MarkShepherd
M

Forum statistics

Threads
1,215,143
Messages
6,123,279
Members
449,094
Latest member
GoToLeep

Share this page

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