Results 1 to 8 of 8

VBA to highlight duplicate data

This is a discussion on VBA to highlight duplicate data within the Excel Questions forums, part of the Question Forums category; Hi, I have the following data in tables in a spreadsheet, and I need a vba script that looks at ...

  1. #1
    New Member
    Join Date
    Nov 2007
    Posts
    8

    Default VBA to highlight duplicate data

    Hi,
    I have the following data in tables in a spreadsheet, and I need a vba script that looks at the whole sheet and highlights any fruit (and adjacent data) that appears more than once (note that the bottom table contains an additional column of data which should also be highlighted).
    I also need it to highlight the fruit in a different colour depending on how many times it is duplicated.
    For example, pear appears twice, so could be highlighted yellow, apple and orange appear three times each so they could be red.

    apple 2
    orange 3
    pear 2
    kiwi 4
    mango 5


    bannana 3
    grape 2
    peach 4
    apple 2
    orange 3


    blueberry 2 A
    coconut 2 B
    orange 3 C
    apple 2 D
    pear 2 E



    This is a small chunk of the data, but the full set will have 20 fruits and maybe 10 tables of data to compare.
    At the moment I have started to write a script that has loops within loops within loops, but this isn't going to be feasible/efficient for my larger set of data.

    Does anyone have some code that they can share which achieves this more efficiently?
    (my vba experience is moderate)

    Many thanks!!!

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,058

    Default Re: VBA to highlight duplicate data

    I would suggest you insert a column to the left, or use an empty column off to the right somewhere, and place a count of each fruit in the column.

    Use a Dynamic range name - that makes it very easy to get the count. Now you're conditional format need only evaluate this count.

    For multiple colors, that's a bit more to ask. With many colors, you'll need vba - perhaps you could use the count as the color index. Or just use VBA to assign a color of choice to each count - again, easier since you only need to look at the count value in your new column.

    Hope this helps.

    On dynamic named ranges:
    http://www.contextures.com/xlNames01.html

    ------------------------------------------
    Sample idea of this column I'm talking about (you'd probably hide the column in actuality).

    Formula in Cell D1 is =COUNTIF(Fruit,A1) where fruit is a dynamic named range of column A. It actually may be a little off since there's empty cells between tables - you may need to tweak it a little.

    ******** ******************** ************************************************************************>
    Microsoft Excel - book1___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    apple2 2
    2
    orange3 2
    3
    pear2 1
    4
    kiwi4 1
    5
    mango5 1
    6
       0
    7
       0
    8
    bannana3 1
    9
    grape2 1
    10
    peach4 1
    11
    apple2 2
    12
    orange3 2
    13
       0
    14
       0
    15
    blueberry2A1
    16
    coconut2B0
    17
    orange3C2
    18
    apple2D2
    19
    pear2E1
    Sheet1 

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3

    Join Date
    Oct 2006
    Posts
    2,541

    Default Re: VBA to highlight duplicate data

    VBA
    try
    Code:
    Sub test()
    Dim a, i As Long
    a = Range("a1", Range("a" & Rows.Count).End(xlUp)).Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 1 To UBound(a, 1)
            If Not IsEmpty(a(i, 1)) Then
                If Not .exists(a(i, 1)) Then .item(a(i, 1)) = VBA.Array("", 0)
                w = .item(a(i, 1))
                w(0) = w(0) & IIf(w(0)="","",",") & Cells(i, 1).Address(0,0)
                w(1) = w(1) + 1 : .item(a(i, 1)) = w
            End If
        Next
        For Each e In .items
            If e(1) <> 1 Then
                Select Case e(1)
                    Case 2 : myClr = vbYellow
                    Case 3 : myClr = vbRed
                    Case 4 : myClr = vbBlue
                    Case 5 : myClr = vbMagenta
                    Case 6 : myClr = vbGreen
                    Case Else : myClr = vbCyan
                End Select
                Range(e(0)).Interior.Color = myClr
            End If
        Next
    End With
    End Sub

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,058

    Default Re: VBA to highlight duplicate data

    Thanks Seiya for giving us a vba solution. I was posting back to say I have no idea why I used a dynamic named range - I think its because I added about 5 of them to a spreadsheet I was working on today. Just using the column would probably work for "Fruit" - i.e., =CountIf(A:A,A1) instead of =CountIf(Fruit,A1) Ah...time for sleep...

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,453

    Default Re: VBA to highlight duplicate data

    You never said what version. This is built into Excel 2007.
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  6. #6
    New Member
    Join Date
    Nov 2007
    Posts
    8

    Default Re: VBA to highlight duplicate data

    Thanks so much for the replies. Very helpful.

    Seiya, for the script you wrote, this works great, but how would it be changed to use a named range.
    i.e. if I wanted to compare for duplicates within range "Fruit1", and then wanted to apply the colour format to range "Fruit2" (a different range).

    I am using excel 2003.

    Again, thanks fo the help.

  7. #7

    Join Date
    Oct 2006
    Posts
    2,541

    Default Re: VBA to highlight duplicate data

    try
    Code:
    Sub test()
    Dim a, i As Long, w(), e
    a = Range("fruit1").Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 1 To UBound(a, 1)
            If Not IsEmpty(a(i, 1)) Then
                If Not .exists(a(i, 1)) Then .item(a(i, 1)) = VBA.Array("", 0)
                w = .item(a(i, 1))
                w(0) = w(0) & IIf(w(0)="","",",") & Range("fruit1").Cells(i, 1).Address(0,0)
                w(1) = w(1) + 1 : .item(a(i, 1)) = w
            End If
        Next
        a = Range("fruit2").Value
        For i = 1 To UBound(a, 1)
            If Not IsEmpty(a(i, 1)) Then
                If Not .exists(a(i, 1)) Then .item(a(i, 1)) = VBA.Array("", 0)
                w = .item(a(i, 1))
                w(0) = w(0) & IIf(w(0)="","",",") & Range("fruit2").Cells(i, 1).Address(0,0)
                w(1) = w(1) + 1 : .item(a(i, 1)) = w
            End If
        Next    
        For Each e In .items
            If e(1) <> 1 Then
                Select Case e(1)
                    Case 2 : myClr = vbYellow
                    Case 3 : myClr = vbRed
                    Case 4 : myClr = vbBlue
                    Case 5 : myClr = vbMagenta
                    Case 6 : myClr = vbGreen
                    Case Else : myClr = vbCyan
                End Select
                Range(e(0)).Interior.Color = myClr
            End If
        Next
    End With
    End Sub

  8. #8
    New Member
    Join Date
    Nov 2007
    Posts
    8

    Default Re: VBA to highlight duplicate data

    Maaaate....
    That works a treat. Long live MrExcel!
    Cheers.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com