locating and separating out duplicates
 

I have a multi-column list the I am currently sorting by
Column A value and then coloring rows with duplicate
values in A using conditional formatting.

How can I either sort by color, to get all the duplicate
rows together (both sets of the duplicate pair)? or, use
some other method to locate and separate out any rows
where there are more than one of any column A value?

Thanks. I think I remember seeing a posting about this a
few weeks ago; but I have not been able to locate it again?

Adam

locating and separating out duplicates
 

Adam

The following formula in the conditional formatting
Formula Is section will highlight the second and
subsequent entries in column A. Put the formula into A1
and copy down the format.

Tony

=COUNTIF($A$1:$A1,A1)1
-----Original Message-----
I have a multi-column list the I am currently sorting by
Column A value and then coloring rows with duplicate
values in A using conditional formatting.

How can I either sort by color, to get all the duplicate
rows together (both sets of the duplicate pair)? or, use
some other method to locate and separate out any rows
where there are more than one of any column A value?

Thanks. I think I remember seeing a posting about this a
few weeks ago; but I have not been able to locate it
again?

Adam
.

locating and separating out duplicates
 

Thanks, Tony. That will apply whatever format I choose to
the cells with duplicate entries. Is there a way to
automatically sort the list so that all the duplicates
appear together at the top or bottom of list; or to hide
non-duplicates? Thanks,

Adam
-----Original Message-----
Adam

The following formula in the conditional formatting
Formula Is section will highlight the second and
subsequent entries in column A. Put the formula into A1
and copy down the format.

Tony

=COUNTIF($A$1:$A1,A1)1
-----Original Message-----
I have a multi-column list the I am currently sorting by
Column A value and then coloring rows with duplicate
values in A using conditional formatting.

How can I either sort by color, to get all the duplicate
rows together (both sets of the duplicate pair)? or, use
some other method to locate and separate out any rows
where there are more than one of any column A value?

Thanks. I think I remember seeing a posting about this
a
few weeks ago; but I have not been able to locate it
again?

Adam
.

.

locating and separating out duplicates
 

One way of doing this would be to sort by color (since the
conditional formatting is coloring all the dupes).
Problem is, I don't know how to sort by color... Is there
a way to do this?

Thanks,

Adam
-----Original Message-----
I have a multi-column list the I am currently sorting by
Column A value and then coloring rows with duplicate
values in A using conditional formatting.

How can I either sort by color, to get all the duplicate
rows together (both sets of the duplicate pair)? or, use
some other method to locate and separate out any rows
where there are more than one of any column A value?

Thanks. I think I remember seeing a posting about this a
few weeks ago; but I have not been able to locate it
again?

Adam
.

locating and separating out duplicates
 

Not any built in way - especially when the color is produced by conditional
formatting.

Regards,
Tom Ogilvy

"Adam" wrote in message
...
One way of doing this would be to sort by color (since the
conditional formatting is coloring all the dupes).
Problem is, I don't know how to sort by color... Is there
a way to do this?

Thanks,

Adam
-----Original Message-----
I have a multi-column list the I am currently sorting by
Column A value and then coloring rows with duplicate
values in A using conditional formatting.

How can I either sort by color, to get all the duplicate
rows together (both sets of the duplicate pair)? or, use
some other method to locate and separate out any rows
where there are more than one of any column A value?

Thanks. I think I remember seeing a posting about this a
few weeks ago; but I have not been able to locate it
again?

Adam
.

locating and separating out duplicates
 

"Adam" wrote in message ...
One way of doing this would be to sort by color (since the
conditional formatting is coloring all the dupes).
Problem is, I don't know how to sort by color... Is there
a way to do this?

Thanks,

Adam
-----Original Message-----
I have a multi-column list the I am currently sorting by
Column A value and then coloring rows with duplicate
values in A using conditional formatting.

How can I either sort by color, to get all the duplicate
rows together (both sets of the duplicate pair)? or, use
some other method to locate and separate out any rows
where there are more than one of any column A value?

Thanks. I think I remember seeing a posting about this a
few weeks ago; but I have not been able to locate it
again?

Adam
.




Try this for sorting by colour



Sub sortByColour()
Dim iLastCol As Integer, iCellColr As Integer
Dim lLastRow As Long
Dim rCell As Range
lLastRow = Range("A1").End(xlDown).Row
iLastCol = Range("A1").End(xlToRight).Column
Application.ScreenUpdating = False
For Each rCell In Range("A2:A" & lLastRow)
iCellColr = rCell.Interior.ColorIndex
rCell.Offset(0, iLastCol).Value = iCellColr
Next rCell
Range("A1").Sort Key1:=Cells(2, iLastCol + 1), Order1:=xlAscending, _
Header:=xlGuess
Range("A1").Offset(0, iLastCol).EntireColumn.Delete
Application.ScreenUpdating = True
End Sub


and this to delete duplicates




Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through.
iListCount = Sheets("Sheet1").Range("A1:A100").Rows.Count
Sheets("Sheet1").Range("A1").Select
' Loop until end of records.
Do Until ActiveCell = ""
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the column number.
If ActiveCell.Row < Sheets("Sheet1").Cells(iCtr, 1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

locating and separating out duplicates
 

I hope this is not a dumb question, but I'm not really
sure what to do with this (below). Do I 'step into' a
macro and paste the instructions below, and then run
macro? Or is there somewhere else to input this
information?

Thanks!




Try this for sorting by colour



Sub sortByColour()
Dim iLastCol As Integer, iCellColr As Integer
Dim lLastRow As Long
Dim rCell As Range
lLastRow = Range("A1").End(xlDown).Row
iLastCol = Range("A1").End(xlToRight).Column
Application.ScreenUpdating = False
For Each rCell In Range("A2:A" & lLastRow)
iCellColr = rCell.Interior.ColorIndex
rCell.Offset(0, iLastCol).Value = iCellColr
Next rCell
Range("A1").Sort Key1:=Cells(2, iLastCol + 1),
Order1:=xlAscending, _
Header:=xlGuess
Range("A1").Offset(0, iLastCol).EntireColumn.Delete
Application.ScreenUpdating = True
End Sub


and this to delete duplicates




Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through.
iListCount = Sheets("Sheet1").Range("A1:A100").Rows.Count
Sheets("Sheet1").Range("A1").Select
' Loop until end of records.
Do Until ActiveCell = ""
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the
column number.
If ActiveCell.Row < Sheets("Sheet1").Cells(iCtr,
1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets("Sheet1").Cells
(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).Delete
xlShiftUp
' Increment counter to account for deleted
row.
iCtr = iCtr + 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub
.

locating and separating out duplicates
 

David McRitchie has some getstarted notes at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Adam wrote:

I hope this is not a dumb question, but I'm not really
sure what to do with this (below). Do I 'step into' a
macro and paste the instructions below, and then run
macro? Or is there somewhere else to input this
information?

Thanks!




Try this for sorting by colour



Sub sortByColour()
Dim iLastCol As Integer, iCellColr As Integer
Dim lLastRow As Long
Dim rCell As Range
lLastRow = Range("A1").End(xlDown).Row
iLastCol = Range("A1").End(xlToRight).Column
Application.ScreenUpdating = False
For Each rCell In Range("A2:A" & lLastRow)
iCellColr = rCell.Interior.ColorIndex
rCell.Offset(0, iLastCol).Value = iCellColr
Next rCell
Range("A1").Sort Key1:=Cells(2, iLastCol + 1),
Order1:=xlAscending, _
Header:=xlGuess
Range("A1").Offset(0, iLastCol).EntireColumn.Delete
Application.ScreenUpdating = True
End Sub


and this to delete duplicates




Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through.
iListCount = Sheets("Sheet1").Range("A1:A100").Rows.Count
Sheets("Sheet1").Range("A1").Select
' Loop until end of records.
Do Until ActiveCell = ""
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the
column number.
If ActiveCell.Row < Sheets("Sheet1").Cells(iCtr,
1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets("Sheet1").Cells
(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).Delete
xlShiftUp
' Increment counter to account for deleted
row.
iCtr = iCtr + 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub
.


--

Dave Peterson