Removing Both Duplicates from a list

[tim]

I have a list that i need to remove all Duplicates, if I find any,

example
List
1
1
2
3
3
4
5
5

Goal
2
4


Not sure how to do this, I'm on office 2007 and I dont see a way to do this
with Remove Duplicates as it still leaves me with one of each number.

 

[Don Guillett]

try this idea. Works for TWO dups as you posted

Sub removeALLdups()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) = Cells(i, mc) Then Rows(i - 1 & ":" & i).Delete
Next i
End Sub

 

[Teethless mama]

"List" is a defined name range (no quotes)

=IF(ISERR(SMALL(IF(COUNTIF(List,List)=1,ROW(INDIRECT("1:"&ROWS(List)))),ROWS($1:1))),"",INDEX(List,SMALL(IF(COUNTIF(List,List)=1,ROW(INDIRECT("1:"&ROWS(List)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

 

[tim]

Also Is this something maybe Microsoft can work into the tool on the next
release of Office or a patch?

Thanks both of you that replied it has been driving me nuts all morning.

 

[GKW in GA]

Works great. If there are duplicates, it removes both. How can i do the same
thing except leave just one of the duplicates

 

[Don Guillett]

Try this For ALL , IF sorted

Sub removealldupsAll()
mc = "a"
lr = Cells(Rows.Count, mc).End(xlUp).Row
Set mrng = Range(Cells(2, mc), Cells(lr, mc))
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
Count = Application.CountIf(mrng, Cells(i, mc))
If Count > 1 Then Rows(i - Count + 1 & ":" & i).Delete
Next i
End Sub

 

[Don Guillett]

Sub removeALLdupsifoneortwo()
Sheets("sheet1").Range("g1:g21").Copy Range("a1")
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1

'leave one
If Cells(i - 1, mc) = Cells(i, mc) Then Rows(i).Delete

'remove both
'If Cells(i - 1, mc) = Cells(i, mc) Then Rows(i - 1 & ":" & i).Delete

Next i
End Sub

 

[Steve]

Use a Pivot table. This would show only 1 row with a count of how many dups
there are

 

[JD]

Hi Don -

This is EXACTLY what I need - but please forgive the noob question: where
exactly do I enter this equation?

Thanks,

JD

 

[Dave Peterson]

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[JD]

Thank you Dave - I opened up the Macro dialogue and it wouldn't let me paste
the code into it, so I figured I was on the wrong track. I'll take a look at
these and give it another shot. Thanks again.

 

[JD]

Thanks to you guys, I'm more knowledgeable on macros AND I've been able to
delete the duplicates. I thought I'd seen some data in triplicate or worse
(4x) but now it seems that it's gone. Is that just me, or does your code
delete all 2 OR MORE instances of data?

Thanks again!!