Excel 2007 : Deleting duplicates as well as associated originals

[dip11]

Hi,

How would I go about deleting duplicates in such a fashion, that it also deletes the original values which matched the duplicates and leave uniques only?

To illustrate:

AAA
BBB
AAA
CCC
BBB

Should after removing duplicates leave only CCC instead of AAA, BBB, CCC

Example:
Check in column B, D, E. If the values all match, delete both originating rows

[GeneralDisarray]

do you mean that want the combined contents of B,D, and E to be compared? please clarify with an attached example sheet, the process is easy with such an example.

[dip11]

Here you go

Check Columns B,C,D,E. If they match in more than 1 row, delete all rows with that feature.

Only 18 of the 27 rows would remain

[GeneralDisarray]

Looks like this does the trick.

Please Login or Register  to view this content.

--EDIT--

Noticed an error when the repeated row went all the way to the top, corrected the logic of the delete statment to not fail.

[dip11]

Hi,

Thanks for the code, but it seems to run into problems.
The Macro1 section always returns an error when I try to run it saying "Variable not defined", highlighting "Sub Macro1()"

When I import the example, it also does not delete the two 7912L entries though it deals with the others just fine.

I am running it inside the workbook you uploaded

When I then modify your macro for my actual large data (replacing B with A, C with B, D with AA and E with AB and increasing the max from 27 to 13412), the general disarray sorts them so that duplicates are all next to each other but does not delete them at all.

This may be because the duplicate information is not entirely complete: The entries in the 4 columns marked for comparison are always there, but contents of other columns vary but should be ignored as I want the rows deleted and only those 4 columns compared to determine if they are identical.

I have attached a new example workbook. I have hidden all the irrelevant columns, but if you unhide them (they look daunting!) it gives you an idea how a lot of thinks are non dup, dup and blank and that's why they shouldn't be considered in the criteria.

While the new example only has short of 100 rows total, the 13413 is the actual row count - I can edit that into the macro.

[GeneralDisarray]

Hi,
When I then modify your macro for my actual large data (replacing B with A, C with B, D with AA and E with AB and increasing the max from 27 to 13412), the general disarray sorts them so that duplicates are all next to each other but does not delete them at all.

Yeah, that's a huge difference. I had the thing compare columns 2,3,4,5....can change it to compare 1,2,27,28 also, i was stepping backwards in the loop...change the i=j to i=j+1 and the thing seems to work on your example. WHen you run the general disarray macro on what you just posted, i'm left with just 3 rows.

Please Login or Register  to view this content.

[dip11]

Worked brilliantly - at first I thought excel was having a fit, but the result was exactly what I wanted, thank you very much