General discussion

Locked

Excel Formulae

By wills.jason ·
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.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel Formulae

by mandajoy In reply to Excel Formulae

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

Collapse -

Excel Formulae

by wills.jason In reply to Excel Formulae

The question was auto-closed by TechRepublic

Collapse -

Excel Formulae

by Steve Barrow In reply to Excel Formulae

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.

Collapse -

Excel Formulae

by wills.jason In reply to Excel Formulae

The question was auto-closed by TechRepublic

Collapse -

Excel Formulae

by wills.jason In reply to Excel Formulae

This question was auto closed due to inactivity

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums