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.

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.

"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.

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.

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.

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.

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.

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.

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

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

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

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.