If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
List top five
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 |
#2
|
|||
|
|||
List top five
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. -- Best Regards, Luke M "Dazed&Confused" wrote in message ... 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 |
#3
|
|||
|
|||
List top five
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" wrote in message ... 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 |
#4
|
|||
|
|||
List top five
Dazed&Confused wrote:
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 If you are not familiar with PivotTables, look he http://peltiertech.com/Excel/Pivots/pivotstart.htm |
#5
|
|||
|
|||
List top five
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 "Glenn" wrote: Dazed&Confused wrote: 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 If you are not familiar with PivotTables, look he http://peltiertech.com/Excel/Pivots/pivotstart.htm . |
Thread Tools | |
Display Modes | |
|
|