• Status: Solved
• Priority: Medium
• Security: Public
• Views: 228

# 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.
0
pdvsa
• 4
• 3
1 Solution

Project financeAuthor Commented:
so for example

1
1
2
2
3

would leave an answer of 3
0

Commented:
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

0

Commented:
You can make it using a macro like the attached example.

jppinto
RemoveDuplicates.xlsm
0

Project financeAuthor Commented:
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
0

Commented:
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
0

Project financeAuthor Commented:
OH I see... very interesting thahkyou
0

Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.