List top five

  • Thread starter Thread starter Dazed&Confused
  • Start date Start date
D

Dazed&Confused

Hi there,
I've searched this site looking for an answer for this but no luck

What I have is imported list of invoices thousands of rows long with about
450 suppliers occurring many times over. What I want is a formula or function
that will give me the top 5 suppliers - something like (MAX(Count,1 ,2,
,3...??
It seems it should be pretty straightforward but it's beaten me
(I can do this easily with a filter but I've been told they find them
confusing!)

Many thanks for your help

Paul
 
SInce you have your data in rows already, you could create a PivotTable
which has Supplier Names as a row field, and Count of supppleer as the data
field. Sort data field descending.
 
You want a Pivot Table. Sort by whatever determines the largest supplier
(count? amount purchased?). You can then limit the number displayed it you
want.

Regards,
Fred
 
Dazed&Confused said:
Hi there,
I've searched this site looking for an answer for this but no luck

What I have is imported list of invoices thousands of rows long with about
450 suppliers occurring many times over. What I want is a formula or function
that will give me the top 5 suppliers - something like (MAX(Count,1 ,2,
,3...??
It seems it should be pretty straightforward but it's beaten me
(I can do this easily with a filter but I've been told they find them
confusing!)

Many thanks for your help

Paul
Click to expand...


If you are not familiar with PivotTables, look here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
 
Back in the office again
I know I can easliy do this with a pivot table but this information will sit
in the middle of a report and I'm trying to avoid the the need for them to
remember to refresh it (because they always forget and send out the report
with the wrong information).

Many thanks

Paul
 
Back
Top