Need help with sorting a column by most common to least common

**mikenmike0001** · 12-05-2011, 04:09 PM

Hello,

I'm not sure how to do this. I have attached my excel spreadsheet. In the spreadsheet are column titles for categorizing books by title, author, illustrator, publisher company, isbn, etc. I have over 400 records in this spreadsheet. What I want to do is sort the 400+ records by the publisher that comes up the most in the spreadsheet to the publisher that comes up the least in the spreadsheet. So for example, in the first row of the spreadsheet, the publisher for Mickey's Spooky Night Read along book is from "Disney Book Group". If "Disney Book Group" has more than one record result in the 400+ records, I want it along with it's rows of Disney Book Group to be prioritized and placed at the top of the stack.

If you look at row 8, you will see, It's a Great Pumpkin Charlie Brown, with a publisher titled "Peanuts Worldwide". In this sorting algorithm, if this record is the only one within the 400+ records that shows up only once for Peanuts Worldwide, I want this record to be last and at the bottom of the stack.

So can someone explain to me what I have to do in order to make this all work? An excel spreadsheet example would be nice.

Thanks,

Michael

**lporter77** · 12-05-2011, 04:20 PM

Insert a title called Frequency in column L with the formula =COUNTIF($F$3:$F$425,F3) in cell L3, copy this formula down the table to the bottom. Then highlight cell L2 and click the sort button selecting frequency (descending ) and Publisher (ascending) this will sort the table

**mikenmike0001** · 12-05-2011, 04:29 PM

Thanks.

Can you explain what this formula "COUNTIF($F$3:$F$425,F3)" does? Can you break it down so that I can understand it? I want to learn how to do this from you.

Also, how do you do the sort part of your solution? I'm using Office Excel XP version. How do you sort one column descending and the other ascending at the same time without losing place of the contents in each row?

Michael

**lporter77** · 12-05-2011, 04:56 PM

The countif function will count the number of times a certain instance occurs within a range. In this case it is counting the number of times the publisher (f3) in the row the formula is in occurs within the column Publishers ($f$3:$f$425). The formula is using the $ signs to fix the range of publisher so it does not change when you copy the formula down.

If you highlight a cell in your table and right click then select sort and custom sort, select Frequency and sort type as Descending then publisher and a to z

**mikenmike0001** · 12-05-2011, 06:01 PM

Hello lporter77,

I've attached an updated excel spreadsheet with the adjustments that you recommended. I noticed something at row 38-39 that I'd like for you to check out. I'm not sure why, but in the Publisher's column, HarperCollins is sliced into two different counts according to the Frequency tab. They should be joined together. I checked the cells and they are titled the same, I don't know if it's because of a white space or something before or after the name, but excel is treating it as two different entities. Can you figure out why?

Thanks,

Michael

**lporter77** · 12-05-2011, 06:11 PM

The text in the publishers columns is not exactly the same, there is an extra space after one than the other, you'll need to make sure all the HarperCollins entries in publisher columns are identical by copying and pasting one entry into all the HarperCollins Publisher cells

**mikenmike0001** · 12-05-2011, 06:55 PM

Is there a quick way of doing this with a formula besides manually going through each cell and making the edit?

**martindwilson** · 12-05-2011, 07:05 PM

use text to columns/fixed width click finish

**martindwilson** · 12-06-2011, 04:28 PM

as per pm, select the column (column f)
then from the tool bar select "data"
then from that sub menu select "text to columns"
choose the "fixed width" option
click finish