macro - Find duplicate in column - delete line

[skyport]

Hi everyone.

What I have is a situation where data is inputted into columns A to G. Each line of data will include a telephone number in Column D.

If possible, I would like to have a macro that searches the column D and when it finds a duplicate telephone number, it will delete the entire rows that contain the duplicate telephone numbers so those lines are completely gone leaving only the one line where that telephone number first appears in column D along with leaving of course all lines where that tel number was never listed in the first place.

Can this be done?

 

[Joe4]

There are a few ways you can go about this.

One way is to add a column that counts the number of duplicates, as they appear.
So if your data starts on row 2, enter this formula in cell H2:
=COUNTIF(D$2:D2,D2)
and copy down for all rows.

Now, you can use Filters to filter out any value in this column that is not equal to 1.
With Advanced Filters, you can paste the result in a new range (so it drops all duplicates).

Alternatively, if you want to use a Macro, you can use this Count column, and starting from the last row with data in column H, loop your way up the rows, deleting any row where the value in column H is not 1.

 

[skyport]

Hi Joe, Thanks for helping me. Mr. Excel and everyone here have always been great.

I don't think the countif concept workks because when it finds a dup, it designates the number 2,3 4 or however many there are found but it would also show that total number to the original entry as well therefore I would end up deleting them all including the original entry which must stay.

 

[Joe4]

I don't think the countif concept workks because when it finds a dup, it designates the number 2,3 4 or however many there are found but it would also show that total number to the original entry as well therefore I would end up deleting them all including the original entry which must stay.

You need to look a little closer at formula, and what happens to it when you copy it down!
row 2: =COUNTIF(D$2:D2,D2)
row 3: =COUNTIF(D$2:D3,D3)
row 4: =COUNTIF(D$2:D4,D4)
...

Note that it is only counting down to the row that the formula is on! So it is not counting anything below it.
So if you had the number 4 on three separate lines, the first time it shows up, it will list a 1 in that cell, the second time 2, and the third time 3.

So it should do exactly what you want (this is a handy little trick to remember, using COUNTIF to act as a counter for specific elements)!

 

[skyport]

Joe, yes I do see it does what you are explaining but the concept simply alerts to where duplicates are and I had tried the CountIf idea prior, and what seems to derail the ability to use it as one example would be the following scenario:


Assume that we begin with 10 rows of data and on every fifth line, the telephone number in question is going to come up. And let's say were going to add 10 more rows of data so we now have 20. This means that the telephone number in question is going to come up on rows 5, 10, 15, and 20 in column D. Let's assume the Countif formula is in the column G. therefore, column G is going to show the number 4 in cells G5, G 10, G 15,. G 20. If I use any method to delete or filter out any row that has a number 4 in column G, it would also filter out or eliminate the original entry or first time the telephone number in question was entered, which is the problem. The original entry must remain in the worksheet. It's the duplicates that need to be eliminated only and not the original occurrence. The formula you are suggesting works if I wanted to eliminate 100% of the times the telephone number in question appears. What I'm hoping to find is some way to quickly eliminate only the duplicate rows and not the original time or row where it first appears.

 

[skyport]

WOE !!!!!!! what is this I'm seeing?????

Joe, I had the countif formula previously in column O to do what you were suggesting and it reacts the way I described above however, when I have the formula in column G it functions as the way you indicated.

 

[Joe4]

Let's assume the Countif formula is in the column G. therefore, column G is going to show the number 4 in cells G5, G 10, G 15,. G 20.

No, that is an incorrect statement.
On G5, it will return 1.
On G10, it will return 2.
On G15, it will return 3.
On G20, it will return 4.

So if you filter out everything that isn't 1, it will only leave G5.

Please try it out exactly as I have explained and see for yourself.

 

[Joe4]

Joe, I had the countif formula previously in column O to do what you were suggesting and it reacts the way I described above however, when I have the formula in column G it functions as the way you indicated.

I see you got it working now. My guess is that you had an issue with how you entered the range in column O the first time. Maybe you were going down all the way to the end instead of just down to the the row that the formula resides on.

For example, if your data can be found in rows 2:100,
on row 2, the formula should be: =COUNTIF(D$2:D2,D2)
I am guessing maybe you had: =COUNTIF(D$2:D$100,D2)

 

[skyport]

I think the problem in O was some other things It was pulling from that were getting a bit fancy and corrupting the concept, but it is great news to see your idea functioning correctly in Column G.

Forgive my ignorance on this one however, I trying to get the filter process to eliminate all rows where Column G has a number greater than 1 and I don't see it as an option. Using excel 2000 ( I know - it's an oldie)

 

[Joe4]

You select the value you want to keep. So just choose the "1".