?
Solved

Excel VBA - Duplicate search multiple columns?

Posted on 2011-09-28
8
Medium Priority
?
379 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 93

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 93

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
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.

 

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 500 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 500 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

800 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