?
Solved

Excel VBA Filtering out duplicates

Posted on 2014-10-27
4
Medium Priority
?
189 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
[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 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

752 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