Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare in same Col (2003 sp2)

    Hello,
    I have the following condition: a list of data (alphanumberic). Within the col I would like to determine if there are duplicates and highlight the cell. I understand how to use conditional formatting to check for data next to each other. (cell next to cell). However I do not understand how to compare for match anywhere in the same col.


    Thanks,
    Brad

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare in same Col (2003 sp2)

    Brad,

    Is a macro solution ok? I have one that I got from Hans:

    Sub SelectDupes_hans()
    ' Source: Woody's Lounge Post #485599 by Hans
    ' To get the unique entries: change > 1 to = 1, the code will select the
    ' cells containing unique values instead.

    Dim oRange As Range
    Dim oCell As Range
    Dim oSelect As Range
    Set oRange = Range("A27").CurrentRegion
    For Each oCell In oRange.Cells
    If Application.WorksheetFunction.CountIf(oRange, oCell) > 1 Then
    If oSelect Is Nothing Then
    Set oSelect = oCell
    Else
    Set oSelect = Union(oSelect, oCell)
    End If
    End If
    Next oCell
    oSelect.Select
    Set oSelect = Nothing
    Set oCell = Nothing
    Set oRange = Nothing
    End Sub

    It selects all cells that are duplicates.
    Hope this helps.
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare in same Col (2003 sp2)

    This seems to just hang, Not sure why?


    Brad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare in same Col (2003 sp2)

    Here is a solution using conditional formatting. Let's say that the data are in A1:A100.
    Select A1:A100. A1 will probably be the active cell.
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown list.
    Enter the following formula in the box next to it:

    =COUNTIF($A$1:$A$100,A1)>1

    In this formula, A1:A100 is the range (the $ characters are essential here), and A1 is the active cell.
    Click Format...
    Specify the formatting you want for duplicates.
    Click OK twice.

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare in same Col (2003 sp2)

    Brad,

    Not sure why it hung. Did you change the macro to indicate the correct range?
    Actually, Since Hans has replied -I would just listen to him.... (smile)


    chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare in same Col (2003 sp2)

    Hans,
    the countif worked fine. I will continue to work the macro as well. If nothing else, than to see it work.


    Thanks,
    brad

Posting Permissions

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