Prev: Allow multiple rows of worksheet tabs for complex spreadsheets
Next: Shutting down the Discussion groups
From: steve on 3 Jun 2010 09:12
Hi,

In "C1" enter the formula

=IF(COUNTIF($A$1:A1,A1)=1,SUMIF(A:A,A1,$B$1:B1),"")

And fill/copy down as far as your list goes

In "D1" enter the formula

=INDIRECT("a"&MATCH(LARGE(C:C,ROW()),C:C,0))

And fill/copy down to "D5"

Regards
Steve

"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: John on 3 Jun 2010 09:24
Disregard my post, I forgot to sum them.
I think Steve got your answer.
John
"John" <johnd(a)newlook.com> wrote in message
news:u1jAT4xALHA.1764(a)TK2MSFTNGP04.phx.gbl...
> 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!
>>
>>
>>
>>
>

From: steve on 3 Jun 2010 09:28
Change the formula in d1 to read

=INDIRECT("a"&MATCH(LARGE(C:C,ROW()),C:C,0))&" "&LARGE(C:C,ROW())

And that will give you the name and score.

Regards
Steve
"steve" <steven_gray1(a)hotmail.com> wrote in message
news:eUeFi6xALHA.4388(a)TK2MSFTNGP04.phx.gbl...
> Hi,
>
> In "C1" enter the formula
>
> =IF(COUNTIF($A$1:A1,A1)=1,SUMIF(A:A,A1,$B$1:B1),"")
>
> And fill/copy down as far as your list goes
>
> In "D1" enter the formula
>
> =INDIRECT("a"&MATCH(LARGE(C:C,ROW()),C:C,0))
>
> And fill/copy down to "D5"
>
> Regards
> Steve
>
> "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: Max on 3 Jun 2010 09:55
Here's a pure non-array formulas play which will achieve your multiple
objectives, viz: extract unique names, total up scores per name then
auto-sort in descending order with tiebreaks (to cater for possibility of
tied totals)

Assume your source data is in sheet: x, in A2:B2 down
In another sheet, place in
A2: =IF(x!A2="","",IF(COUNTIF(x!A$2:A2,x!A2)>1,"",ROW()))
B2: =INDEX(x!A:A,SMALL(A:A,ROWS($1:1)))
C2: =SUMIF(x!A:A,B2,x!B:B)
D2: =IF(ISERROR(B2),"",C2-ROW()/10^10)
E2:
=IF(ROWS($1:1)>COUNT($D:$D),"",INDEX(B:B,MATCH(LARGE($D:$D,ROWS($1:1)),$D:$D,0)))
Copy E2 to F2. Select A2:F2, copy down to cover the max expected extent of
source data in x, eg down to F100. Minimize/hide away cols A to D. Cols E & F
will return the required results, ie a uniques listing of the names-total
scores, sorted in descending order by the scores. Names with tied scores, if
any, will be listed in the same relative order that they appear within the
source data. Read off the top xx as desired.
--
Max
Singapore
---
"puiuluipui" wrote:
> 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 below i need the second top value
> and name....and so on....
First  |  Prev  | 
Pages: 1 2
Prev: Allow multiple rows of worksheet tabs for complex spreadsheets
Next: Shutting down the Discussion groups