Excel – remove duplicates

Topic

New Reply

I hope all are well.

What is the “easiest” way to remove duplicates in Excel?

A macro, formula or even an application would be nice.

I manage a newsletter mailing list (emails) and it can be an absolute nightmare. Need a better way.

Cannot afford a monthly or very expensive option.

TIA – BJ

Reply | Quote

Replies

What sort of duplicates ? One column or many ?

One quick and easy way to remove duplicates is to sort the data and either

1. Do a visual inspection for duplicates as they will now be adjacent
2. Add a column with an IF statement testing if the two entries on the same row and the one below are the same, thus finding any duplicates

Do you have a sample workbook ?

Reply | Quote

Hope all is well; appreciate.

Actually I think I didn’t explain properly; I try here; issue here is to deal with “removal” of emails (we have a few hundred emails and need to remove multiple emails at one time. Or if with some automation can also work well.

Attached a sample – my challenge is to manage a few hundred emails and bulk compares.

• Column A – the exemplar has 3 emails of people who want to unsubscribe
• Column B – has the actual mailing list

We want to compare Column A with B and “delete” these 3 automatically somehow.

Thank you.

Reply | Quote

Hi,

I’ve attached V2 of your sample workbook that goes some way towards your goal.

It has a macro RemoveUnwantedEmailAddresses which deletes any addresses in your unsubscribe list.

It will deal with any number of addresses and unsubscribe requirements as long as there are no gaps in either list

Note that I have given two of the worksheet cells Names – these Names must be present for the macro to run. You can see the Names at the top left of the worksheet when you click on either cell:

A2:  “UnsubscribeListStart”

B2: “SubscriptionListStart”

If you want to close-up the blanks that the macro produces it may be simplest to to that as follows:

1. Select the range from which you want to remove blanks
2. Press F5 and click the Special button
3. In the Go To Special dialog box, select Blanks and click OK
4. Right-click any one of the selected blanks, and choose Delete from the context menu:

Hope this helps

Reply | Quote

Appreciate very much; I think the attachment is missing?

Ben

Reply | Quote

I thought I had attached it !
Here it is . . .

Reply | Quote

I think still missing 👍

Reply | Quote

Yes, I’m not being allowed to attached it, despite it being in the list of allowed file types !

Never mind, let’s try it this way:

1. Open the workbook you sent me and save it as a Macro-enabled Workbook – file type .xlsm
2. Close it and then re-open it
3. Click on cell A2 and in the box at the top left (which should be displaying A2), type UnsubscribeListStart and press return
4. Click on cell B2 and in the box at the top left (which should be displaying B2), type SubscriptionListStart and press return
5. Create a new Macro: Press ALT and F11
6. Double click on ThisWorkbook at the left
7. Copy the bold text below into the blank screen which opens, then close the whole window
8. Press ALT and F8 to bring up the list of macros.
9. Pick RemoveUnwantedEmailAddresses() and click on Run
10. Now your duplicates should be gone, replaced by blank cells

Sub RemoveUnwantedEmailAddresses()

Dim UnsubscribeListRow As Integer ‘To track which address is being unsuscribed
Dim SubscriberListRow As Integer ‘To track where we are in the list of subscribers’ emails

UnsuscribeListRow = 0 ‘Initialise

Do While Range(“UnsubscribeListStart”).Offset(UnsubscribeListRow, 0).Value <> “” ‘Keep going while there are addresses to unsuscribe

SubscriberListRow = 0

Do While Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value <> “” ‘Keep going while there are addresses to unsuscribe

‘Test for a duplicate valee

If Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value = Range(“UnsubscribeListStart”).Offset(UnsubscribeListRow, 0).Value Then

Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value = “Deleted” ‘Remove the duplicate

End If

SubscriberListRow = SubscriberListRow + 1

Loop

UnsubscribeListRow = UnsubscribeListRow + 1

Loop

‘Now removed any “Deleted” values

SubscriberListRow = 0

Do While Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value <> “” ‘Run through the email list once

‘Test for a “Deleted” valee

If Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value = “Deleted” Then

Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value = “” ‘Remove “Deleted” value

End If

SubscriberListRow = SubscriberListRow + 1

Loop

End Sub

Reply | Quote

Susan here – try changing the file name to .renamemetoxlsm and see if it let’s you upload?

Susan Bradley Patch Lady

1 user thanked author for this post.

WSMartinM

Reply | Quote

Susan,

That didn’t work, neither did .docx 🙁

Reply | Quote

Here we go again – if this works, simply rename the file from .docx to .

xlsm

Fingers crossed !

Reply | Quote

Trying again !

Rename this file from .pdf to .xlsm

Reply | Quote

many sites have issues that block uploads. Here is a workaround that works for most.

• create a zip file of the xlsm.
  • IE work.xlsm created to work.zip
• rename a zip file of the pdf.
  • IE work.zip created to work.pdf
• create another zip and than upload
  • IE work.pdf create a zip

The person than has to do things backwards  in list above to get the file back. A long process but it has 99% chance of working. It worked on all site that I use that block file types.

Reply | Quote