asked on

Delete duplicate Date - both the duplicate and the copy of it

Hello,

I know Excel 07 has a function to delete duplciate data and you can be left with the unique values. But I need something that deletes both the duplicate and the other copy of it.

thank you.

pdvsa

ASKER

so for example

1
1
2
2
3

would leave an answer of 3

jppinto

Here's one method of doing this:

- Add a 2nd column with this formula:

=COUNTIF(A:A,A1) copied down

- Use Paste Special/Values to convert formula results to values

- Sort this new column and then delete all rows that have the value >1

jppinto

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

cool. Does that handle the entire column in A? I see C there so not sure how it is working. but it does work...
Sub RemoveDups()
Columns("C:C").Insert
Bottom = [A1].End(xlDown).Row
Range("C2:C" & Bottom).Select
Selection = "=1/(COUNTIF(R2C1:R" & Bottom & "C2,RC[-2])-1)"
Selection.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
Columns("C:C").Delete
End Sub

jppinto

The code check for the last value on column A (where the values are):

Bottom = [A1].End(xlDown).Row

Then it insert a new column on C to put the formula on that column:

Selection = "=1/(COUNTIF(R2C1:R" & Bottom & "C2,RC[-2])-1)"

Then it will select all the cells on column C that have the value "1" and deletes the rows.

jppinto

pdvsa

ASKER

OH I see... very interesting thahkyou

jppinto

Thanks for the grade...