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?
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.