• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duplicates 'original' row id in answer

kds14589

New Member
I’m using the following Code #1 to find duplicates. But I wish to have the original color’s row identified.
I tried using Code #2 but it was designed for another project with someone’s help but it is too ‘Advanced’ for me to modify even after many attempts. Can someone help me with either code.
A copy of the worksheet and it’s code is attached.

Code #1
Code:
’’’’’’’’’’’’Duplicates
For RowData = RowDataStart To RowDataEnd
    Cells(RowData, 19).Clear
    Cells(RowData, 19).IndentLevel = 1

    If Application.WorksheetFunction.CountIf(Range("G5:G" & RowData), Range("G" & RowData)) > 1 Then
    Range("S" & RowData).Value = "Duplicate Color"    ‘should show original color row number
    Range("S" & RowData).Interior.Color = RGB(255, 255, 0) 'yellow
    Range("G" & RowData).Interior.Color = RGB(255, 255, 0) 'yellow
    Range("B" & RowData).Interior.Color = RGB(255, 255, 0) 'yellow
    ws.Range("A28").Value = "Count of Duplicate Color " & Application.WorksheetFunction.CountA(ws.Range("S5:S" & RowData))
    ws.Range("a28").Interior.ColorIndex = 3
    Else
    Range("S" & RowData).Value = ""
    End If
    Next RowData

Code #2
Code:
''''''''''''original version on 'test ws' by Marc L @ chandoo forum
'      Const D = "Original Color"
'      Const E = "Duplicate Color"
'        Dim V, R&, L&
'    With [D5].CurrentRegion.Columns
'        V = Evaluate(Replace("IF(COUNTIF(#,#)>1,#,"""")", "#", .Item(1).Address))
'    For R = 5 To .Rows.Count - 1
'        If V(R, 1) Like "R*" Then
'            For L = R + 1 To .Rows.Count
'                If V(L, 1) = V(R, 1) Then V(L, 1) = E & " of Row # " & R + 2
'            Next
'                V(R, 1) = D & " Row # " & R + 2
'        End If
'    Next
'       .Item(12) = V
'    End With
 

Attachments

  • Book11.26.22.xlsm
    85.6 KB · Views: 1
kds14589
Did You miss Your previous thread?
 
kds14589
Did You miss Your previous thread?
This is a new project. I just recycled the previous code (#1) but it doesn’t do what I want. I tried using the other code (#2), which worked great on the other project, but I can’t get it adapted to this worksheet. As evidenced from both attachments, the prior thread post had a simple worksheet, this post has a totally different and more complicated worksheet, but my friend asked me for more and the Duplicate code part is giving me headaches. I didn’t think this NEW post would cause any problems. SORRY. How can I correct this and still not contact the ‘Code #2’ author about this new project (which I thought was discouraged)?
 
kds14589
# You showed two codes from ... somewhere ... without a link. Why did You so?
# You should focus to show - what do You would like to get?
... You should able to show - before and after situation as well as how would You use Your code?
# If something is ... complicated ... then something should change.
 
kds14589
# You showed two codes from ... somewhere ... without a link. Why did You so?
# You should focus to show - what do You would like to get?
... You should able to show - before and after situation as well as how would You use Your code?
# If something is ... complicated ... then something should change.
Maybe ‘Complicated’ is the wrong word to describe this worksheet, ‘Involved’.
I have reworked this ‘demo’ to show the Code 2 that my friend prefers.
Code:
''''''  '        '''''Duplicate RGB Code #2

''''''''''''original version by Marc L @ chandoo forum with some modifications by KDS

      Const D = "Original Color"

      Const E = "Duplicate Color"

        Dim V, R&, L&

    With [G5].CurrentRegion.Columns

        V = Evaluate(Replace("IF(COUNTIF(#,#)>1,#,"""")", "#", .Item(7).Address))

    For R = 5 To .Rows.Count - 1

        If V(R, 1) Like "R*" Then

            For L = R + 1 To .Rows.Count

                If V(L, 7) = V(R, 7) Then V(L, 19) = E & " of Row # " & R + 2

            Next

                V(R, 19) = D

        End If

    Next

       .Item(19) = V

    End With

He has ‘issues’, one of which he is very anti-social. It has taken me over 10 years for him to ‘open up’ to me. He has found out that I have a hobby in my retirement to work Excel VBA, so I’m no professional or someone that uses Excel programs at work. That code worked fine on his original worksheet, but now he wants more data information (one of his ‘issues’), and so now we can get the RGB() that is duplicated but not the cells address (as shown in the attachment). He likes the idea that the duplicate color also shows the original’s row as it did in his original worksheet but now with a different layout and more data we can’t get it to work even after MANY attempts.
 

Attachments

  • Book11.29.22.xlsm
    88.5 KB · Views: 2
  • Book11.29.22.xlsm
    88.5 KB · Views: 4
Back
Top