0

Excel has function to remove duplicates but what if i want only first 2 records of each value in a column.

E.g. Sheet

    Email           Value
    [email protected]   23
    [email protected]   24
    [email protected]     75
    [email protected]   51
    [email protected]     85
    [email protected]   95
    [email protected]   52
    [email protected]     95
    [email protected]     42

Expected Result Sheet

Email           Value
[email protected]   23
[email protected]   24
[email protected]     75
[email protected]   51
[email protected]     85
[email protected]   52
[email protected]     95
[email protected]     42

Note that "[email protected] 95" record which was third entry for the email id was removed. All other records has either one or two entries. No record is allowed for more than two entries. I have thousands of these in a sheet and need to only have first two entries of each email. Can anyone tell me how i can i delete more than 2 records as in above example?

data-bite
  • 417
  • 2
  • 5
  • 17
  • Use the [COUNTIF function](https://support.office.com/en-US/article/COUNTIF-function-E0DE10C6-F885-4E71-ABB4-1F464816DF34) in a 'helper' column. Filter on that for 3 or greater and delete. –  May 10 '16 at 07:55

1 Answers1

2

create a helper column with the following formula:

Assuming "[email protected]" is in cell A2, enter this formula in cell C2

=Countif($A$2:A2,A2)>2

This will result in a TRUE/FALSE value. Copy/Fill down the formula to the bottom of your list.

Since this formula returns TRUE for the first two instances of the email address, all of your FALSE values can be deleted from your worksheet.

06chakin
  • 71
  • 5
  • The requirement is to check max 2 entries for every email-id. That is there can be other email-id's which have more than 2 entries/records. We are suppose to take only first two records of all the emial-id's. The solution you suggested works for only 1 email-id in particular. – data-bite May 11 '16 at 09:33