Solved

Excel VBA Filtering out duplicates

Posted on 2014-10-27
4
156 Views
Last Modified: 2014-10-31
Hi
I have a spreadsheet of data that contains duplicates in column A.
I want to loop through column A and for each ID and highlight
all other duplicates in green (and not the instance that I have found)

1
0
Comment
Question by:murbro
4 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 250 total points
ID: 40407787
Try macro from this sample
Mark-duplicates.xlsm
0
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40407835
you can use Conditional Formatting function for it.
1. Select your whole sheet data with all rows & columns.
2. Go to Conditional Formatting.
3. Select New Rule.
4. Now Select Format only Unique or duplicate values.
5. Now select duplicate at Format All.
6. At Preview click on Format and Select your background/font color and click OK.
7. Click on Final OK at New Formatting Rule popup.

You are done with your work.......................
1.jpg
2.jpg
3.jpg
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 250 total points
ID: 40409027
I assume that:
1) You only want to highlight duplicate values in column A that match a cell selected in column A also.
2) You want this behavior to be dynamic; that is, if you select a new cell, the original highlighting is removed and a new check is done for duplicate values to the new cell.
3) If you select any cell outside of column A, no highlighting will appear.

This Worksheet_Change event (inserted in the sheet module) will do the above:
Option Explicit
Dim valKey As Variant
Dim lngKeyRow As Long
Dim rng As Range
Dim cl As Object
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 And Target.Rows.Count = 1 Then
        valKey = Target.Value
        lngKeyRow = Target.Row
        Set rng = Range("A2", Range("a2").End(xlDown))
        For Each cl In rng
            If cl.Row <> lngKeyRow Then
                If cl.Value = valKey Then
                    cl.Interior.Pattern = xlSolid
                    cl.Interior.Color = RGB(0, 255, 0)
                Else
                    cl.Interior.Pattern = xlNone
                End If
            Else
                cl.Interior.Pattern = xlNone
            End If
        Next cl
    Else
        Set rng = Range("A2", Range("a2").End(xlDown))
        For Each cl In rng
            cl.Interior.Pattern = xlNone
        Next cl
    End If
End Sub

Open in new window


Example workbook attached.

Regards,
-Glenn
EE-HighlightDuplicates.xlsm
0
 

Author Closing Comment

by:murbro
ID: 40416150
Thanks very much. The VBA code was what I needed
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now