Removing Both Duplicates from a list
 

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.

Removing Both Duplicates from a list
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tim" wrote in message
...
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.

Removing Both Duplicates from a list
 

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

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

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


"tim" wrote:

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.

Removing Both Duplicates from a list
 

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.

"Teethless mama" wrote:

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

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

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


"tim" wrote:

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.

Removing Both Duplicates from a list
 

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tim" wrote in message
...
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.

Removing Both Duplicates from a list
 

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
Microsoft MVP Excel
SalesAid Software

"tim" wrote in message
...
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.

Removing Both Duplicates from a list
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"GKW in GA" wrote in message
...

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tim" wrote in message
...
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.

Removing Both Duplicates from a list
 

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

"Don Guillett" wrote:

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
Microsoft MVP Excel
SalesAid Software

"tim" wrote in message
...
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.

Removing Both Duplicates from a list
 

Hi Don -

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

Thanks,

JD

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel

Removing Both Duplicates from a list
 

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
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 wrote:

Hi Don -

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

Thanks,

JD

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel

--

Dave Peterson

Removing Both Duplicates from a list
 

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.

"Dave Peterson" wrote:

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
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 wrote:

Hi Don -

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

Thanks,

JD

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel

--

Dave Peterson

Removing Both Duplicates from a list
 

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!!

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tim" wrote in message
...
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.