Solved

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

Posted on 2011-03-09
213 Views
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
Question by:pdvsa
• 4
• 3

Author Comment

so for example

1
1
2
2
3

would leave an answer of 3
0

LVL 33

Expert Comment

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

LVL 33

Accepted Solution

jppinto earned 500 total points
You can make it using a macro like the attached example.

jppinto
RemoveDuplicates.xlsm
0

Author Comment

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

LVL 33

Expert Comment

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

Author Comment

OH I see... very interesting thahkyou
0

LVL 33

Expert Comment

0

## Featured Post

### Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…