If condition in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.
I have a column in a Sheet (A10:A24) which has integer numbers (from 1 to
80). I would like to apply in the bottom cell A25 the condition: If all
numbers in column A10:A24 are different each other return “OK†else return
“Râ€.
Can anyone help me?
Thanks.
 
You can check unique list in 2 ways.

1 Worksheet formula
2 UDF (user defined function)

1 Worksheet Formula
Put this formula in cell A25
=IF(INT(COUNTA(A10:A24)/SUMPRODUCT(COUNTIF(A10:A24,A10:A24))),"OK","R")

2 UDF (User Defined Function)
Put below code in Module, and in cell A25 put this formula
=IF(isunique(A10:A24),"OK","R")


If not familiar with UDF, VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm





Function isUnique(rng As Range) As Boolean

Dim myListCount, app As Application
Set app = Application
myListCount = app.CountA(rng)

'use if condition to validate blank range
'otherwise for blank range it shows error #Value!
'if range is blank return 1=true or 0=false
'1= list is uniqe
'0=list is not uniqe

'If myListCount = 0 Then
'isUnique = 0 ' or 1 'Change to suit
'Else
isUnique = myListCount \ app.SumProduct(app.CountIf(rng, rng))
'End If

End Function



Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)
If You Can't Excel with Talent, Triumph with Effort.



Keywords : check duplicate entery, repeat items,
 
Thank you both.

shah shailesh said:
You can check unique list in 2 ways.

1 Worksheet formula
2 UDF (user defined function)

1 Worksheet Formula
Put this formula in cell A25
=IF(INT(COUNTA(A10:A24)/SUMPRODUCT(COUNTIF(A10:A24,A10:A24))),"OK","R")

2 UDF (User Defined Function)
Put below code in Module, and in cell A25 put this formula
=IF(isunique(A10:A24),"OK","R")


If not familiar with UDF, VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm





Function isUnique(rng As Range) As Boolean

Dim myListCount, app As Application
Set app = Application
myListCount = app.CountA(rng)

'use if condition to validate blank range
'otherwise for blank range it shows error #Value!
'if range is blank return 1=true or 0=false
'1= list is uniqe
'0=list is not uniqe

'If myListCount = 0 Then
'isUnique = 0 ' or 1 'Change to suit
'Else
isUnique = myListCount \ app.SumProduct(app.CountIf(rng, rng))
'End If

End Function



Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)
If You Can't Excel with Talent, Triumph with Effort.



Keywords : check duplicate entery, repeat items,
Click to expand...
 
Back
Top