Excel sorting --- Sort by highest occurance of item

I have an excel spreadsheet with 5 columns of data.  Column two contains names.  I would like to order the sheet in descending order of who has the most number of occurances of their name.

How could I do this?

I thought of putting a number in front of the name to specify the order in the list but the list changes as items are added and manually changing each number as things change order is not appealing.
LVL 9
Sean MeyerIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

with your names in column A and assuming a header row, use a new column with

=countif(A:A,A2)

Copy down. That will count the occurrencs of each name. Now sort by this column.

cheers, teylyn
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Sort by name first, then by the count column.

cheers, teylyn
jppintoCommented:
Use a new column to get a count of the number of times the names on column B appears and use this to sort Descending. Take a look at the attached example.

jppinto
Book2.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

reitzenCommented:
http://www.dailydoseofexcel.com/archives/2010/09/03/building-a-self-sorting-list/

This is an excellent article that should give you exactly what you are looking for.
Sean MeyerIT DirectorAuthor Commented:
jppinto  Thank you for the attached Excel sheet.  Why do you use the $ signs and teylyn did not?  They both work the same but I figure there is a reason for the added $

reitzen  -- The article to which you pointed was not doing any counting.  It was sorting some sort of alphabetical with spaces or blanks.
jppintoCommented:
$ is used on formula to "fix" the cell references. If you make a formula like this on cell A1:

=SUM(B1:B5)

and you copy this to a new cell, say to C3, it will change the cells references to this:

=SUM(D3:D7)

If you put the $ on your original formula, like this:

=SUM($B$1:$B$5)

it wil mantain the reference to the B1:B5 range and will not change it to D3:D7.

jppinto
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,
thanks for the grade. I did not need to use $ signs, since my formula references the whole column anyway, and that does not change when copied down.

cheers, teylyn
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.