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

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
61
Office Version
2016
Platform
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:
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,936
Office Version
2016
Platform
Windows
Select column A
Data>Data Tools>Remove Duplicates
Select data that remains, copy, paste underneath
Sort column A
 

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
61
Office Version
2016
Platform
Windows
Select column A
Data>Data Tools>Remove Duplicates
Select data that remains, copy, paste underneath
Sort column A
yes, In case column A is the same, but B and C have different values, how do we do?
 

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
61
Office Version
2016
Platform
Windows
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,010
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
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,936
Office Version
2016
Platform
Windows
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
 

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
61
Office Version
2016
Platform
Windows
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:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,010
your csv has 8686 rows
after remove duplicates from 3 columns together: 8637 rows
is that what you want?
 

Nguyen Anh Dung

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

Watch MrExcel Video

Forum statistics

Threads
1,095,265
Messages
5,443,409
Members
405,234
Latest member
AA90

This Week's Hot Topics

Top