Solved

Excel VBA Filtering out duplicates

Posted on 2014-10-27
4
175 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 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:Murray Brown
ID: 40416150
Thanks very much. The VBA code was what I needed
0

Featured Post

Industry Leaders: 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

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.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

690 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