Sorting based on count...

[mud_shark]

I have a spreadsheet which lists all of our company's outstanding order lines - currently sitting at about 30,000 lines. (We have a catalogue of about 1 million items). The catalogue number is the unique property for each product.

I'd like to be able to sort them so that the items with the most outstanding orders are listed at the top of the report. I think this means counting how many times each catalogue number appears, and then sorting this count from largest to smallest.

My aim is to see which items have the most outstanding orders, so that I can see where we have the biggest stock shortages, and can then act accordingly.

Should I be sub-totalling the catalogue numbers as a starting point?

[martindwilson]

with order numbers in col a first sort sheet by col a to get the orders together then in another column put =countif(a:a,a1) and drag down then sort sheet descending by that column

[mud_shark]

with order numbers in col a first sort sheet by col a to get the orders together then in another column put =countif(a:a,a1) and drag down then sort sheet descending by that column

Thanks for your reply.

Am unclear what the a:a represents in your formula.
A1 is presumably the cell with the unique item catalogue number.

[martindwilson]

Yep so drag that down and every thing will be counted A:A is the whole column you could use
a1:a30000 or whatever your range is

[mud_shark]

Yep so drag that down and every thing will be counted A:A is the whole column you could use
a1:a30000 or whatever your range is

Yep, got it and works well - thanks...


I'd like to take it to the next level - and use the info to provide me with a list of the items with the biggest orders (largest to smallest).

If I have (say) 500 orders for a particular item, it lists all 500 individual order lines (which I suppose is what I asked for previously).. I'd like to see some sort of summary list with only one line per item..

eg:

Product A - 500
Product B - 450
Product C - 300
..and so on..

[martindwilson]

run a pivot table report
just using product codes as source
in the table drag the field into row and data sections
youll get a pivot table with 2 columns
you can sort the pivot table
highlight all the total values
then sort descending as you would any column

[mud_shark]

Thanks martindwilson - works a treat.