remove duplicate if cell appears twice macro

[daveyc18]

the removed duplicates function because other columns are different

let's assume the duplicated value appears in column A

i'd like the macro to delete only ONE of the duplicates

...

i have a helper column in Y that does a countif and this code

    finalrow = Cells(Rows.Count, "A").End(xlUp).Row


For i = finalrow To 2 Step -1

If Range("y" & i) > 1 Then
Range("y" & i).EntireRow.Delete
Exit Sub
End If
Next i

but the issue with this code is that it would stop once it finds the any row with 2 and there could be multiple rows with duplicate values in column A (this code only works if there's only two rows of data with duplicates, but, not, say, 4)

 

[Jeffrey Mahoney]

Please try this:

I'm hoping that your helper column is not putting a total count next to each occurrence of the value. Your formula needs to be anchored so that it only counts the value one time. like:
=COUNTIF(A$2:A2,A2) and then copied down

The reason your original code doesn't work is because rows are removed within the range you were trying to evaluate. This code removes the rows after all are evaluated.

Sub RemoveDups()

  Dim FinalRow As Long
  Dim u As Range
  Dim Cel As Range
  Dim Rng As Range
  Dim i As Range
  
  Set Rng = Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp))

  For Each Cel In Rng
    If Cells(Cel.Row, 25).Value > 1 Then
      If Not u Is Nothing Then
        Set u = Union(u, Cel)
      Else
        Set u = Cel
      End If
    End If
  Next Cel
  If Not u Is Nothing Then u.EntireRow.Delete
  

End Sub

 

[Jeffrey Mahoney]

Please see that I edited the above code after posting.

 

[daveyc18]

Please see that I edited the above code after posting.

tks for the reply

i just tested it, but unfortunately both duplicates get removed, even after adjusting for the countif formula. i would like to leave one of them.

 

[Joe4]

I think it might be a bit clear if you could show us an example of your data and expected result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[daveyc18]

I think it might be a bit clear if you could show us an example of your data and expected result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

SAMPLE.xlsx

Sheet1 Report_Date,Entity,Cpty_Minor,Ticket, LV, Q, mvLC, MV,LR,FR 20240202,TOR,SBHS,27944038, 137,856 , 1,436 , 132,227 , 132,227 ,5.28,1.345749994 20240202,TOR,SBHS,28135398, 192,000 , 2,000 , 184,160 , 184,160 ,5.18,1.345749994 20240202,TOR,USMB,27943746, 268,800 , 2,800 , 257,824 ...

docs.google.com

the highlighted cells are duplicated rows ...what's different is the LR column so that's why the built in removed duplicates function doesn't work

but the LR column isn't needed so i don't care which "duplicate" gets removed

tbh i think the duplicated rows will probably always be adjacent to each other, which would then make the code relatively straightforward

but, ofc it's not guaranteed so looking for a code that's more dynamic

 

[daveyc18]

anybody ?

 

[dermie_72]

With the data in Column Y is it hard coded or formula driven? If it's hard coded it's going to remove both duplicates.
If it's formula driven, the by removing Exit Sub from your macro it will continue until it has done the whole range.

 

[dermie_72]

For i = finalrow To 2 Step -1
If application.worksheetfunction.countif(Range("A:A"), Range("A" & i) > 1 Then
    Range("A" & i).EntireRow.Delete
End If

If the range that has the duplicates is in Column A, then this adjustment to your code should remove the duplicates without having to check column Y

 

[daveyc18]

For i = finalrow To 2 Step -1
If application.worksheetfunction.countif(Range("A:A"), Range("A" & i) > 1 Then
    Range("A" & i).EntireRow.Delete
End If

If the range that has the duplicates is in Column A, then this adjustment to your code should remove the duplicates without having to check column Y

here is the data with column D with the duplicates

i have shortened it without the helper column in Y

the highlighted cells are duplicated rows ...what's different is the LR column so that's why the built in removed duplicates function doesn't work

but the LR column isn't needed so i don't care which "duplicate" gets removed

tbh i think the duplicated rows will probably always be adjacent to each other

but, ofc it's not guaranteed so looking for a code that's more dynamic

SAMPLE.xlsx

Sheet1 Report_Date,Entity,Cpty_Minor,Ticket, LV, Q, mvLC, MV,LR,FR 20240202,TOR,SBHS,27944038, 137,856 , 1,436 , 132,227 , 132,227 ,5.28,1.345749994 20240202,TOR,SBHS,28135398, 192,000 , 2,000 , 184,160 , 184,160 ,5.18,1.345749994 20240202,TOR,USMB,27943746, 268,800 , 2,800 , 257,824 ...

docs.google.com