• 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.

Highlight unique records

ThrottleWorks

Excel Ninja
Hi,

Please see attached file for your reference.
In original data, I am trying to get the result at same location, that is Column A and B.

For sample data, I have input at Range("A2:B16") and output at Range("D2:E16").
However, original data range is Range("A1:B15").

I need to highlight unique row for each record set.
Once a particular combination is highlighted, that combination should not be highlighted again.

Can anyone please help me in this.
 

Attachments

  • Chandoo.xlsm
    9.1 KB · Views: 6
Hi @Marc L sir, sorry. I attached unsaved version earlier.
Please see updated version if you get time.

I was also trying with CF earlier but could not hence I thought VBA would be only option.

Have a nice weekend.
 

Attachments

  • Chandoo.xlsm
    9.4 KB · Views: 5
I thought VBA would be only option.
Like I thought it is not unique records but about first ones !
As for unique records only the last one is to be highlighted,
the reason why I asked for an accurate attachment …

So according to your first attachment, just with Excel features like
any beginner can yet operate manually so easy with the Macro Recorder :​
Code:
Sub Demo1()
        Application.ScreenUpdating = False
    With Sheet1.UsedRange.Resize(, 4).Columns
        .Interior.ColorIndex = 0
        .Item(3).Formula = "=A1&""¤""&B1"
        .Item(4).Formula = "=COUNTIF($C$1:C1,C1)"
        .Item(4).AutoFilter 1, 1
        .Item("A:B").Rows("2:" & .Rows.Count).Interior.ColorIndex = 36
        .AutoFilter
        .Item("C:D").Clear
    End With
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
My revamped code using only one helper column :​
Code:
Sub Demo1r()
        Application.ScreenUpdating = False
    With Sheet1.UsedRange.Resize(, 3).Columns
        .Interior.ColorIndex = 0
        .Item(3).Formula = "=COUNTIF($B$1:B1,B1)"
        .Item(3).AutoFilter 1, 1
        .Item("A:B").Rows("2:" & .Rows.Count).Interior.ColorIndex = 36
        .AutoFilter
        .Item(3).Clear
    End With
        Application.ScreenUpdating = True
End Sub
You may Like it !
 
Back
Top