Deleting duplicate rows

**khenneous** · 07-26-2012, 04:53 PM

The issue I'm facing in the attached file is this. I need a VBA Macro that first, checks to see if there is any duplicate data that matches column A AND column B. I.E. If the last name is the same, is the first name ALSO the same. If so, keep going down the list to identify ALL rows that have duplicate data in column A AND column B (this could be as few as 2 rows, or many more). Next it needs to search column E for ONLY the rows that A AND B have duplicates in. If it finds ANY occurrence of the word "No", it deletes all other duplicate rows (leaving me with one row and the word "No" is still resident in column E. If it doesn't find any occurrence of the word "No" in column E, it simply deletes all rows except for one. This is quite confusing to me on how one goes about completing this with a VBA Macro. I am somewhat familiar with macros and am already using a few to manipulate data on other spreadsheets, but this one is just getting the better of me.

Any help is greatly appreciated!!

**VBA FTW** · 07-26-2012, 06:10 PM

Question: would using a formula to combine columns A and B pose a problem for you? I think I can figure this out if combining the 2 columns is ok, maybe even throw them onto a new sheet in case that does indeed pose a problem for you

**khenneous** · 07-26-2012, 07:11 PM

If I had a good formula, I could probably convert to a macro. As for combining the columns, I don't see a problem with that either. Thanks so much!!

**cathchin** · 07-27-2012, 05:09 AM

Hi,

Please refer to the attached, I've created a simple macro but please ensure you must name your Sheet as "List" in order to run the macro. If you like my macro, please give me a star at the right hand corner of this post.

Thanks and have a great day!

Rgds,
Catherine

**Marcol** · 07-27-2012, 07:06 AM

Provided your data is sorted as per your sample, try this ...

In G2 (Helper column, hide with Grouping button) Drag down as required.

Formula:

Please Login or Register  to view this content.

Then in H2 Drag Across to K2, Then Down until a blank row is returned.

Formula:

Please Login or Register  to view this content.

In L2 Drag down as required.

Formula:

Please Login or Register  to view this content.

Sorry can't post a sample workbook, but will do once the "Tech Team" gets its act together, that might be some little while ...

[EDIT]
Must have been that nasty guy "Spike" again. Wait 'til the "Tech Team" get their hands on him ...

Here's the attachment.

**khenneous** · 07-27-2012, 03:36 PM

Thanks so much Catherine. That works like a champ!!! I have added information to your reputation.

---------- Post added at 02:36 PM ---------- Previous post was at 01:09 PM ----------

Marcol,

This option also works, however, every time a new member is added, you have to re-drag the help row. The macro Catherine provided does it automatically as part of the whole data collection process. Thanks though!!

**Marcol** · 07-27-2012, 09:52 PM

Before you start, drag the helper as far as you might reasonably expect your data to go, it wont eat you out of house and home.

I only offered this as a solution using native formula, often macros are prohibited by the end user, and indeed some proud owners of macs don't have access to VBa ...

Native formula often seems to be an endangered species ...

Happy to have helped ...