Macro - Deleting Duplicate Cell Content

[Gessen]

Hi Guys,

Conceptually this doesn't sound too crazy to me, I just don't know VBA. So I could really use your help! I need a Macro to delete duplicate cell content (String) within a column section, until it hits an empty ("") cell, with a starting cell based off of a specified column and row number. Can anyone help me put something together? I've got a sheet that pulls a lot of data from other sheets and creates some duplicate content. I just want to be able to clean them up. The duplicate content might not be one after another. The duplicate may be at the opposite end of the list. Thanks for your help!

-Gessen

 

[Smitty]

Welcome to the Board!

Can you post an example? Check the HTML Maker link in my sig, which will let you post a shot of your sheet.

 

[Gessen]

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>AU</TH><TH>AX</TH><TH>BA</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1014</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">All Pro (27)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Adora/Charisma Dolls (45)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">A Parent Company</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1015</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Black Mountain Products (9)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Advanced Graphics (40) (U)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Action Baby Carriers (5)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1016</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Cemco Strength</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Ashley Entertainment (17)(U)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">ARPillow (5)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1017</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">CrossCore (3)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">baby Abuelita (5)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Baby Be Mine</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1018</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">EZ-Goal (26)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Best Pal (10)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Baby Hip Hugger (10)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1019</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Flexcords</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Boot Scoot Bike (10)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Baby Smart (13)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1020</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Lube-N-Walk (5)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">FA System dba (10)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Bathing Buddies (shower sling)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1021</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Sequoia Fitness (3)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Fashion Angels (40)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">BooginHead (20)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1022</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Stud Bar (3)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">The Happy Kid Company (6)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Daisy Hugs (10)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1023</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Ironcompany.com (41)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Geared for Imagination (46)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Guardian Angel (5)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1024</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Geospace (20)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Ihosa Baby (10)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1025</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Greenline Bicyles (95) (U)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Lil' Bowtique & Co (30)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1026</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Hornby (20)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">LPB Showroom (50)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1027</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Janlynn Corp (100)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Maison Chic (50)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1028</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Kumotek (40)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Noelle Dass (20)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1029</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Trial Rover (7)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Peek Away (10)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1030</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Maranda Enterprises (35)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Poopy Doo (10)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1031</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Ohio Art Company (20)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Posh Petals (20)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1032</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Outdoor Living Today (20)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Rockin Baby Sling (5)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1033</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Recent Toys (10)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Splendipity (20)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1034</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Senario (20)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Tambino (50)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1035</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Sensory University (10)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Youzzo</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1036</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Sport Oh (30)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Peek Away (10)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1037</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Stiga products (30)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Poopy Doo (10)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1038</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">The Finke Co (30)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Posh Petals (20)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1039</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Walachia (10)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Rockin Baby Sling (5)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1040</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Thames & Kosmos (11) (U)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Splendipity (20)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1041</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Worldwise Imports (690)</TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Tambino (50)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1042</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Vinci (2)</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1043</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">BCW (1)</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1044</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Veggie Take Toys (8) (U)</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1045</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Learning Journey</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1046</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Knex</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1047</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Pulse Scooters (2) (U)</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1048</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Eitech(72)</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1049</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD><TD style="BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold">Teifoc (10)</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; FONT-WEIGHT: bold"></TD></TR></TBODY></TABLE>

Master Buying Sheet​

 

[Smitty]

In the example what should be deleted?

 

[Gessen]

In this case, Tambino, in column BA, shows up twice. I want to be able to start a loop beginning at Cell BA(1014), scans for duplicates until it hits an empty cell, and then cleasr the duplicate cell closest to the bottom of the list. Does that make sense?

 

[Smitty]

If you have Excel 2007+ there's a Remove Duplicates Wizard on the Data tab. You just need to tell it to only focus on column BA and not expand the range, which it will want to do.

 

[Gessen]

My problem is I need to do this for a huge sheet. Each column has three different sections I need to check, and I feel like it will take to long to check every cell so I'd like to be able to section off portions of the sheet. I also already have a macro attached to a button that auto-populates the sheet. I'd like to add this cleanup macro to the end so that after it auto populates, it gets rid of duplicates.

 

[Smitty]

Just record a macro selecting each section then invoking the delete dupes wizard.

If you post what you get back here someone can clean it up for you.

Will the individual sections always be consistent?

 

[Gessen]

The 'Starting' cell will always be accurate, but the length until you hit an empty cell will fluctuate.

That part's easy to address.

The reason for you do record a macro is that it will capture all of your ranges, and helps someone adjust the code specific to your situation. Otherwise all someone can give you is samples that would most likely need adjusting on your end.

 

[Gessen]

I'm getting an error using the remove duplicate wizard, about the merged cell sizes needing to be the same. Would it be possible to come up with a function that just iterates through the each item and checks the entire section for a duplicate. I know it's inefficient, but it will probably work for my needs. These lists aren't going to be too long, so hopefully the calculation time won't be huge, or I can have a seperate macro for each column.