Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    NJ
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    duplicate keys (Office 2000)

    How do I determine if a sorted column has duplicates

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate keys (Office 2000)

    This array formula counts the number of unique entries in range A1:A6:

    =SUM(1/COUNTIF(A1:A6,"=" & A1:A6))
    (press control-shift enter when entering this formula!)
    Use
    =CountA(A1:A6)
    to count all entries. If there is a difference, you have duplicates.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    NJ
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate keys (Office 2000)

    I tried your array formula and it worked - thanx

    I would like to understand what the array formula is doing

    Why the construct "1/countif....)"? Does this mean "1 divided by the result of the countif"?
    I would guess not.

    I assume that the first a1:a6 represents the range being checked.

    I'm guessing that the stuff following the comma breaks down into "=a1 or =a2 or =a3 or =a4 or =a5 or =a6". How does it know to check "unique" occurrences?

    Why the control-shift-enter (i read in a book that this places the formula in each of the array cells, but I don't know what that means)!

    The counta - now that's something i can understand

  4. #4
    Lounger
    Join Date
    Sep 2001
    Location
    NJ
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate keys (Office 2000)

    I'm new to this lounge! where is the VBA code located? How can I access it?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate keys (Office 2000)

    Open the workbook and then press Alt+F11. That should put you in the VBA Editor (known as VBE). Make sure that your workbook is selected in the project explorer at the left of the screen, and then select "Module" from the Insert menu. You should now have a Module in the right window where you can put VBA code.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate keys (Office 2000)

    An array formula can operate on an array of numbers, thus avoiding the need to copy a normal formula accross several cells (which is possible of course and yields the same result). In short, a well devised array formula does a calculation in one cell, which would otherwise require a whole table. One tells XL a formula is an array formula by pressing control-shift-enter.

    The array formula works like this:

    - for each entry in A1:A6, it counts how many times it occurs. Then it reciprocates the result. If the cells contain:

    A,A,B,B,B,C

    then the resulting array of values of the 1/countif function is:

    1/2 , 1/2, 1/3 , 1/3 , 1/3 , 1/3 , 1

    Summing this array yields 3, the number of unique entries.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: duplicate keys (Office 2000)

    I think Art means "Where is your VBA code?" You forgot to add it onto your post. <img src=/S/doh.gif border=0 alt=doh width=15 height=15> -Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate keys (Office 2000)

    This VBA code will tell you which cells in the current selection are duplicates. It expects the selection to be in a single column, and to be sorted. It also uses one cell beyond the end of the selection, so that cell should not be the same as the last cell in the selection or it will give a false hit.

    <pre>Public Sub FindDups()
    Dim oCell As Range
    For Each oCell In Selection
    If oCell.Value = oCell.Offset(1, 0).Value Then
    MsgBox oCell.Address & " and " & oCell.Offset(1, 0).Address & " are duplicates."
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate keys (Office 2000)

    OOPS! Thanks. I have edited my message and inserted the code.
    Legare Coleman

  10. #10
    Lounger
    Join Date
    Sep 2001
    Location
    NJ
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate keys (Office 2000)

    devious!

    thanx

    art

  11. #11
    Lounger
    Join Date
    Sep 2001
    Location
    NJ
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate keys (Office 2000)

    thanx, it worked very nicely.
    I'm going to try to use this as a springboard for learning the use of macros

Posting Permissions

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