Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    This Q is not for work purposes...its just to learn and experiment on!

    I have not been able to find out how to select all duplicates in a bunch of numbers, (as in multiple select!) Also, is it possible to show me how this selection of multiple cells can be inverted so that all non duplicated values get selected. I assume a NOT operator will be involved here. The code in the sample W/B is a start!!!!

    TIA
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Rudi

    I shall start by giving you some code which you can adapt that finds duplicates in a column and then a function that will allow you to sort the colour formatted fields using A-Z. At present this only works for a single column. this came from <post#=484000>post 484000</post#>

    Sub FindDups()
    '
    ' NOTE: You must select the first cell in the column and
    ' make sure that the column is sorted before running this macro
    '
    ScreenUpdating = False
    FirstItem = ActiveCell.Value
    SecondItem = ActiveCell.Offset(1, 0).Value
    Offsetcount = 1
    Do While ActiveCell <> ""
    If FirstItem = SecondItem Then
    ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
    Offsetcount = Offsetcount + 1
    SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
    Else
    ActiveCell.Offset(Offsetcount, 0).Select
    FirstItem = ActiveCell.Value
    SecondItem = ActiveCell.Offset(1, 0).Value
    Offsetcount = 1
    End If
    Loop
    ScreenUpdating = True
    End Sub

    --------------------------------------------------------------------------------------------
    Function ColorIndexOfCell(Rng As Range, _
    Optional OfText As Boolean, _
    Optional DefaultAsIndex As Boolean = True) As Integer

    Dim C As Long
    If OfText = True Then
    C = Rng.Font.ColorIndex
    Else
    C = Rng.Interior.ColorIndex
    End If

    If (C < 0) And (DefaultAsIndex = True) Then
    If OfText = True Then
    C = GetBlack(Rng.Worksheet.Parent)
    Else
    C = GetWhite(Rng.Worksheet.Parent)
    End If
    End If
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Alternatively

    In cell H2 paste =IF(COUNTIF($B$2:B2,B2)>1,"Duplicate","Unique")

    in cell I2 paste =IF(COUNTIF($C$2:C2,C2)>1,"Duplicate","Unique")
    etc to M2

    copy down to Row 22

    You will then have a grid of Duplicate and Unique. You can then sort on thhes two values and show all your duplicates grouped together
    Jerry

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

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Here's a different take. The following procedure will select all cells containing duplicate values:

    Sub SelectDups()
    Dim oRange As Range
    Dim oCell As Range
    Dim oSelect As Range
    Set oRange = Range("B2").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

    If you change > 1 to = 1, the code will select the cells containing unique values instead.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Thanx for the time to search for this code Jezza. However, this is not what i need. I have been frustrated by how a person selects multiple cells that meet a certain criteria. The use of special cell is easy in code, and if I record a macro to select multiple cells, the recorder records Range("A1", "B610","C20").Select! This is what i am after.
    Thanx again...please know that i appreciate the reply!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Hans,
    This is what i was after. I see that the code loops through each cell, testing it with the CountIf function. Then after it has evaluated the cells it uses the Union Function to select all the ranges! I looped through the macro to watch the variable values. i see it only tests the first duplicate. (Ie Set oSelect = oCell only shows 59, which is the first duplicate.) How does the code find the other duplicates, as I do not see the cell values passed through the oCell variable?
    Also, i guess the secret to selecting various cells is getting a reference to them and using the Union Function to select them all!
    This is neat!
    Tx
    Regards,
    Rudi

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

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Some comments on the central part of the code:

    Loop through the cells
    For Each oCell In oRange.Cells
    Do we have a duplicate? (This mimics the condition you used for conditional formatting)
    If Application.WorksheetFunction.CountIf(oRange, oCell) > 1 Then
    Has oSelect been filled yet?
    If oSelect Is Nothing Then
    If not, i.e. oCell is the first duplicate encountered, set oSelect to equal oCell (as a range)
    Set oSelect = oCell
    Else
    Otherwise, add oCell to oSelect
    Set oSelect = Union(oSelect, oCell)
    End If
    End If
    Next oCell

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Well knock me down with a feather..... [make note to myself " Look up Union function when I have 5 minutes"]
    Jerry

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Thanx. this code is definetly going into my Useful Code List. This can be modified to select anything and do with it as needed!
    Great Post!!!!!!!

    This code can be called: <!post=CONDITIONAL SELECTION,485599>CONDITIONAL SELECTION<!/post>
    Regards,
    Rudi

  10. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Rudi:
    A comprehensive answer to the problem you raised can be found in "Writing Excel Macros" Authored by Steven Roman Published by O'Reilly. In addition the book is a good Excel VBA guide which I have used on several occasions to enhance my Excel VBA procedures.

    I am posting this mostly for the benefit of other Loungers that may read this post.

    Regards,
    Tom Duthie

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

    Thanx Tom.
    Regards,
    Rudi

Posting Permissions

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