Possible to calcilate COUNT and sort to show most frequest rows 1st ?

[Anupam Sharma]

I have a worksheet with Names as one of the columns. I need to display the
worksheet to:
- show the frequency (count), and
- sort such that the rows for the most frequent Name appear 1st, and
then the next frequent etc.

Is there a way?

Regards.

 

[Dan E]

Anupam,

Assuming your list of names is in column A rows 1:100
In B1 Put
=COUNTIF($A$1:$A$100, "=" &A1)
In C1 Put
=RANK(B1,$B$1:$B$7)
Copy B1 and C1 down for all of your rows

Select A1:C100
Choose "Data" -> "Sort"
Sort first according to column A to get all matching names
together. (has to be done so that names appear together
if 2 names occur the same number of times)
Sort again according to column B to get the most frequent
names at the top of the list.

Dan E

 

[Beth]

I suggest you use a pivot table. Assuming your column
heading is labelled "Names" and is in column A....

Highlight column A.
Click Data, Pivot Table and Pivot Chart Report.
Click next, next, then Layout.
Drag the Names item to the row area, and drag it again to
the Data area.
Click OK, Finish.
On your pivot table, Double Click the Names field label.
Click Advanced.
Switch to Ascending under AutoSort options.
Select Count of Names under Using Field.
Click Ok, Ok.

That should do it.

 

[Anupam Sharma]

Hi Beth,

Thank you for the detailed answer. Works well.

Regards.

 

[Anupam Sharma]

Hi Dan,

Thanks for the response. Had trouble duplicating it
though. Wamy try it again later. For now, I decided to
use the Pivot table solution posted by Beth.

Regards.

-----Original Message-----
Anupam,

Assuming your list of names is in column A rows 1:100
In B1 Put
=COUNTIF($A$1:$A$100, "=" &A1)
In C1 Put
=RANK(B1,$B$1:$B$7)
Copy B1 and C1 down for all of your rows

Select A1:C100
Choose "Data" -> "Sort"
Sort first according to column A to get all matching names
together. (has to be done so that names appear together
if 2 names occur the same number of times)
Sort again according to column B to get the most frequent
names at the top of the list.

Dan E

"Anupam Sharma" <[email protected]> wrote in

message [email protected]...