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

Attached Files

Barnes and Noble Nook Kids.xls (93.0 KB, 13 views)

Hi Michael,

You can use an array formula (ie a formula entered with Ctrl-Shift-Enter) like:
=SUM(IF(C3=C$1:C$500,1,))
When it's input this way, it'll look like:
{=SUM(IF(C3=C$1:C$500,1,))}
You can then copy the formula down as far as needed. As coded, it's sufficient for 500 rows.

Once you've done that, you can sort the sheet by whatever column you put the formula in. That'll give you a sheet sorted by author frequency.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

thank you macropod. I will try out that formula and see if I have any problems. My question for you. I'm new to Excel and what what those "$" signs mean and how you use them. What would happen if you left that out of the formula? What results will you get in comparison to using the "$" sign?

The $ signs in cell references make that part of the reference absolute

F I if you drag a formula across containing $F3, the $F part will not change, while the row part might depending on how you drag.

See " absolute and relative references " in XL Help - F1

Basically, if you omit the $ symbol in the formula, the cell references will change when you copy it down. As coded, copying gives:
{=SUM(IF(C3=C$1:C$500,1,))}
{=SUM(IF(C4=C$1:C$500,1,))}
{=SUM(IF(C5=C$1:C$500,1,))}
and so on. Without the $ you'd get:
{=SUM(IF(C3=C1:C500,1,))}
{=SUM(IF(C4=C2:C501,1,))}
{=SUM(IF(C5=C3:C502,1,))}
and this would compromise the formula's validity for what you're trying to achieve.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

That makes sense now. Thanks for helping out.