Discussion Locked

Excel Formulae

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need to be able to type in a formaula in an excel spreadsheet(office 2000) that will enable me to see if all entries in a list are unique, some of these lists are a couple of thousand entries long so 'Subtotals' isn't very useful. Does anyone knowof any?
Please, please can you help.

5 replies

by mandajoy

If I understand your question completely you are looking for a unique list the simplest way to do that in excel would be using the data: filter: advanced filter: select the action of copy to another location: set the copy to range and then select Unique records only you can then see if the lists are the same lenght... or you can do a pivot table with a lay out of the column being both the row and the data (the data as a count)
hope this is what you were looking for if it isn't feel free to contact me

by steve barrow

This macro code will take the current selected cells and check them - must be in a single column for this simple code and in the range A-Z. But the code is easily modified to allow extensions. Run brom a nenu item or toolbar buttton if you like.
Sub unique()
Dim start, finish, answer As String
Dim i, j, loopcnt, loopout As Integer
start = Left(ActiveWindow.RangeSelection.Address, 4)
finish = Right(ActiveWindow.RangeSelection.Address, 4)
start = Mid(start, 2, 1) & Right(start, 1)
finish = Mid(finish, 2, 1) & Right(finish, 1)

i = Val(Right(start, 1))
j = Val(Right(finish, 1))
answer = ""
For loopout = i To j
For loopcnt = i To j
If Range(Left(start, 1) & loopout).Value = Range(Left(start, 1) & loopcnt).Value Then If loopout <> loopcnt Then
answer = "Not Unique"
End If
End If
Next loopcnt
Next loopout
If answer = "Not Unique" Then
MsgBox "not unique"
Else
MsgBox "all unique"
End If
End Sub

Hope it helps

Stephen.

This discussion is closed to new replies.