Solved: function help

[anandbohra]

hi all

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#examples
Example 6: Count the number of unique values in a range.


based on the above example to count unique values i am trying to make my own UDF for this but unable to write perfect coding pl help me.

Function unique_count(selcell As Range)
Dim xval, i As Double
i = 0
For Each xval In selcell
i = 1 / WorksheetFunction.CountIf(selcell, selcell)
Next xval
unique_count = i

End Function

your reply will be highly appreciated

[Bob Phillips]

That example uses COUNTIF with an array, something that you can do in SUMPRODUCT. If yoiu were to write it in a UDF, you would either have to evaluate a similar worksheet function in VBA, or use some other method of capturing a single instance of each value, such as a collection, and then count them at the end.

And in summary, why bother when it is so easy in a formula.

[Charlize]

Something like this. This one informs you if a value is unique in a range or not. Could be used for sheduling persons I guess.Function unique_count(AllCells As Range, selcell As Range) As Variant
'AllCells are all the cells belonging to the range
'selcell is the cell of which you want to know
'if it's a unique value
unique_count = Application.WorksheetFunction.CountIf(AllCells, selcell)
If unique_count > 1 Then
unique_count = "Not unique"
Else
unique_count = "Unique"
End If
End Function

[Bob Phillips]

Not what is being asked as I understand it, the SP formula counts the number of unique values. And guess what, that UDF also is pointless, creating a UDF that basically just runs a worksheet function.

=IF(COUNTIF(A:A,A1)>1,"Not unqiue","Unique")

[anandbohra]

sorry guys :doh:
my query is not to found whether item is unique or not but to count number of unique values in given range.

my live example will tell u my purpose.
i have list of 719 companies for which i import data from third party database software that comes in nearby 10000 to 20000 records now problem is every time i have to count the no. of companies for which i got the records.
that is why i want one function specially UDF which counts the no. of unique records & tell me
the logic for unique records is simple.

count single value in given range & 1 divide by that value

e.g.

column A
a
a
a
a
b
b
b
d
d
c
c
e
so unique are 5 sum of this (1/4+1/4+1/4+1/4+1/3+1/3+1/3+1/2+1/2+1/2+1/2+1)
:yes

[Bob Phillips]

My response stands.

[Charlize]

Not what is being asked as I understand it, that formula counts the number of unique values. And guess what, that also is pointless, creatinga UDF that basically just runs a worksheet function.

=IF(COUNTIF(A:A,A1)>1,"Not unqiue","Unique")I know that it is a waste of time to produce something that's already built in. Unless there is a good reason to do so. I try to avoid formulas here at work because some people have a, I call it -- click click click - behaviour --, without first reading the instructions of how to use a certain sheet (I know you can protect a sheet or certain cells of it.).

Those people are a pain in the a** because they succeed in messing everything up. Most of the sheets are made and changed during several years. If I want to modify a good working solution (for 90%) to make It foolproof, they are somewhat reluctant here (as most people are when you try to change something in the workflow).

Maybe I could do it quitly because the templates are stored in a workfolder and synchronised when I change something in the workfolder.

Anyway, thanks for pointing this one out.
---
As I read the last post of the OP I understand what Xld means. Better use a collection (a collection can't have double values)

[anandbohra]

As I read the last post of the OP I understand what Xld means. Better use a collection (a collection can't have double values)

will u pl give me coding for this as i haven't used collection object so i dont have any idea about using it.

i will be thankful of u if u give me exact code which throws me unique no of records for the given range of cells.:clap:

[Charlize]

This coding is using a messagebox. Obvious you can't use this when more then 20 items are unique. Use a userform and combobox instead and add the items of the collection to those controls rather than the messagestring.Sub Unique_Items()
'AllCells is complete range - cell is item in AllCells
Dim AllCells As Range, cell As Range
'NoDupes is the collection
Dim NoDupes As New Collection
'Used for sorting
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item
'last row with data in A
Dim lastrow As Long
'message string
Dim vmessage As String
vmessage = "Unique Companies ..." & vbCrLf
lastrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
' The items are in A1:A ...
Set AllCells = Range("A1:A" & lastrow)
' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each cell In AllCells
NoDupes.Add cell.Value, CStr(cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next cell
' Resume normal error handling
On Error GoTo 0
' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i
' Add the sorted, non-duplicated items to a ListBox, combobox or msgbox
' for listbox or combobox use additem
For Each Item In NoDupes
vmessage = vmessage & Item & vbCrLf
' UserForm1.ListBox1.AddItem Item
Next Item
vmessage = vmessage & "Total unique items : " & NoDupes.Count
' Show the messagebox
MsgBox vmessage, vbInformation
End Sub

[anandbohra]

thank u very much Charlize

for elaborating code in precise manner.

:friends:

[anandbohra]

hi Charlize

just doing r&d with collection object & got this
even this also solve my purpose

Function unique(selcell As Range)
Dim xVal As Variant
Dim myColl As New Collection

For Each xVal In selcell
myColl.Add Item:=xVal, key:=xVal
Next xVal
On Error GoTo 0
unique = myColl.Count
End Function

[Charlize]

Try using your function with doubles first. When you tried that, try numbers. I suggest this coding (probably not the best): Function unique(selcell As Range) As Long
Dim xVal As Range
Dim myColl As New Collection
'Need this to continue when there are doubles
'because you can not add doubles to a collection
On Error Resume Next
For Each xVal In selcell
'So it will work for strings and numbers - CStr
myColl.Add Item:=xVal, key:=CStr(xVal)
Next xVal
'reset the errortrapping to normal means
'tell it if there is some kind of error
On Error GoTo 0
unique = myColl.Count
End Function
A cell with no value counts also as a unique value (1).