Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

unique counts

  Asked By: Darcy    Date: Mar 23    Category: MS Office    Views: 1211
  

I have 3000+ rows of employees. In another column I have a list of
their managers. Some of the employees report to the same managers. I
need to know how many different managers are in the column.

I know I can use unique filters on the managers, save to a temp
column, get the count, and then delete the temp column, but is there
more direct function to use?

I've tried all the Subtotal() functions to no avail.

Share: 

 

15 Answers Found

 
Answer #1    Answered By: Isaac Evans     Answered On: Mar 23

Try array-entering a formula like this:

=SUM(IF(COUNTIF(A1:A10,A1:A10)=0,"",1/COUNTIF(A1:A10,A1:A10)))

 
Answer #2    Answered By: Erin Dunn     Answered On: Mar 23

I entered the formula using Control - Shift - Enter (in case some of our
members don't know how to enter arrays) and it works.

However, any time I update a manager, it takes several minutes for the array
to recalculate the 3000+ lines. Can you think of a faster method?

 
Answer #3    Answered By: Ana Bradley     Answered On: Mar 23

: However, any time I update a manager, it takes several minutes
: for the array to recalculate the 3000+ lines. Can you think of a
: faster method?

How about tracking the unique  manager names in a dictionary
object? Use this as a user defined function. I named the test
range "managers". This is a generic function. It should work on
any range of values.

=UniqueCount(managers)

Function UniqueCount(rngCells As Range) As Integer

Dim vCell As Variant

' Setup dictionary
Dim Unique As Object
Set Unique = CreateObject("Scripting.Dictionary")

' Populate dictionary
For Each vCell In rngCells
If Not Unique.exists(vCell.Value) Then Unique.Add vCell.Value,
vCell.Value
Next vCell

' Count unique items
UniqueCount = Unique.Count

' Clean up
Set Unique = Nothing

End Function

 
Answer #4    Answered By: Bernard Gutierrez     Answered On: Mar 23

I get a compile error: 'Next without For' when using the function.
Any recommendations?

 
Answer #5    Answered By: Vilhelm Fischer     Answered On: Mar 23

: I get a compile error: 'Next without For' when using the
: function. Any recommendations?

I suspect it is an error in copying long lines from an
email message. Try changing the "Populate dictionary"
section to this.

' Populate dictionary
For Each vCell In rngCells
If Not Unique.exists(vCell.Value) Then
Unique.Add vCell.Value, vCell.Value
End If
Next vCell

 
Answer #6    Answered By: Kyle Fox     Answered On: Mar 23

added the 'EndIf' as recommended. Now getting #VALUE for the function.

 
Answer #7    Answered By: Leonard Pierce     Answered On: Mar 23

Perhaps you should post the whole function (copy from the IDE and paste into
the email) and we can try to spot what is wrong.

 
Answer #8    Answered By: Elisabeth Bell     Answered On: Mar 23

Copied straight out of the VB module:
Function UniqueCount(rngCells As Range) As Integer

Dim vCell As Variant

' Setup dictionary
Dim unique  As Object
Set Unique = CreateObject("Scripting. Dictionary")

' Populate dictionary
For Each vCell In rngCells
If Not Unique.exists(vCell.Value) Then
Unique.Add vCell.Value, vCell.Value
End If
Next vCell

' Count unique items
UniqueCount = Unique.Count

' Clean up
Set Unique = Nothing

End Function


Calling the function in cell B4 with a named range (MyRange) of text values;
=UniqueCount(MyRange)
also tried manually setting a range of cells:
UniqueCount(A2:A7)

 
Answer #9    Answered By: Midissia Lopez     Answered On: Mar 23

You seem to have a space after the full stop in ("Scripting. Dictionary"). It
should be
("Scripting.Dictionary")

 
Answer #10    Answered By: Sebastien Anderson     Answered On: Mar 23

Issue identified as a [space] character in the ("Scripting.Dictionary") line
item.
I copied & pasted from the original code that resulted in:
("Scripting.[space]Dictionary")

 
Answer #11    Answered By: Hu Chalthoum     Answered On: Mar 23

Can this function be updated to return the list of unique  values?
I was wondering about the option for feeding the function the defined name I'd
like the unique values to be returned to. I'd like to 'send' the list to a
drop-down input control list.

Any thoughts?

 
Answer #12    Answered By: Danny Perkins     Answered On: Mar 23

: Can this function be updated to return the list of unique
: values?

Yes.


: I was wondering about the option for feeding the function the
: defined name I'd like the unique  values to be returned to. I'd
: like to 'send' the list to a drop-down input control list.

That shouldn't be a problem. The object "Unique" holds the
names of the managers. Read up on looping through a dictionary
object and adding items to a drop down list.

 
Answer #13    Answered By: Riley-jack Johnson     Answered On: Mar 23

I too couldn't get the function to work, but used the code as a macro instead
having the number is a cell. I was planning on using the cell as a refference in
a macro but this works just as well for me. What I needed it for was the macro I
have breaks out a different Excel file for each manager from the master file.
Now they want to know the status of the breakouts. ie "Current_File of
Unique_Count". I can get the rest of the data processed. I will be looking into
the best way to use a splash screen to update the users. If you have any
thoughts on this.

Sub Unique_Managers()
Dim unique  As Object

Set Unique = CreateObject("Scripting.Dictionary")
EndOfData = Cells(Rows.Count, 1).End(xlUp).Row

' Populate dictionary
For Each vCell In Range(Cells(3, 1), Cells(EndOfData, 1))
If Not Unique.exists(vCell.Value) Then
Unique.Add vCell.Value, vCell.Value
End If
Next vCell

' Count unique items
Unique_Count = Unique.Count

' Clean up
Set Unique = Nothing
End Sub

 
Answer #14    Answered By: Kawthar Malik     Answered On: Mar 23

: If you have any thoughts on this.

The code you have provided will lead you down the road to
spaghetti code. As a programmer, much of your time is spent
maintaining old code, not writing new code. Worse, you will be
maintaining someone else's old code.

Don't assume that the code you are writing today will be your
own code in the future. You will be a different programmer in six
months and you will not understand why or how you wrote what you
wrote. There is no better way to make code more maintainable for
your future self than to use good programming practices today and
to write it with that other programmer in mind.

This code doesn't not accomplish anything unless you are using
EndOfData and Unique_Count outside the sub. If you are, then you
need a function, not a sub.

If you need a result, learn how to write functions. Whether
they are user defined functions in sheets or functions in
programs, never use a VBA sub in place of a VBA function.

Turn on Option Explicit and declare all your variables.
Declare them in the smallest procedure possible. It is doubtful
you need EndOfData or vCell outside the sub. Declare it inside the
sub. It will not be available outside the sub or function.


: Sub Unique_Managers()
: Dim unique  As Object

The norm is to indent the code inside a procedure, not to
indent the first line of a procedure. A two or four space tab is
most common. This is a standard programming practice. Write it
like this. Keep the same indent until you get to the End Sub
statement.

Sub Unique_Managers()
Dim Unique As Object


Programs are written for other humans to read. If you use an
odd convention, other programmers are going to wonder why. For
example, why is the second line not indented and the first line
indented two spaces?

This is not consistent indentation.

: Set Unique = CreateObject("Scripting.Dictionary")
: EndOfData = Cells(Rows.Count, 1).End(xlUp).Row


This is consistent indentation.

Set Unique = CreateObject("Scripting.Dictionary")
EndOfData = Cells(Rows.Count, 1).End(xlUp).Row


Each group of lines in a program is called paragraph. Related
lines tend to fall in the same paragraph. What is the relationship
between Unique and EndOfData? There is no right or wrong answer.
If you see a relationship, there is a relationship. If you do not
see a relationship, add white space between the lines.

I hate that you choose to indent the comment two spaces in
each paragraph, but you are consistent.


: ' Populate dictionary
: For Each vCell In Range(Cells(3, 1), Cells(EndOfData, 1))
: If Not Unique.exists(vCell.Value) Then
: Unique.Add vCell.Value, vCell.Value
: End If
: Next vCell

: ' Count unique items
: Unique_Count = Unique.Count
:
: ' Clean up
: Set Unique = Nothing
: End Sub


But, you have indented some blocks of code and not others. To
maintain consistency, the code should look like this. Yes, that
extra four space indent is important. Even in an email message. It
screams, "I am competent."

' Populate dictionary
For Each vCell In Range(Cells(3, 1), Cells(EndOfData, 1))
If Not Unique.exists(vCell.Value) Then
Unique.Add vCell.Value, vCell.Value
End If
Next vCell

' Count unique items
Unique_Count = Unique.Count

' Clean up
Set Unique = Nothing

End Sub


Spaghetti code leads to errors which are almost impossible to
debug. It is called spaghetti code because tracing the value of
variables which are not properly passed into and out of other
procedures is like finding the other end of a piece of pasta on a
plate. You would be amazed how many tiny little programs become
million line behemoths full of code no one can debug.

It looks like you are using Unique_Count in another part of
your program. If that is the case and if that is all you need then
this procedure should be a function, not a sub.

You will not become a better VBA programmer until you can
write functions. If my guess is correct, change this back to a
function. Don't give up and make it a sub. You are robbing
yourself and your organization by doing that.


This is not consistent indentation.

: Sub Unique_Managers()
: Dim Unique As Object
:
: Set Unique = CreateObject("Scripting.Dictionary")
: EndOfData = Cells(Rows.Count, 1).End(xlUp).Row
:
: ' Populate dictionary
: For Each vCell In Range(Cells(3, 1), Cells(EndOfData, 1))
: If Not Unique.exists(vCell.Value) Then
: Unique.Add vCell.Value, vCell.Value
: End If
: Next vCell
:
: ' Count unique items
: Unique_Count = Unique.Count
:
: ' Clean up
: Set Unique = Nothing
: End Sub


This is consistent indentation.

Sub Unique_Managers()
Dim Unique As Object

Set Unique = CreateObject("Scripting.Dictionary")
EndOfData = Cells(Rows.Count, 1).End(xlUp).Row

' Populate dictionary
For Each vCell In Range(Cells(3, 1), Cells(EndOfData, 1))
If Not Unique.exists(vCell.Value) Then
Unique.Add vCell.Value, vCell.Value
End If
Next vCell

' Count unique items
Unique_Count = Unique.Count

' Clean up
Set Unique = Nothing

End Sub

Gee, I sure hope EndOfData and vCell are not used somewhere
else in the script for some other reason. If one is, this sub just
clobbered that value. Hope it wasn't important!

This code operates on a specific range in a spreadsheet. It
can never be used to find the unique number of managers on another
sheet and the manager list can never be moved. It will break if
Excel it is used properly. That is not a good thing.

EndOfData = Cells(Rows.Count, 1).End(xlUp).Row

' Populate dictionary
For Each vCell In Range(Cells(3, 1), Cells(EndOfData, 1))


The function I wrote originally, will find the unique number
of items in any range, not just the managers range. It was
independent of the where the managers range was maintained. It
operated just as well on rows as on columns and accepted mutiple
rows and columns. There is nothing wrong with specialization, but
use it wisely.

One last note. Write sub and functions to do one thing and to
do only that one thing well. If you need to do two tasks, write
two procedures.

 
Answer #15    Answered By: Anthony Smith     Answered On: Mar 23

Not via formula, at least not that I've seen. A refreshable pivot
table might be easier, since you can control when it refreshes. Do
you need any statistics by manager? A pivot table to get those
statistics would also have your unique  list (and count) there.

 
Didn't find what you were looking for? Find more on unique counts Or get search suggestion and latest updates.




Tagged: