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

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
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
1585376962735.png

link file:
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Select column A
Data>Data Tools>Remove Duplicates
Select data that remains, copy, paste underneath
Sort column A
 
Upvote 0
as picture below: Remove Duplicates column A and keep only 2 rows the same, but columns B and C have different values that do not overlap.
columns B and C retain the random values

example:
A-Date/time B-Latitude C-Longitude
2020-03-18 03:20:07 10.8179754 106.6320498
2020-03-18 03:20:07 10.8179742 106.6320433
2020-03-18 03:20:08 10.817971 106.6320275
2020-03-18 03:20:08 10.8179699 106.6320222
2020-03-18 03:20:09 10.8179575 106.6319162
2020-03-18 03:20:09 10.8179571 106.6319106


1585379330323.png
 
Upvote 0
only these values are duplicated
Date/timeLatitudeLongitude
18/03/2020 03:20:0710.8179718106.6320324
18/03/2020 03:20:0710.8179728106.6320376
18/03/2020 03:20:0710.8179742106.6320433
18/03/2020 03:20:0710.8179754106.6320498
18/03/2020 03:20:0810.8179572106.6319321
18/03/2020 03:20:0810.817959106.6319386
18/03/2020 03:20:0810.8179596106.6319564
18/03/2020 03:20:0810.8179596106.6319605
18/03/2020 03:20:0810.8179596106.6319647
18/03/2020 03:20:0810.8179601106.6319529
18/03/2020 03:20:0810.8179603106.6319492
18/03/2020 03:20:0810.8179604106.6319451
18/03/2020 03:20:0810.8179605106.6319707
18/03/2020 03:20:0810.8179615106.6319773
18/03/2020 03:20:0810.8179622106.6319828
18/03/2020 03:20:0810.8179628106.6319879
18/03/2020 03:20:0810.8179635106.6319932
18/03/2020 03:20:0810.8179641106.6319983
18/03/2020 03:20:0810.8179651106.6320041
18/03/2020 03:20:0810.8179666106.6320101
18/03/2020 03:20:0810.8179681106.6320159
18/03/2020 03:20:0810.8179699106.6320222
18/03/2020 03:20:0810.817971106.6320275

so what you want to achieve?
Longitude doesn't contain duplicates
 
Upvote 0
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
 
Upvote 0
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
not correct. you can help me vba?
link file: test.csv
 
Last edited:
Upvote 0
your csv has 8686 rows
after remove duplicates from 3 columns together: 8637 rows
is that what you want?
 
Upvote 0
after remove duplicates from 3 columns together: 962
8686 remove duplicates=>481 rows. if keep only 2 rows the same, but columns B and C have different values that do not overlap 962 rows.
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top