Count unique numbers for execs

Hi, I have a column with Client Reference Numbers(CRN) in. Next to this is a
column of executives for the particual CRN. The CRN can appear more than
once. Please can someone explain how I can get excel to list the execs then
group count how many unique CRNs there are in the list as well as count how
many times the CFN shows. For example:
Exec CRN
AA 1234
BB 5678
BB 8888
AA 1234
CC 1111
CC 2222
AA 3333
DD 5554
CC 1111
CC 1111

Results:
AA: 1234: 2
3333: 1
Total CRN for AA: 3
BB: 5678: 1
8888: 1
Total CRN for BB: 2
CC: 1111: 3
2222: 1
Total CRN for CC: 4
DD: 5554: 1
Total CRN for DD: 1

I hope you get the idea.
I've tried all sorts of Pivot tables etc. Please help.
Thanks,
Daniel.

Hi Daniel

A pivot Table works for me
Drag Exec to the Row area
Drag CRN to the row area and place to the right of Exec
Drag CRN to the Data Area, double click on Field and set to Count instead of
Sum.

Regards

Roger Govier


DanielJW wrote:
> Hi, I have a column with Client Reference Numbers(CRN) in. Next to this is a
> column of executives for the particual CRN. The CRN can appear more than
> once. Please can someone explain how I can get excel to list the execs then
> group count how many unique CRNs there are in the list as well as count how
> many times the CFN shows. For example:
> Exec CRN
> AA 1234
> BB 5678
> BB 8888
> AA 1234
> CC 1111
> CC 2222
> AA 3333
> DD 5554
> CC 1111
> CC 1111
>
> Results:
> AA: 1234: 2
> 3333: 1
> Total CRN for AA: 3
> BB: 5678: 1
> 8888: 1
> Total CRN for BB: 2
> CC: 1111: 3
> 2222: 1
> Total CRN for CC: 4
> DD: 5554: 1
> Total CRN for DD: 1
>
> I hope you get the idea.
> I've tried all sorts of Pivot tables etc. Please help.
> Thanks,
> Daniel.

Thanks. A great help.

"DanielJW" wrote:

> Hi, I have a column with Client Reference Numbers(CRN) in. Next to this is a
> column of executives for the particual CRN. The CRN can appear more than
> once. Please can someone explain how I can get excel to list the execs then
> group count how many unique CRNs there are in the list as well as count how
> many times the CFN shows. For example:
> Exec CRN
> AA 1234
> BB 5678
> BB 8888
> AA 1234
> CC 1111
> CC 2222
> AA 3333
> DD 5554
> CC 1111
> CC 1111
>
> Results:
> AA: 1234: 2
> 3333: 1
> Total CRN for AA: 3
> BB: 5678: 1
> 8888: 1
> Total CRN for BB: 2
> CC: 1111: 3
> 2222: 1
> Total CRN for CC: 4
> DD: 5554: 1
> Total CRN for DD: 1
>
> I hope you get the idea.
> I've tried all sorts of Pivot tables etc. Please help.
> Thanks,
> Daniel.