Delete Duplicate Rows depend on creteria on Col A and Col B

tuffley21 · May 28th 2013

I got 2 sheets one with Employees name in col A number of hours worked in col B. Second sheet is download form time clock which has Name in Col A and Hours worked in Col B and other details in other columns. I want MACRO to delete matching rows depend on Col A and Col B . any guidance please

cytop · May 28th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Please post an anonymised copy of your workbook with a sample of the data...

tuffley21 · May 30th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Are you able to see attachment (done 3> times)

tuffley21 · Jun 3rd 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Any help please?

tuffley21 · Oct 7th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Would anyone help on this please.

cytop · Oct 7th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Seems I missed this earlier...

Code

Sub z()


    Dim r As Excel.Range
    Dim lngRow As Long
    Dim lngRows As Long
    
    Set r = Sheets("Time Clock").Range("A1").CurrentRegion
    
    lngRows = Sheets("Dept Hours").Range("A1").End(xlDown).Row
    
    For lngRow = lngRows To 2 Step -1
    
        If WorksheetFunction.VLookup(Sheets("Dept Hours").Cells(lngRow, 1).Value, r, 2, False) = _
            Sheets("Dept Hours").Cells(lngRow, 2).Value Then
            
            Sheets("Dept Hours").Rows(lngRow).EntireRow.Delete
        End If
    Next
    
End Sub

Display More

Assumptions:

All data is contiguous with no blank rows
The Time Clock sheet always starts Row 1, Col 1

tuffley21 · Oct 7th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Thank you very much -I do some testing and let you know- Ta

tuffley21 · Oct 10th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

[ATTACH=CONFIG]56813[/ATTACH]

Hi cytop
I get this error when I ran this with FRESH data (unable to get the VLookup property of the WorksheetFunction class)
Appriciate if you help me
Thanks

cytop · Oct 10th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Post a copy of that workbook with sample data...

tuffley21 · Oct 10th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

i
[Blocked Image: http://www.ozgrid.com/forum/images/attach/xlsx.gif] sample.xlsx (19.7 KB) I need to compare Col A and B of two sheets- Leave Dept Hours with only difference values and those values highlighted on the Time Clock sheet. Not sure I explain enough.Thanks

cytop · Oct 10th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

It was trying to lookup a value which does not exist - added a FIND to ensure the value being looked up does exist...

Code

Sub z()
     
    Dim r As Excel.Range
    Dim rF As Excel.Range
    
    Dim lngRow As Long
    Dim lngRows As Long
     
    Set r = Sheets("Time Clock").Range("A1").CurrentRegion
     
    lngRows = Sheets("Dept Hours").Range("A1").End(xlDown).Row
     
    For lngRow = lngRows To 2 Step -1
          
        Set rF = r.Find(What:=Sheets("Dept Hours").Cells(lngRow, 1).Value, LookAt:=xlWhole, MatchCase:=False)
        
        If Not rF Is Nothing Then
            If WorksheetFunction.VLookup(Sheets("Dept Hours").Cells(lngRow, 1).Value, r, 2, False) = _
            Sheets("Dept Hours").Cells(lngRow, 2).Value Then
                 
                Sheets("Dept Hours").Rows(lngRow).EntireRow.Delete
            End If
        End If
     Next
     
End Sub

Display More

Of course, could have handled this any number of ways...

apo · Oct 10th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Hi..

Quote

I want MACRO to delete matching rows depend on Col A and Col B

Which sheet do you want to delete the rows from... ?.. Dept Hours Sheet..?

tuffley21 · Oct 10th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

thanks for response - Dept Hours

apo · Oct 10th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Hi..

New object for me.. but i think this does it..

Code

Private Sub CommandButton1_Click()
Dim d As Object, e
Dim i As Long
Set d = CreateObject("scripting.dictionary")
For Each e In Sheets("Time Clock").Range("A1").CurrentRegion.Offset(1)
    d(e.Value & e.Offset(0, 1).Value) = 1
Next e
Sheets("Dept Hours").Activate
For i = Sheets("Dept Hours").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If d(Range("A1").CurrentRegion(i, 1).Value & Range("A1").CurrentRegion(i, 2).Value) = 1 Then
            Cells.Rows(i).Delete
        End If
Next i
End Sub

Display More

tuffley21 · Oct 10th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Thanks both of you cytop and apo - will let you know how I go- ta

tuffley21 · Oct 13th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Both works- cytop code without header and apo code leave the headings where it is- thanks both of you. How do I high light difference on Time Clock in one go (same code module) putting in a separate sheet side by side P-l-e-a-s-e

apo · Oct 13th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Can you elaborate on both these statements..

Quote

How do I high light difference on Time Clock in one go (same code module)

I don't get that.. I mean.. you are deleting the rows on the "Dept Hours" sheet if they DO exist on the Time Clock sheet... so .. how are you going to highlight the differences on the Time Clock sheet (rows that DO NOT exist on the Dept Hours sheet and Time Clock sheet).. if they don't exist to be highlighted?

and

Quote

putting in a separate sheet side by side

The more detail the better..

tuffley21 · Oct 13th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

I scrambled on explanation. 1. correct delete similar entries. 2. Now I have 2 difference between 2 sheets. "Time clock has ALL entries but Dept Hours has <> entries (after macro). I want to see difference between Dept hours to Time Clock. Time Clock had actual hours and Dept Hours is what they are claiming. eg if one person is late Time Clock say only work for 7.75 hours, But Dept Hours say 8 hours I need to see this person side by side so I can go and fix the time correctly. I don't want to see if both entries are same , I want to deal with not equal entries. Thanks

apo · Oct 13th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Hi..

Try this..
I added a new sheet called "Variances"..

Your desired results should display there..

Code

Private Sub CommandButton1_Click()
  Dim d As Object, e
    Dim i, LR As Long
    Dim aCell As Range, strSearch As String
    Dim oSht As Worksheet
    Set oSht = Sheets("Time Clock")
    Set d = CreateObject("scripting.dictionary")
    For Each e In Sheets("Time Clock").Range("A1").CurrentRegion.Offset(1)
        d(e.Value & e.Offset(0, 1).Value) = 1
    Next e
    Sheets("Dept Hours").Activate
    For i = Sheets("Dept Hours").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      
    If d(Range("A1").CurrentRegion(i, 1).Value & Range("A1").CurrentRegion(i, 2).Value) = 1 Then
            Cells.Rows(i).Delete
        End If
        
    lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
    strSearch = Range("A1").CurrentRegion(i, 1).Value
    
    If d(Range("A1").CurrentRegion(i, 1).Value & Range("A1").CurrentRegion(i, 2).Value) <> 1 Then
    Set aCell = oSht.Range("A1:A" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
    aCell.Resize(1, 2).Interior.ColorIndex = 3
    Sheets("Variances").Range("A" & Rows.Count).End(xlUp).Resize(1, 4).Offset(1).Value = _
    Range("A1").CurrentRegion(i, 1).Resize(1, 4).Value
    Sheets("Variances").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = aCell.Offset(, 1).Value
    End If
    End If
    Next i
End Sub

Display More

tuffley21 · Oct 13th 2013

Re: Delete Duplicate Rows depend on creteria on Col A and Col B

Thank you - I will give it a go -let you know- Thank you for your time and knowledge.

Delete Duplicate Rows depend on creteria on Col A and Col B

Participate now!

Share