Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Hi all (asking here because this meta question told me to).

I have some data in an excel spreadsheet here. It's no more than a table with about five columns.

Year Purchased  Manufacturer            Model           Num  Unit Price      Total Price
        2007    SMARTBOX                FuturePad XP    1    £2,915.00   £2,915.00 
        2007    Attainment Company Inc  Go Talk 9+      1    £104.00 
        2007    Attainment Company Inc  Go Talk 9+      1    £104.00     
        2007    Attainment Company Inc  Go Talk 9+      1    £104.00     £104.00 
        2007    Attainment Company Inc  Go Talk 20+     1    £114.00     £114.00 

I'd like to be able to build a 'top ten' of either manufacturers or models (and I'd like to be able to do it by either most mentioned, most sales, or highest value of sales) - but I've got no idea what the best method is in excel. Any suggestions...?

The ideal output might be a set of sells that says something like

Company Units
A   5342
B   232
C   2
D   1
share|improve this question
    
Do you want to sum the Num separately for each Manufacturer, and then sort this list ascending by this Sum of Num, and return first 10 records? It also can be done purely with formulas, but if it is one-time only, it might be easier to build SQL query for that. –  Adam Ryczkowski Sep 24 '12 at 14:57
    
Is the table you have provided (A 5342, B 232, etc) the actual results you expect from the data you have provided? I think the largest total number of Number (various Models) from any one Manufacturer other than #N/A may be 176 from Ablenet. –  pnuts Sep 24 '12 at 21:30
    
So - yes, effectively sum the num separately for each manufacturer and then sort (just taking the top ten would be great but not vital) - would be great to get some insight into the best method for doing this so that it's resilient to changes in the data - yes Ablenet probably at the top - the sample data was just to paint a bit of a picture - sorry that wasn't more clear... –  Joe Sep 24 '12 at 21:35
    
Have only just seen your reply (without @pnuts I was not notified). –  pnuts Apr 11 '13 at 23:05

2 Answers 2

Use a PivotTable and filter one or other column on Top 10:

SU478226 example.

share|improve this answer

The most straight-forward way of doing this is with manual sort of the array. But I guess it is not what you ask.

If you prefer having it done purely with formulas the solution is not short nor obvious, but it is doable (In fact I do it almost on a daily basis).

So, if you are ready to have extra 6 long columns dedicated to it with complicated array formulas and indirect referencing and some penalty in computation time when re-calculating (fortunately not noticable before hundreds of rows) than I can give you the answer, just tell me so.

share|improve this answer
    
I think that a manual sort would not help - I've edited the question so this is clear... :) –  Joe Sep 24 '12 at 9:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.