Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing List A to List B and add what's missing from List B | Excel Discussion (Misc queries) | |||
Comparing List A to List B and add what's missing from List B | Excel Discussion (Misc queries) | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions |