Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating the max value AND performing a fn on it only | Excel Discussion (Misc queries) | |||
Locating duplicates within range of time punch data | Excel Worksheet Functions | |||
locating duplicates | Excel Discussion (Misc queries) | |||
Locating | New Users to Excel | |||
locating the top 5 number (in a col) | Excel Worksheet Functions |