duplicate keys (Office 2000)

Topic

New Reply

How do I determine if a sorted column has duplicates

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

devious!

thanx

art

Reply | Quote

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.

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

I think Art means “Where is your VBA code?” You forgot to add it onto your post. -Sam

Reply | Quote

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

Reply | Quote

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

Reply | Quote