Prev: Allow multiple rows of worksheet tabs for complex spreadsheets
Next: Shutting down the Discussion groups
From: puiuluipui on 3 Jun 2010 08:34
Hi, i have a table like this:

john 4500
john 1200
mary 900
jim 5000
john 3700
mary 2300
jim 1400

And i need formulas to extract top 5 values:
I need something like this:
John 9400
jim 6400
.....etc

Can this be done?
Thanks!




From: Jacob Skaria on 3 Jun 2010 08:52
Assign header to both columns and use Pivot Table. Data>Pivot table....Drag
and drop name to 'Row field' and the values to Data items area..
set AutoSort options from Right click>Field Settings>Advanced>

--
Jacob (MVP - Excel)


"puiuluipui" wrote:

> Hi, i have a table like this:
>
> john 4500
> john 1200
> mary 900
> jim 5000
> john 3700
> mary 2300
> jim 1400
>
> And i need formulas to extract top 5 values:
> I need something like this:
> John 9400
> jim 6400
> ....etc
>
> Can this be done?
> Thanks!
>
>
>
>
From: Steve Dunn on 3 Jun 2010 08:56
Investigate the functions LARGE, and LOOKUP.


"puiuluipui" <puiuluipui(a)discussions.microsoft.com> wrote in message
news:7EE72D07-724B-4B7E-8D29-2A52B932877F(a)microsoft.com...
> Hi, i have a table like this:
>
> john 4500
> john 1200
> mary 900
> jim 5000
> john 3700
> mary 2300
> jim 1400
>
> And i need formulas to extract top 5 values:
> I need something like this:
> John 9400
> jim 6400
> ....etc
>
> Can this be done?
> Thanks!
>
>
>
>

From: puiuluipui on 3 Jun 2010 08:58
Hi, the problem is that i need this top values in another sheet. that's why i
need the formulas. and the sheet with the database is connected to a database
and i cannot modify it.
So i need a formula to make the sum by names and than to display in another
sheet, the top name and sum. In the cell bellow i need the second top value
and name....and so on....


Can this be done?

"Jacob Skaria" wrote:

> Assign header to both columns and use Pivot Table. Data>Pivot table....Drag
> and drop name to 'Row field' and the values to Data items area..
> set AutoSort options from Right click>Field Settings>Advanced>
>
> --
> Jacob (MVP - Excel)
>
>
> "puiuluipui" wrote:
>
> > Hi, i have a table like this:
> >
> > john 4500
> > john 1200
> > mary 900
> > jim 5000
> > john 3700
> > mary 2300
> > jim 1400
> >
> > And i need formulas to extract top 5 values:
> > I need something like this:
> > John 9400
> > jim 6400
> > ....etc
> >
> > Can this be done?
> > Thanks!
> >
> >
> >
> >
From: John on 3 Jun 2010 09:09
Try this
=INDEX($A$1:$A$7,MATCH(LARGE($B$1:$B$7,1),$B$1:$B$7,0))&LARGE($B$1:$B$7,1)
This will give you the name and value in the same cell.
You can separate them if you want it in separate cell.
Adjust range to your needs.
HTH
John
"puiuluipui" <puiuluipui(a)discussions.microsoft.com> wrote in message
news:7EE72D07-724B-4B7E-8D29-2A52B932877F(a)microsoft.com...
> Hi, i have a table like this:
>
> john 4500
> john 1200
> mary 900
> jim 5000
> john 3700
> mary 2300
> jim 1400
>
> And i need formulas to extract top 5 values:
> I need something like this:
> John 9400
> jim 6400
> ....etc
>
> Can this be done?
> Thanks!
>
>
>
>

 |  Next  |  Last
Pages: 1 2
Prev: Allow multiple rows of worksheet tabs for complex spreadsheets
Next: Shutting down the Discussion groups