Prev: Sharing Violation
Next: Index/Lookup Function?
From: Dazed&Confused on 15 Mar 2010 11:58
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
From: Luke M on 15 Mar 2010 12:16
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" <DazedConfused(a)discussions.microsoft.com> wrote in message
news:385285E8-EB0F-436C-BB82-6CA43EE0A854(a)microsoft.com...
> 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


From: Fred Smith on 15 Mar 2010 12:20
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" <DazedConfused(a)discussions.microsoft.com> wrote in message
news:385285E8-EB0F-436C-BB82-6CA43EE0A854(a)microsoft.com...
> 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

From: Glenn on 15 Mar 2010 12:49
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 here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
From: Dazed&Confused on 16 Mar 2010 10:13
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 here:
>
> http://peltiertech.com/Excel/Pivots/pivotstart.htm
> .
>
 | 
Pages: 1
Prev: Sharing Violation
Next: Index/Lookup Function?