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
Solved

Excel VBA - Duplicate search multiple columns?

Posted on 2011-09-28
8
372 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
  • 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

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.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

839 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