Duplicates

cns324

New Member
Joined
Jan 21, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,770
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 21, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.
DataCommentsRequestedAppReq #Days inReq DateExpire DateNameReq NameAssignedApprovalCompleted
12/28/2021xxxxxxreq10001xxxxxxxxxxxxxxxx
1/3/2022xxxxxxreq10002xxxxxxxxxxxxxxxx
1/8/2022xxxxxxreq10003xxxxxxxxxxxxxxxx
1/14/2022xxxxxxreq10004xxxxxxxxxxxxxxxx
1/18/2022xxxxxxreq10005xxxxxxxxxxxxxxxx
1/19/2022xxxxxxreq10006xxxxxxxxxxxxxxxx
1/19/2022xxxxxxreq10007xxxxxxxxxxxxxxxx
1/22/2022xxxxxxreq10008xxxxxxxxxxxxxxxx
1/24/2022xxxxxxreq10009xxxxxxxxxxxxxxxx
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,770
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 21, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
3,770
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 21, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 21, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
3,770
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2011
Messages
1,799
Office Version
  1. 2016
May be:
VBA Code:
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
 

Forum statistics

Threads
1,176,013
Messages
5,900,883
Members
434,857
Latest member
lowiscoetzee

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
Top