Delete Unique Records in Excel?
I find it impossible to do the following in Excel.
I have a list of items as follows:
A
A
B
C
C
D
I would like to leave only those items in the list that are listed more than once. So the desired end result would be:
A
A
C
C
From this list, I could then easily delete the duplicates, which would leave:
A
C
Background: I have three lists with email addresses from an email marketing campaign. List 1 contains addresses that bounced in Month 1, List 1 in Month 2, and List 3 in Month 3. I merged the three lists, and I would like a final list that contains addresses that bounced in at least two of the three months. I will then delete these addresses from our database.
merging them into 1 column made it easy.
asssume you merged your list in column A
then in B1 copy&paste this formula
=IF(COUNTIF(A:A,A1)>1, IF(COUNTIF($A$1:A1,A1)=1,A1,”"),”")
then copy&paste the formula down the column.
you can autofilter column B or sort by column B and delete out the ones you want.
that will get the
A
C
feel free to update the question or email me if something isnt working.
edit-
this might work better in B1
=IF(COUNTIF(A:A,A1)>1,A1,”")
copy down and you will get a list of what to delete.
in other words your result
A
A
C
C
There are different ways to fix your email marketing list. Here is what you can do:
Let’s say you have Column A in Excel with title “Name” in cell A1 and your data starting in A2. Click in cell A2 and sort them as they are in your example (A, A, B, C, C, D).
In Column B – cell B2 enter this formula: =IF(A2=A3,”Yes”,”No”) and copy the formula in the entire Column B – as long as you have data in Column A.
Now you can filter the results in column B and select and delete all “No” rows.
Example excel file to fix your email marketing list:
Let me know if you have any questions.