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
    abc@gmail.com   23
    xyz@yahoo.com   24
    sns@abc.com     75
    abc@gmail.com   51
    lkj@asd.com     85
    abc@gmail.com   95
    xyz@yahoo.com   52
    uhk@asj.com     95
    uhk@asj.com     42

Expected Result Sheet

Email           Value
abc@gmail.com   23
xyz@yahoo.com   24
sns@abc.com     75
abc@gmail.com   51
lkj@asd.com     85
xyz@yahoo.com   52
uhk@asj.com     95
uhk@asj.com     42

Note that "abc@gmail.com 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 "abc@gmail.com" 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