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

#### Nguyen Anh Dung

##### Board Regular
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

### 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
Select column A
Data>Data Tools>Remove Duplicates
Select data that remains, copy, paste underneath
Sort column A

#### Nguyen Anh Dung

##### Board Regular
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?

#### footoo

##### Well-known Member
yes, In case column A is the same, but B and C have different values, how do we do?
Your sample data only showed column A.

What do you want to happen with columns B & C ?

#### Nguyen Anh Dung

##### Board Regular
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

#### sandy666

##### Well-known Member
only these values are duplicated
 Date/time Latitude Longitude 18/03/2020 03:20:07 10.8179718 106.6320324 18/03/2020 03:20:07 10.8179728 106.6320376 18/03/2020 03:20:07 10.8179742 106.6320433 18/03/2020 03:20:07 10.8179754 106.6320498 18/03/2020 03:20:08 10.8179572 106.6319321 18/03/2020 03:20:08 10.817959 106.6319386 18/03/2020 03:20:08 10.8179596 106.6319564 18/03/2020 03:20:08 10.8179596 106.6319605 18/03/2020 03:20:08 10.8179596 106.6319647 18/03/2020 03:20:08 10.8179601 106.6319529 18/03/2020 03:20:08 10.8179603 106.6319492 18/03/2020 03:20:08 10.8179604 106.6319451 18/03/2020 03:20:08 10.8179605 106.6319707 18/03/2020 03:20:08 10.8179615 106.6319773 18/03/2020 03:20:08 10.8179622 106.6319828 18/03/2020 03:20:08 10.8179628 106.6319879 18/03/2020 03:20:08 10.8179635 106.6319932 18/03/2020 03:20:08 10.8179641 106.6319983 18/03/2020 03:20:08 10.8179651 106.6320041 18/03/2020 03:20:08 10.8179666 106.6320101 18/03/2020 03:20:08 10.8179681 106.6320159 18/03/2020 03:20:08 10.8179699 106.6320222 18/03/2020 03:20:08 10.817971 106.6320275

so what you want to achieve?
Longitude doesn't contain duplicates

#### footoo

##### Well-known Member
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
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?

Last edited:

#### sandy666

##### Well-known Member
after remove duplicates from 3 columns together: 8637 rows
is that what you want?

#### Nguyen Anh Dung

##### Board Regular
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.