?
Solved

Excel VBA Filtering out duplicates

Posted on 2014-10-27
4
Medium Priority
?
212 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:Murray Brown
4 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 1000 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 1000 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:Murray Brown
ID: 40416150
Thanks very much. The VBA code was what I needed
0

Featured Post

Independent Software Vendors: 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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 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