• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

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?

0
nainil
Asked:
nainil
  • 4
  • 2
  • 2
2 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
geoffkkCommented:
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
 
geoffkkCommented:
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
 
nainilAuthor Commented:
Thank you, this works as expected!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now