CODEWITHSUNDEEP
excelvbaduplicateshighlight
Catnipper
Catnipper

Reputation: 53

Highlight duplicates of concatenated value WITHOUT creating concatenated values in work sheet

Goal: I have a list of first name last name and want to highlight duplicates when entering a new person. (or click the button to check if its a duplicated)

Old Solution: I originally had a code that looked if Firstname is a duplicate AND if Lastname is a duplicate and then highlight but that doesn't actually show me true duplicates because in this example: Tim Smith Peter Smith Tim NotSmith Tim Smith would be highlighted because Tim is a duplicate and Smith is a Duplicate, but TimSmith is not.

Current code: Now, I am looking at the concatenated list of Firstname and Lastname and if duplicate then highlight. That obviously works well.

Private Sub CommandButton1_Click()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row

  For i = 3 To lastrow
  Set myrange = Range("G:G")

  Range("G3:G98") = "=CONCATENATE(RC[-2],RC[-1])"

      For j = 3 To lastrow

        If WorksheetFunction.CountIf(myrange, Worksheets("Sheet1").Cells(j, 7).Value) 
        <> 1 Then

            Worksheets("Sheet1").Cells(j, 5).Interior.ColorIndex = 8
            Worksheets("Sheet1").Cells(j, 6).Interior.ColorIndex = 8

        End If

      Next
  Next
End Sub

So instead of this, I don't want the concatenated column in the data set but just exist as memory or something like that. Maybe in an array?

I just want to check if there were duplicates if I would have a column like that but not actually create the column. I need to keep the worksheet and its columns consistent. So I can't have an additional column like the concat.

I am open to different strategies altogether. There are many ways to skin a cat.

The main goal is to highlight real duplicates. Ideally when entering a new person, or when clicking a button.

Upvotes: 0

Views: 639

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

Why not use conditional formatting with the following formula:

=COUNTIFS($E:$E,$E1,$F:$F,$F1)>1

The will highlight all duplicates

If you don't want the first instance highlighted use:

=COUNTIFS($E$1:$E1,$E1,$F$1:$F1,$F1)>1

Upvotes: 2

Related Questions