thmh
asked on
test if values are unique vba
hy ,
how to test if values in 4 cells are unique
A B C D
1 4 9 7 5
something like :
if A1 <>B1<>C1<>D1 then
how to test if values in 4 cells are unique
A B C D
1 4 9 7 5
something like :
if A1 <>B1<>C1<>D1 then
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what if cells are in different locations
A BD CP DJ
1 4
2 9
3 3
5 7
A BD CP DJ
1 4
2 9
3 3
5 7
If you want just a formula, perhaps this will do it:
=IF(OR(COUNTIF(A1:D1,A1)<> 1,COUNTIF( A1:D1,B1)< >1,COUNTIF (A1:D1,C1) <>1,COUNTI F(A1:D1,D1 )<>1),"Dup licates present","No duplicates")
Patrick(ab)
=IF(OR(COUNTIF(A1:D1,A1)<>
Patrick(ab)
You could use an array-entered formula like:
=SUM(IFERROR(1/COUNTIF(A1: D1,A1:D1), 0))=COUNTA (A1:D1) returns either TRUE or FALSE; requires Excel 2007 or later
=SUM(IF(ISERROR(1/COUNTIF( A1:D1,A1:D 1)),0,1/CO UNTIF(A1:D 1,A1:D1))) =COUNTA(A1 :D1) works in any version of Excel
To array-enter a formula:
1) Click in the formula bar
2) Hold the Control and Shift keys down
3) Hit Enter
4) Release all three keys
Excel should respond by adding curly braces { } surrounding the formula. If not, repeat steps 1 through 4.
If you prefer a user-defined function, I posted one in the snippet.
=SUM(IFERROR(1/COUNTIF(A1:
=SUM(IF(ISERROR(1/COUNTIF(
To array-enter a formula:
1) Click in the formula bar
2) Hold the Control and Shift keys down
3) Hit Enter
4) Release all three keys
Excel should respond by adding curly braces { } surrounding the formula. If not, repeat steps 1 through 4.
If you prefer a user-defined function, I posted one in the snippet.
Function NoDupes(Values As Variant) As Boolean
Dim v As Variant
Dim coll As New Collection
On Error Resume Next
For Each v In Values
If v <> "" Then coll.Add CStr(v), CStr(v)
Next
NoDupes = Err = 0
On Error GoTo 0
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
tnx all
matthewspatrick solutions is best for what i need
matthewspatrick solutions is best for what i need
thmh - Tks4pts - Patrick(ab)
The code below is in the attached file. Try changing one of the numbers so that it is the same as another.
Hope it helps
Patrick
Open in new window
thmh-04.xls