Counts of unique keys in a list
 

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

Counts of unique keys in a list
 

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

Select the range A1:A10
Goto the menu DataFilterAdvanced 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) :D6
Sort the range D2:D6 ascending
Enter this formula in E2:

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

Copy down to E6

Biff

"gazza67" wrote in message
ups.com...
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

Counts of unique keys in a list
 

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

"gazza67" wrote:

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

Counts of unique keys in a list
 

Another method: Select a cell outside the table and choose
dataconsolidate 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.



On Jan 28, 6:24 am, "gazza67" wrote:
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

Counts of unique keys in a list
 

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"
wrote in message
Another method: Select a cell outside the table and choose
dataconsolidate 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.

Counts of unique keys in a list
 

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


"Jim Cone"
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"
wrote in message
Another method: Select a cell outside the table and choose
dataconsolidate 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.

Counts of unique keys in a list
 

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.



On Jan 28, 10:19 pm, "Jim Cone" wrote:
Wrong conclusion, did some comparisons.
Using a pivot table is much, much faster than using Consolidate.
Jim Cone

"Jim Cone"
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, USAhttp://www.realezsites.com/bus/primitivesoftware

"Lori"
wrote in message
Another method: Select a cell outside the table and choose
dataconsolidate 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.