# Thread: duplicate keys (Office 2000)

1. ## duplicate keys (Office 2000)

How do I determine if a sorted column has duplicates

2. ## 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.

3. ## 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. ## Re: duplicate keys (Office 2000)

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

5. ## 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.

6. ## 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.

7. ## 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

8. ## 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>

9. ## Re: duplicate keys (Office 2000)

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

devious!

thanx

art

11. ## 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
•