Solved

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

Posted on 2011-03-09
7
213 Views
Last Modified: 2012-08-13
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
Comment
Question by:pdvsa
  • 4
  • 3
7 Comments
 

Author Comment

by:pdvsa
Comment Utility
so for example

1
1
2
2
3

would leave an answer of 3
0
 
LVL 33

Expert Comment

by:jppinto
Comment Utility
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

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

jppinto
RemoveDuplicates.xlsm
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:pdvsa
Comment Utility
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

by:jppinto
Comment Utility
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

by:pdvsa
Comment Utility
OH I see... very interesting thahkyou
0
 
LVL 33

Expert Comment

by:jppinto
Comment Utility
Thanks for the grade...
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now