I used conditional formatting to find duplicate email addresses in a calc sheet. All duplicates are highlighted in red. I need to copy all duplicate rows into a new sheet. Any suggestions how to do that without manually selecting each individual row?
There is a lengthy discussion link text that you schould not use this.
One bottle-neck is that cells appear to be highlighted, but the highlight is not equal to the background color. The color to retrieve by macro, is hidden in the cell property ‘conditionalformat’ and very dependent upon what you conditionally programmed.
If you can sort the sheet on email-name, than there is a more reliable and direct algorithm to select duplicates directly and subsequently copy the value (or the entire row) to another sheet.
Suppose row A contains the sorted E-mail adresses
Then formula for column B is =IF(LOWER(A1)=LOWER(A2);1;0) : looks ahead, copy formula in B1 to rest of cells in the column
Formula cell C2 is =IF(LOWER(A2)<>LOWER(A1);1;0) : looks backwards, copy to rest of cells C3, C4…
be aware that C1 contains 1 (no looking back possible)
Formula D: =IF(AND(B1=1;C1=1);A1;"")
Use macro to copy columns to the ,already added, Sheet2
Sub move_duplicate_column()
Dim oSheet as object, oSheet2 as object, value As string
Dim n As integer, iRow As Integer, iColumn As integer
Dim j As long
oSheet = thiscomponent.getSheets().getByIndex(0)
oSheet2 = thiscomponent.getSheets().getByIndex(1)
iColumn = 3 REM duplicates are in column D, A=0 index
n = 0
for iRow=1 To 900 REM adjust to last row filled
oCell = oSheet.getCellByPosition(iColumn,iRow-1)
value = oCell.getString
If value<>"" Then
For j=0 To 9 Rem adjust to last column filled
oCell = oSheet.getCellByPosition(j,iRow)
value = oCell.getString()
oCell = oSheet2.getCellByPosition(j,n)
oCell.setString(value)
Next j
n = n + 1
endif
next iRow
Print "done"
End sub
In advance: I am not aware that I did advocate my answer, that I posted 15 hours, without any response of the community, after it appeared. There will always be room for improvement
AFAIK is your comment not in line with general rules for a collaborative platform, where it is a habit not to judge other people. That would introduce a vertical structure with a board judging which contributors are allowed to post. The nature of an open source platform is to work horizontally, that is inclusive: no one is banned from contributing.(You never can know what specific knowledge a contributor has, that can be useful in another situation.)
You can make your comment more informative, and friendly, by giving suggestions on how to, either improve the answer or recommend a procedure to prevent answers you do not like, fi. it is always better to look for build in solutions before writing code.
This is not a difficult task. You will need a simple formula like =COUNTIF(A:A;A2) and the Standard Filter tool
