Solved

Excel VBA - Duplicate search multiple columns?

Posted on 2011-09-28
8
375 Views
Last Modified: 2012-06-27
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
Comment
Question by:nainil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36716590
WHich version of Excel?  And do you care about the 'B' and/or 'D' values or not?
0
 

Author Comment

by:nainil
ID: 36716674
@matthewspatrick:
it is Excel 2007.
No, I do not really care the values coming in column B and column D.

Thanks!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36718134
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:nainil
ID: 36788465
@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
 
LVL 2

Assisted Solution

by:geoffkk
geoffkk earned 125 total points
ID: 36814811
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
 

Author Comment

by:nainil
ID: 36944320
@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
 
LVL 2

Accepted Solution

by:
geoffkk earned 125 total points
ID: 36946930
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
 

Author Closing Comment

by:nainil
ID: 36987231
Thank you, this works as expected!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question