Remove duplicates, but keep the first one

[christian201]

Hello,

I used google for a while and tried to solve it myself, however, I cannot find the solution. Maybe somebody here can help me? Highly appreciated.

Actually I want to remove duplicates and keep the first one. For instance, if i have values "x" and "y" in different pairs, I always want to keep the first "x" and "y" and clear the content below them.

I have also attached a screenshot.

Thanks in advance!

[C.R.]

Christian201,

See thread # 43865. GTO's solution has saved me many times with a similar Issue. I've used it on 100's of 1000's of rows of data over multiple columns with duplicate values. Ongoing thanks to GTO for this one.

C.R.

[christian201]

Hi C.R.,

Thanks for your message. However, how can I find this thread? I used the search function "# 43865", but it did not show any thread with this number.

[Kenneth Hobs]

Look at the URL for this thread. Just replace thread's number with the one provided. When you get past 5 posts, you can post a full link.

http://www.vbaexpress.com/forum/showthread.php?t=43865

[christian201]

hi, thanks, just found it by using the search function (searchin for c.t.), the code looks like rocket science...but i ll do my best :)

Any other suggestions?

[christian201]

ah, I think its just me again. I tried two of the posted macros (thanks c.t.!), they actually work pretty fine. However, a "x" is missing.

Does anybody have an idea how to change the macros in order to use it?

I ve used the code below and GTO's code.

Cheers!

Sub Delete_Dup()
Dim L_Rw As Long
With ThisWorkbook.Sheets(1)
L_Rw = .Cells(Rows.Count, 3).End(xlUp).Row

.Range("i2").Value = "=IF(COUNTIF($C$1:$C$" & L_Rw & ",C2)>1," & _
"IF(MATCH(C2,$C$1:$C$" & L_Rw & ",0)<>ROW(C2),1,""Cor""),""Cor"")"
.Range("i2").Copy .Range("i2:i" & L_Rw)

.Range("i2:i" & L_Rw).SpecialCells(xlCellTypeFormulas, 1).Offset(, -6).ClearContents
.Range("i2:i" & L_Rw).ClearContents

'try the same to rest two colums


End With
End Sub

[GTO]

Hi Christian,

Your images seem to have the 'before' and 'after' columns reversed?

Could you post an example workbook (preferably in .xls format)? Show the 'before' on one sheet, and the 'after' on a second worksheet. I believe this will help us see exactly what you are trying to accomplish.

@C.R. :

Thank you for the feedback :-) It's always nice to know a solution helped.

Mark

[C.R.]

christian201,

I misread your requirement. I had my other glasses on, and read the second set of x’s as z’s. In which case the solutions in the thread Ireferenced would work.
Sorry for that.

Does your set of invoice numbers really repeat like yourexample would indicate? Is there any other associated data, that, when combinedwith the repeating invoice #’s would make them totally unique? Post some real data as Mark requests.

Mark: yw. Your solutions are always a learning experience, and help tremendously.

Best Regards,

C.R.

[snb]

Why not ... see the attachment

[christian201]

Hello,

thanks for all replies so far.

Please see attached the file with the "before" and "after" columns.

I tried another macro as well but its quite difficult.

Cheers

[christian201]

Hi snb,

I just tried ur macro. In my original sheet the values are in column 7, how can I adjust ur macro to this sheet?

I tried another example of your macro which is closer to the real set of data. Cd u please have another look at it?

Highly appreciated.

[snb]

Please, post some real data, in real columns.

[christian201]

pls see attached

[Trebor76]

Hi christian201,

If you must cross-post please always make sure to provide a link to the cross-post as I have done with this hyperlink (http://www.excelforum.com/excel-programming-vba-macros/887894-remove-duplicates-but-keep-the-first-one.html).

Robert

[christian201]

Hi Trebor, sorry! Nobody an idea how to solve this?

[snb]

With the clues you got you must be able to do the final polishing.

[christian201]

Hi snb,

thanks for your reply. I adjusted ur macro to another column (I havent seen such a macro before to solve such a problem) and it looks actually fine, there is just one problem with "short", I just dont have a clue how to work further from here.

[christian201]

Hi,

thanks again for ur help, finally somebody in a German forum helped me out here. I probably never came to the conclusion.

Part of the solution was the fact that some cells just were not empty and had to be cleared in order to use the makro.


Sub xyloeschen()
Dim i As Long, z As Long
Dim stg As String
For i = 2 To Cells(Rows.Count, 4).End(xlUp).Row
If Cells(i, 4).Value = "x" Or Cells(i, 4).Value = "y" Then
For z = i + 1 To Cells(Rows.Count, 4).End(xlUp).Row
stg = Cells(i, 4)
If Cells(z, 4).Value = stg Or IsEmpty(Cells(z, 4)) Then
Cells(z, 4).ClearContents
Else
i = z - 1
Exit For
End If
Next z
End If
Next i
End Sub