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
|
|||
|
|||
Top value
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! |
#2
|
|||
|
|||
Top value
Assign header to both columns and use Pivot Table. DataPivot table....Drag
and drop name to 'Row field' and the values to Data items area.. set AutoSort options from Right clickField SettingsAdvanced -- 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! |
#3
|
|||
|
|||
Top value
Investigate the functions LARGE, and LOOKUP.
"puiuluipui" wrote in message ... 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! |
#4
|
|||
|
|||
Top value
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. DataPivot table....Drag and drop name to 'Row field' and the values to Data items area.. set AutoSort options from Right clickField SettingsAdvanced -- 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! |
#5
|
|||
|
|||
Top value
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" wrote in message ... 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! |
#6
|
|||
|
|||
Top value
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" wrote in message ... 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! |
#7
|
|||
|
|||
Top value
Disregard my post, I forgot to sum them.
I think Steve got your answer. John "John" wrote in message ... 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" wrote in message ... 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! |
#8
|
|||
|
|||
Top value
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" wrote in message ... 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" wrote in message ... 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! |
#9
|
|||
|
|||
Top value
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(LAR GE($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.... |
Thread Tools | |
Display Modes | |
|
|