Help Excel formula eliminate duplicate values and keep only 2 identical rows.

[Nguyen Anh Dung]

as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9 rows,etc.... How to delete duplicate values and keep only 2 rows

link file:

1.csv

drive.google.com

 

[Nguyen Anh Dung]

Enter in D2 and fill down to last data row : =IF(COUNTIF(A$2:A2,A2)>2,"d",1)
Select column D and go to SpecialCells>Formulas>Text
Delete>EntireRow
Clear column D

Can you help me with VBA code?

 

[footoo]

Sub v()
Dim rng As Range: Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(3).Row)
With rng.Offset(0, 3)
    .Formula = "=IF(COUNTIF(A$2:A2,A2)>2,""d"",1)"
    .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
    .ClearContents
End With
End Sub

 

[Nguyen Anh Dung]

VBA Code:
Sub v()
Dim rng As Range: Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(3).Row)
With rng.Offset(0, 3)
.Formula = "=IF(COUNTIF(A$2:A2,A2)>2,""d"",1)"
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
.ClearContents
End With
End Sub

Thanks you so much!!! you can help me for this case run multiple file in folder!!!

 

[footoo]

VBA Code:
Sub v()
Dim rng As Range: Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(3).Row)
With rng.Offset(0, 3)
.Formula = "=IF(COUNTIF(A$2:A2,A2)>2,""d"",1)"
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
.ClearContents
End With
End Sub

Thanks you so much!!! you can help me for this case run multiple file in folder!!!

Since this is a different topic, it's better that you start a new thread.

 

[Nguyen Anh Dung]

In your CSV, what are all those 1's in column D ?
You must have entered the formula incorrectly - please note the absolute row ref (A$2):
=IF(COUNTIF(A$2:A2,A2)>2,"d",1)

Thank you and best regards, I have more question: if keep only 2 rows the same time, but getting the first and the last row?

 

[footoo]

Assuming column A is in sequence :

Sub v()
With Range("D2:D" & Cells(Rows.Count, "A").End(3).Row)
    .Formula = "=IF((A2=A1)*(A2=A3),""d"",1)"
    .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
    .ClearContents
End With
End Sub

 

[Nguyen Anh Dung]

t

Assuming column A is in sequence :

Sub v()
With Range("D2:D" & Cells(Rows.Count, "A").End(3).Row)
    .Formula = "=IF((A2=A1)*(A2=A3),""d"",1)"
    .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
    .ClearContents
End With
End Sub

thanks you!!!

 

[sandy666]

just for fun with Power Query

first two duplicates from each group (part of data)

Index	Date/time	Latitude	Longitude
1	2020-03-18 03:28:10	10.8208494	106.6307964
2	2020-03-18 03:28:10	10.8208495	106.6307963
13	2020-03-18 03:28:11	10.8208499	106.6307958
14	2020-03-18 03:28:11	10.82085	106.6307957
32	2020-03-18 03:28:12	10.820851	106.630795
33	2020-03-18 03:28:12	10.8208511	106.6307951
48	2020-03-18 03:28:13	10.8208515	106.6307952
49	2020-03-18 03:28:13	10.8208515	106.6307952
64	2020-03-18 03:28:14	10.8208514	106.6307956
65	2020-03-18 03:28:14	10.8208514	106.6307956
82	2020-03-18 03:28:15	10.820852	106.6307959
83	2020-03-18 03:28:15	10.8208519	106.630796

first and middle duplicates from each group (part of data)

Index	Date/time	Latitude	Longitude
1	2020-03-18 03:28:10	10.8208494	106.6307964
6	2020-03-18 03:28:10	10.8208496	106.6307961
13	2020-03-18 03:28:11	10.8208499	106.6307958
21	2020-03-18 03:28:11	10.8208502	106.6307954
32	2020-03-18 03:28:12	10.820851	106.630795
39	2020-03-18 03:28:12	10.8208514	106.630795
48	2020-03-18 03:28:13	10.8208515	106.6307952
55	2020-03-18 03:28:13	10.8208513	106.6307955
64	2020-03-18 03:28:14	10.8208514	106.6307956
72	2020-03-18 03:28:14	10.820852	106.6307958
82	2020-03-18 03:28:15	10.820852	106.6307959
89	2020-03-18 03:28:15	10.8208528	106.6307964

index visualizing only which records were used

 

[Nguyen Anh Dung]

Assuming column A is in sequence :

Sub v()
With Range("D2:D" & Cells(Rows.Count, "A").End(3).Row)
    .Formula = "=IF((A2=A1)*(A2=A3),""d"",1)"
    .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
    .ClearContents
End With
End Sub

Hi footoo,
In this case I have 16 rows or 18 rows or 19 rows duplicate: if keep only 5 rows the same time, but getting the first and four, eight,
twelve and the last row?

 

[footoo]

Sub v()
With Range("D2:D" & Cells(Rows.Count, "A").End(3).Row)
    .Formula = "=IF((A2<>A1)+(COUNTIF(A$2:A2,A2)=4)+(COUNTIF(A$2:A2,A2)=8)+(COUNTIF(A$2:A2,A2)=12)+(A2<>A3),""k"",1)"
    .SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
    .ClearContents
End With
End Sub