Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates i.e. "Anne Bell"
Hi Hi have a list of names, however some is duplicated. Is there a eas way to delete all the duplicates.. some are also duplicated more tha once... Thank -- Ctec ----------------------------------------------------------------------- Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774 View this thread: http://www.excelforum.com/showthread.php?threadid=48678 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates i.e. "Anne Bell"
In an adjacent column, add this formula
=COUNTIF($A$1:A2,A2)1 and copy down Then filter that column for the TRUE value, and delete visible rows. -- HTH RP (remove nothere from the email address if mailing direct) "Ctech" wrote in message ... Hi Hi have a list of names, however some is duplicated. Is there a easy way to delete all the duplicates.. some are also duplicated more than once... Thanks -- Ctech ------------------------------------------------------------------------ Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745 View this thread: http://www.excelforum.com/showthread...hreadid=486784 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates i.e. "Anne Bell"
Thanks, that worked fine. However now I want to implment this forumla into a more advanced macro. I have made a User Form, with a RefEdit for the Range, and two radio buttons. How can I implement this fomula to work with a different range each time. I have made some code, however I'm not even close to make it.. All help needed. Thanks Macro: Private Sub cmd_OK_Click() Dim mRange Dim mRows As Long mRange = Duplicates.RefEdit1 mRows = Range(mRange).Rows.Count If cb_delete Then ' My code End If If cb_color Then ****Not needed yet **** End If Unload Duplicates End Sub Private Sub cmd_Cancel_Click() Unload Duplicates End Sub -- Ctech ------------------------------------------------------------------------ Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745 View this thread: http://www.excelforum.com/showthread...hreadid=486784 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates i.e. "Anne Bell"
How about this
Private Sub cmd_OK_Click() Dim mRange As Range Dim mRows As Long Set mRange = Range(Duplicates.RefEdit1) mRows = mRange.Rows.Count If cb_Delete Then Columns("B:B").Insert Range("B1").Formula = "=COUNTIF($A$1:A1,A1)" Range("B1").AutoFill Range("B1").Resize(mRows) Rows(1).Insert Range("B1").Value = "Temp" mRange.Offset(-1, 1).Resize(11).AutoFilter Field:=1, Criteria1:="1", Operator:=xlAnd mRange.Offset(0, 1).Resize(11).SpecialCells(xlCellTypeVisible).Enti reRow.Delete Rows(1).Delete Columns("B:B").Delete End If If cb_Color Then '****Not needed yet **** End If Unload Duplicates End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Ctech" wrote in message ... Thanks, that worked fine. However now I want to implment this forumla into a more advanced macro. I have made a User Form, with a RefEdit for the Range, and two radio buttons. How can I implement this fomula to work with a different range each time. I have made some code, however I'm not even close to make it.. All help needed. Thanks Macro: Private Sub cmd_OK_Click() Dim mRange Dim mRows As Long mRange = Duplicates.RefEdit1 mRows = Range(mRange).Rows.Count If cb_delete Then ' My code End If If cb_color Then ****Not needed yet **** End If Unload Duplicates End Sub Private Sub cmd_Cancel_Click() Unload Duplicates End Sub -- Ctech ------------------------------------------------------------------------ Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745 View this thread: http://www.excelforum.com/showthread...hreadid=486784 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates i.e. "Anne Bell"
Your macro worked fine, when it was used on a range in Column A, however I need it to work everywhere in the wb. So I've made my own littile marco, which should fix this problem. However Im having some problems with the addtounion part. Macro: Dim DelRg As Range Private Sub cmd_OK_Click() Dim mRange As Range Dim mTest As Range Dim mRows As Long Dim mCell As Range Dim Cell As Range Set DelRg = Nothing Set mRange = Range(Duplicates.RefEdit1) Set mTest = mRange.Offset(0, 1) Set mCell = mTest.Cells(1) mRows = mRange.Rows.Count If cb_delete Then mTest.EntireColumn.Insert Shift:=x1ToLeft Set mTest = mRange.Offset(0, 1) mTest.Select mTest.Formula = "=COUNTIF($A$1:A1,A1)" For Each Cell In mTest If Cell.Value 1 Then AddToUnion Cell End If Next Cell If Not DelRg Is Nothing Then DelRg.EntireRow.Delete Shift:=x1up mTest.EntireColumn.Delete Shift:=x1ToLeft End If If cb_color Then '****Not needed yet **** End If Unload Duplicates End Sub Sub AddToUnion(Cell As Range) Set DelRg = Union(DelRg, Cell) End Sub -- Ctech ------------------------------------------------------------------------ Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745 View this thread: http://www.excelforum.com/showthread...hreadid=486784 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates i.e. "Anne Bell"
Why not filter as I did?
-- HTH RP (remove nothere from the email address if mailing direct) "Ctech" wrote in message ... Your macro worked fine, when it was used on a range in Column A, however I need it to work everywhere in the wb. So I've made my own littile marco, which should fix this problem. However Im having some problems with the addtounion part. Macro: Dim DelRg As Range Private Sub cmd_OK_Click() Dim mRange As Range Dim mTest As Range Dim mRows As Long Dim mCell As Range Dim Cell As Range Set DelRg = Nothing Set mRange = Range(Duplicates.RefEdit1) Set mTest = mRange.Offset(0, 1) Set mCell = mTest.Cells(1) mRows = mRange.Rows.Count If cb_delete Then mTest.EntireColumn.Insert Shift:=x1ToLeft Set mTest = mRange.Offset(0, 1) mTest.Select mTest.Formula = "=COUNTIF($A$1:A1,A1)" For Each Cell In mTest If Cell.Value 1 Then AddToUnion Cell End If Next Cell If Not DelRg Is Nothing Then DelRg.EntireRow.Delete Shift:=x1up mTest.EntireColumn.Delete Shift:=x1ToLeft End If If cb_color Then '****Not needed yet **** End If Unload Duplicates End Sub Sub AddToUnion(Cell As Range) Set DelRg = Union(DelRg, Cell) End Sub -- Ctech ------------------------------------------------------------------------ Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745 View this thread: http://www.excelforum.com/showthread...hreadid=486784 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates i.e. "Anne Bell"
I tried, but I couldn't manage to get it to work. I'll happly use your method, but either way I need some more hel please. Thanks al -- Ctec ----------------------------------------------------------------------- Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774 View this thread: http://www.excelforum.com/showthread.php?threadid=48678 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicates i.e. "Anne Bell"
What problem are you having?
-- HTH RP (remove nothere from the email address if mailing direct) "Ctech" wrote in message ... Your macro worked fine, when it was used on a range in Column A, however I need it to work everywhere in the wb. So I've made my own littile marco, which should fix this problem. However Im having some problems with the addtounion part. Macro: Dim DelRg As Range Private Sub cmd_OK_Click() Dim mRange As Range Dim mTest As Range Dim mRows As Long Dim mCell As Range Dim Cell As Range Set DelRg = Nothing Set mRange = Range(Duplicates.RefEdit1) Set mTest = mRange.Offset(0, 1) Set mCell = mTest.Cells(1) mRows = mRange.Rows.Count If cb_delete Then mTest.EntireColumn.Insert Shift:=x1ToLeft Set mTest = mRange.Offset(0, 1) mTest.Select mTest.Formula = "=COUNTIF($A$1:A1,A1)" For Each Cell In mTest If Cell.Value 1 Then AddToUnion Cell End If Next Cell If Not DelRg Is Nothing Then DelRg.EntireRow.Delete Shift:=x1up mTest.EntireColumn.Delete Shift:=x1ToLeft End If If cb_color Then '****Not needed yet **** End If Unload Duplicates End Sub Sub AddToUnion(Cell As Range) Set DelRg = Union(DelRg, Cell) End Sub -- Ctech ------------------------------------------------------------------------ Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745 View this thread: http://www.excelforum.com/showthread...hreadid=486784 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
which record is deleted when I use the "remove duplicates" functi. | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |