Duplicates

[cns324]

Hello, looking to do 2 things with duplicate values in VBA:
1.) Need to highlight duplicates in Sheet1, column E and when comparing it to Sheet2, column E. It needs to be the whole column as data is being added and removed daily. Only sheet1 needs to be highlighted. I tried to do a countif conditional formatting, but it was not working.

2.) Need to delete the full row, for duplicates in Sheet1, column E, when compared to Sheet3, column E.


Thanks

 

[Micron]

Some data examples would help. You're likely thinking there's only one way to interpret that, but I'd say that's not the case.
And how many rows, because that could rule out using arrays.

 

[cns324]

Below is what the data would look like in general. The Request # in Column E will always be in the same format as shown. I have code the checks for duplicates and deletes them on sheet1, but I would like to also have it check for duplicates on Sheet2 and then Sheet3.
Each of the 3 sheets will have the headers as shown below. Rows can vary from 2, to up to 5000.

Data

Comments

Requested

App

Req #

Days in

Req Date

Expire Date

Name

Req Name

Assigned

Approval

Completed

12/28/2021

xx

xx

xx

req10001

xx

xx

xx

xx

xx

xx

xx

xx

1/3/2022

xx

xx

xx

req10002

xx

xx

xx

xx

xx

xx

xx

xx

1/8/2022

xx

xx

xx

req10003

xx

xx

xx

xx

xx

xx

xx

xx

1/14/2022

xx

xx

xx

req10004

xx

xx

xx

xx

xx

xx

xx

xx

1/18/2022

xx

xx

xx

req10005

xx

xx

xx

xx

xx

xx

xx

xx

1/19/2022

xx

xx

xx

req10006

xx

xx

xx

xx

xx

xx

xx

xx

1/19/2022

xx

xx

xx

req10007

xx

xx

xx

xx

xx

xx

xx

xx

1/22/2022

xx

xx

xx

req10008

xx

xx

xx

xx

xx

xx

xx

xx

1/24/2022

xx

xx

xx

req10009

xx

xx

xx

xx

xx

xx

xx

xx

 

[Micron]

I have code the checks for duplicates and deletes them on sheet1

Well I'm still confused. Your post said you wanted code to do this to sheet1 but now you're saying you already have that? You just need to duplicate it for other sheets?
If that's not the case, then I'm pretty sure 5k rows is too much for an array. Recordsets come to mind if there is no Excel formula for this, but until just a moment ago I had no idea that Excel could work with recordsets. Apparently it can, but at this point I still don't understand what's needed.

 

[cns324]

Sorry for the confusion. When I paste rows in sheet 1, it checks for any duplicates in column E on the same sheet, and deletes those. What I would like it to do is after I paste the rows, and any duplicates are deleted on that sheet. Then I would like it to compare column E on sheet1, to column E on sheet3, if it finds a duplicate, then delete the full row on Sheet1.
Then, would like it to compare column E on sheet 1 to column E on sheet 2, and highlight any duplicates it finds in Column E of sheet 1. Thanks for the help.

 

[Micron]

So you have vba code that works (finds and deletes dupes from column E on sheet1) can you not just modify that to select sheet3 and do the same? Is that what you need help with? Then something to start with would help others to help you (if perhaps that is not me). Why don't you post the code that works, assuming you do have that?

 

[cns324]

This is the code I am using to remove the dupes on sheet1:
Sub RemoveDups()
Activesheet.UsedRange.RemoveDuplicates Columns:=5, Header:xlYes

End Sub

If it can be modified to compare sheet 1 column E to Sheet 3 column E and then remove the duplicates from sheet 1, that would be great. I am not sure how to, if anyone else does, I would be very grateful!.

 

[cns324]

As I thought of this more, I realized I can use the same code to remove the dupes on sheet3, I just have to move them from sheet1 to sheet3 first, which is not an issue.
And I was able to get the duplicates highlighted when comparing Sheet1 to Sheet2 by using conditional formatting on sheet 1 with the formula countif(Complete!$E:$E, $E2)
My next goal is to see if I can create a macro that will do the conditional formatting for me.

 

[Micron]

As I thought of this more, I realized I can use the same code to remove the dupes on sheet3, I just have to move them from sheet1 to sheet3

I guess post 4 didn't really resonate for you then?

you wanted code to do this to sheet1 but now you're saying you already have that? You just need to duplicate it for other sheets?

 

[bebo021999]

May be:

Option Explicit
Sub test()
Dim i&, Lr1&, Lr2&, Lr3&, f2, f3
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set ws1 = Sheets("Sheet1"): Set ws2 = Sheets("Sheet2"): Set ws3 = Sheets("Sheet3")
Lr1 = ws1.Cells(Rows.Count, "E").End(xlUp).Row
Lr2 = ws2.Cells(Rows.Count, "E").End(xlUp).Row
Lr3 = ws3.Cells(Rows.Count, "E").End(xlUp).Row
    For i = Lr1 To 2 Step -1
        Set f2 = ws2.Range("E2:E" & Lr3).Find(ws1.Cells(i, "E").Value) ' find duplicate in sheet2
        Set f3 = ws3.Range("E2:E" & Lr3).Find(ws1.Cells(i, "E").Value) ' find duplicate in sheet3
            If Not f3 Is Nothing Then
                ws1.Cells(i, "E").EntireRow.Delete ' find duplicate in sheet3 first, then delete
                Exit For
            ElseIf Not f2 Is Nothing Then
                ws1.Cells(i, "E").Interior.Color = vbRed ' next find duplicate in sheet2, then color
            End If
    Next
End Sub