Excel VBA - Duplicate search multiple columns?

I would like to see a VBA function which will identify whether we have any duplicates in a combination of columns.
For Eg:, in below dataset, column A and column C need to have unique values.

 
id	ColumnA	ColumnB	ColumnC	ColumnD
1	A	B	C	D
2	A1	B1	C1	D1
3	A2	B2	C2	D2
4	A1	B3	C1	D3 --> Value for column A and C duplicated with id 2
5	A4	B1	C4	D1 --> 
6	A2	B1	C2	D1 --> Value for column A and C duplicated with id 3

Open in new window


and so on

I want to identify the IDs which are duplicated in the sheet for the Column A and Column C (combined).

How best can we achieve the same?

nainilAsked:
Who is Participating?
 
geoffkkConnect With a Mentor Commented:
I have attached a sample with formulae that will do the job. It uses 3 extra columns but the formulae could be combined in necessary. It could be refined more but gives you the idea.

This solution may not work for some data types as it uses & to combine, but try it, and if necessary convert each cell to text before using &.

As shown, each duplicate is marked and the first appearance is referenced.
dupvalues.xlsx
0
 
Patrick MatthewsCommented:
WHich version of Excel?  And do you care about the 'B' and/or 'D' values or not?
0
 
nainilAuthor Commented:
@matthewspatrick:
it is Excel 2007.
No, I do not really care the values coming in column B and column D.

Thanks!
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Patrick MatthewsCommented:
No need for VBA, really.  You can do this with Conditional Formatting:

1) Enter the values from your question above into A1:E7

2) Select A2:E7

3) Add a formula-based Conditional Formatting rule using this formula:

=SUMPRODUCT(($B$2:$B2=$B2)*($D$2:$D2=$D2))>1

and select your desired formatting

That will apply your desired formatting to the 2nd, 3rd, 4th, Nth instances of repeated values.
0
 
nainilAuthor Commented:
@matthewspatrick:

I see what you have suggested. But, I already have a VBA application which performs certain other validations. I would like to add this validation of column a and column c to my existing custom validation.

Hence, VBA is suggested mode for me.
0
 
geoffkkConnect With a Mentor Commented:
Here is a sub that will highlight column 1 in yellow for duplicates and place a message in column 6 of the duplication.
Sub MarkDuplicates()
Dim rw As Integer
Dim i As Integer
Dim item As String
Dim dupcount As Integer
Dim rowcount As Integer
  With Selection
    rowcount = .rows.Count
    For rw = 1 To rowcount
      item = .Cells(rw, 2).Text & .Cells(rw, 4).Text
      For i = rw + 1 To rowcount
        If .Cells(i, 2).Text & .Cells(i, 4).Text = item Then
          .Cells(rw, 1).Interior.ColorIndex = 6
          .Cells(i, 1).Interior.ColorIndex = 6
          .Cells(i, 6).Formula = "Duplicate of item " & .Cells(rw, 1).Text
        End If
      Next i
    Next rw
  End With
End Sub

This is OK for small lists but the number of iterations increases roughly as the square of the number of items in the list.

For larger lists you could place a formula in col 6 to combine the two cells and then use a countif function in column 7 to count the no of appearances of each formula from col 6 in the range.

Alternatively instead of the code above you might want to show a msgbox detailing the dupication etc. I was not clear on what you wanted to do with the duplicates.
Regards
Geoff
0
 
nainilAuthor Commented:
@geoffkk:This code works perfectly fine as described for the small lists. However, for a list of say 2000 values, it is taking around 5 minutes, which is OK i guess for now.

I am not sure about the proposed solution you have for the larger lists. Can you please share some more thoughts on the same? I get the part of combining the two cells, but, how will that help?
0
 
nainilAuthor Commented:
Thank you, this works as expected!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.