Excel Duplicate Formula Help

[khalidwa]

Hello,

Using Excel 2010 I can select the column of interest, click Conditional Formatting > Highlight Cell Rules > Duplicate Values

This will highlight any duplicate value in the column of interest, however it does not split them.

The column of interest is a list of phone numbers and I need to be able to find out how many times each duplicate number appears.

I'm looking for a way of doing this so the result will say

There are X duplicates of phone number 0123456789
There are X duplicates of phone number 0111111111
There are X dupliucates of phone number 022222222

Can anyone help me?

Thank you in advance,

Khalid.

[Bob Phillips]

Pivot the data, that will tell you.

[khalidwa]

Hi, so this is what I did

I select the column entitled CLI (the telephone numbers)

I selected to create a pivot table in a new worksheet

In the pivot table field list I selected CLI and on the new worksheet it now shows a long list of telephone numbers

I'm not sure how to extract what I want from this list?

[Bob Phillips]

Put CLI into the values pane as well, set the aggregate function to count, and you have a list with the number of instances. You can sort it by the count.