Delete Duplicates in Multiple Cells & Multiple Columns

Hi Everyone,

I'm new here and I want to say thanks to everyone who might be able to help me with this, I'm no Excel expert and can't seem to figure out how to create a macro or a code to deal with this problem. I have a sheet with multiple rows and columns A through K. The cells in column A is unique, but all rows may have duplicates in multiple cells and columns. For ex. B11, D11, E11, F11 are all duplicate phone numbers. Is there a way to delete the duplicates with a code? I have tried the automatic "Remove Duplicates" but since the entire rows are not looking the same it won't work. I want to be able to highlight the duplicate values in each cell with for ex. "Conditional Formatting" and then delete all but 1 unique value.

Not sure if I'm making any sense, but I have looked everywhere for a solution, and this is my last stop in trying to fix this.

Any advise is appreciated.

Thanks!

Hello jennysoderquist,

Welcome to the forum!

For us to answer your questions quickly and correctly, you should post a copy of the workbook you are using.

Before you post, please either redact or change any information in your copy that may be confidential or sensitive.


How to Post your Workbook

1. At the bottom of your post, click the button GO ADVANCED.
2. When the page reloads, look for the section titled Additional Options
3. Look for the heading Manage Attachments: and click on the link Manage Attachments
4. The page will launch the uploader and open a new window.
5. Click the button Choose File in the upper left corner.
6. Select the file you want to upload.
7. Click the button Upload at the far right.
8. When the upload is complete, close the window.

Hello jennysoderquist,

The following macro has been added to the attached workbook and has been tested.

Remove Duplicates Macro Code

Please Login or Register  to view this content.

Hello Jenny,

This error occurs with the Mac Office version. It does not allow VBA to import other libraries. The library in this case is the Scripting Runtime library of which the Dictionary is part.

I use the Dictionary more than a Collection object. The both work basically the same but the Dictionary has other features that the Collection does not have. These additional features make coding more complex data handling much easier.

Anyway, here is the workaround using a Collection object. Delete the old the macro then copy and paste this one in it's place.

Please Login or Register  to view this content.

Wow stupid question (my own I mean, which I just removed here), ignore me!

Hello Jenny,

You're welcome. I should have noticed you were using Office 2011 the first time I answered.