Counts of unique keys in a list

[gazza67]

Hi,

I have a collection of of keys that I wish to summarise;

a
z
b
c
d
a
c
c
d


I would like to produce the following (preferably in alphabetical
order) summary AND graph the results (i think this means I cant use
subtotal)

a - 2
b - 1
c - 3
d - 2
z - 1

I would really appreciate any help on this as I really dont know much
about excel

Thanks,

Gary

 

[T. Valko]

Assume your data is in the range A2:A10 and cell A1 is the header.

Select the range A1:A10
Goto the menu Data>Filter>Advanced filter
Select: Copy to another location
Copy to: for demo purposes, we'll use cell D1 so, enter D1.
Select: Unique records only
OK

Now, (based on your sample) the uniques have been extracted and are in the
range D1 (header) 6
Sort the range D26 ascending
Enter this formula in E2:

=COUNTIF(A$2:A$10,D2)

Copy down to E6

Biff

 

[Guest]

Hi Gray,
Please do the following:

Let's assume that your inputs starts from A1 to A15, then your results cells
starts from A20 in alphabitical order.

A20 = A
A21 = B
A22 = C
A23= D

then on B20 put this formula: COUNTIF(A1:A15,A20) Or: COUNTIF(A1:A15,"A")

and then drag down the formula for other letters.

Hope this help you.

Daoud Fakhry

 

[Lori]

Another method: Select a cell outside the table and choose
data>consolidate on the range with 'labels in left column' and 'count'
selected. This method needs more than one column of data so add a copy
of data to the left if necessary.

 

[Jim Cone]

Thanks for posting that.
I hadn't looked at the Consolidate method in years.
I have been using pivot table code to count instances.
Consolidate appears to be simpler, faster code then that required for a pivot table.
Must be a catch somewhere. <g>
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Lori" <[email protected]>
wrote in message
Another method: Select a cell outside the table and choose
data>consolidate on the range with 'labels in left column' and 'count'
selected. This method needs more than one column of data so add a copy
of data to the left if necessary.

 

[Jim Cone]

Wrong conclusion, did some comparisons.
Using a pivot table is much, much faster than using Consolidate.
Jim Cone


"Jim Cone" <[email protected]>
wrote in message Thanks for posting that.
I hadn't looked at the Consolidate method in years.
I have been using pivot table code to count instances.
Consolidate appears to be simpler, faster code then that required for a pivot table.
Must be a catch somewhere. <g>
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Lori" <[email protected]>
wrote in message
Another method: Select a cell outside the table and choose
data>consolidate on the range with 'labels in left column' and 'count'
selected. This method needs more than one column of data so add a copy
of data to the left if necessary.

 

[Lori]

Agreed, pivot tables are significantly faster but it's worth taking
another look at the consolidate method as it has some little known
features which can be very useful and reduce the need for coding. For
example you can:

- apply to a range with with existing row and column headings e.g. to
match two lists.
- use wildcards for filenames such as: *!data to reference all files
in a directory
- check the create links option with count and labels in top row and
then remove subtotals to combine data from similar workbooks.