Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DUPLICATE RANKS
I have the following data table :-
NAME POINTS RANK Adrian 0 5 Billy 0 5 Carl 23 1 Des 4 3 John 4 3 William 6 2 Hazel 0 5 The actual table is a lot bigger than this but it gives the gist of what I need. Basically I want to extract the names of everyone with more than 0 points in one column, and the points each person has in the next column (Ranked in order if possible). Cheers |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DUPLICATE RANKS
One way using non array formulae ..
Assume source table is in A1:C8 Put in D2: =IF(B20,C2+ROW()/10^10,"") (Leave D1 empty) Put in E2: =IF(ISERROR(SMALL(D:D,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0))) Put in F2: =IF(E2="","",VLOOKUP(E2,A:B,2,0)) Select D2:F2, copy down until the last row of data Cols E and F will return the desired results -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Matthew" wrote in message ... I have the following data table :- NAME POINTS RANK Adrian 0 5 Billy 0 5 Carl 23 1 Des 4 3 John 4 3 William 6 2 Hazel 0 5 The actual table is a lot bigger than this but it gives the gist of what I need. Basically I want to extract the names of everyone with more than 0 points in one column, and the points each person has in the next column (Ranked in order if possible). Cheers |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DUPLICATE RANKS
Matthew,
Not sorted in rank, but I can get you the list Select E1:E20 (or however many you think you need) and add this formula to the formula bar =IF(ISERROR(SMALL(IF($B$1:$B$200,ROW($A$1:$A$20), ""),ROW($A$1:$A$20))),"", INDEX($A$1:$A$20,SMALL(IF($B$1:$B$200,ROW($A$1:$A $20),""),ROW($A$1:$A$20))) ) and commit with Ctrl-Shift-Enter. Then do the same for F1:F20 with this formula =IF(ISERROR(SMALL(IF($B$1:$B$200,ROW($A$1:$A$20), ""),ROW($A$1:$A$20))),"", INDEX($B$1:$B$20,SMALL(IF($B$1:$B$200,ROW($A$1:$A $20),""),ROW($A$1:$A$20))) ) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Matthew" wrote in message ... I have the following data table :- NAME POINTS RANK Adrian 0 5 Billy 0 5 Carl 23 1 Des 4 3 John 4 3 William 6 2 Hazel 0 5 The actual table is a lot bigger than this but it gives the gist of what I need. Basically I want to extract the names of everyone with more than 0 points in one column, and the points each person has in the next column (Ranked in order if possible). Cheers |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DUPLICATE RANKS
Max,
I haven't studied your solution in enough detail to be sure, but doesn't this achieve the same result? =IF(B20,RANK(B2,$B$2:$B$8),"") On another point, when I first read it I thought that was what was wanted, but on reading the text, I decided he wanted a list of the non-zero scores that he could rank, hence my suggestion. You could always use either solution and filter the zeroes I suppose <vbg Regards Bob "Max" wrote in message ... One way using non array formulae .. Assume source table is in A1:C8 Put in D2: =IF(B20,C2+ROW()/10^10,"") (Leave D1 empty) Put in E2: =IF(ISERROR(SMALL(D:D,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0))) Put in F2: =IF(E2="","",VLOOKUP(E2,A:B,2,0)) Select D2:F2, copy down until the last row of data Cols E and F will return the desired results -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Matthew" wrote in message ... I have the following data table :- NAME POINTS RANK Adrian 0 5 Billy 0 5 Carl 23 1 Des 4 3 John 4 3 William 6 2 Hazel 0 5 The actual table is a lot bigger than this but it gives the gist of what I need. Basically I want to extract the names of everyone with more than 0 points in one column, and the points each person has in the next column (Ranked in order if possible). Cheers |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DUPLICATE RANKS
"Matthew" wrote in message
... I have the following data table :- NAME POINTS RANK Adrian 0 5 Billy 0 5 Carl 23 1 Des 4 3 John 4 3 William 6 2 Hazel 0 5 The actual table is a lot bigger than this but it gives the gist of what I need. Basically I want to extract the names of everyone with more than 0 points in one column, and the points each person has in the next column (Ranked in order if possible). Cheers Assuming you have Names in a range named Names and Points in a range named Points: =IF(ROW(A1)COUNT(Points),"", INDEX(Points,MATCH(D1,Names,0))) This gives Points ordered ascending {=IF(ROW(A1)COUNT(Points),"",INDEX(Names, MATCH(ROW(A1),RANK(Points,Points,1)+ COUNTIF(OFFSET(Points,,,ROW(Points)- ROW(OFFSET(Points,,,1))+1),Points)-1,0)))} FormulaArray This gives the related names =IF(ROW(A1)COUNT(Points),"", INDEX(RANK(Points,Points,1),MATCH(D1,Names,0))) This gives ranks ordered ascending Bruno |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DUPLICATE RANKS
One way is to make a pivot table of the data. Make name the row field. Make
points and rank data fields (sum of). Make points a page field as well. Then double-click on the page field points heading and choose to hide items that have 0 points (items with 0 points will disappear from the pivot table). Then double-click on the row field name heading. Choose advanced. In the autosort option choose ascending and then use the drop down arrow on the "using field" to select the field you want to sort by (sum of rank). I believe this should do it for you rather easily. You can refresh the pivot table as/if the data changes. Hope this helps. Bill Horton "Matthew" wrote: I have the following data table :- NAME POINTS RANK Adrian 0 5 Billy 0 5 Carl 23 1 Des 4 3 John 4 3 William 6 2 Hazel 0 5 The actual table is a lot bigger than this but it gives the gist of what I need. Basically I want to extract the names of everyone with more than 0 points in one column, and the points each person has in the next column (Ranked in order if possible). Cheers |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DUPLICATE RANKS
Thankyou Everyone....Excellent responses!
"William Horton" wrote: One way is to make a pivot table of the data. Make name the row field. Make points and rank data fields (sum of). Make points a page field as well. Then double-click on the page field points heading and choose to hide items that have 0 points (items with 0 points will disappear from the pivot table). Then double-click on the row field name heading. Choose advanced. In the autosort option choose ascending and then use the drop down arrow on the "using field" to select the field you want to sort by (sum of rank). I believe this should do it for you rather easily. You can refresh the pivot table as/if the data changes. Hope this helps. Bill Horton "Matthew" wrote: I have the following data table :- NAME POINTS RANK Adrian 0 5 Billy 0 5 Carl 23 1 Des 4 3 John 4 3 William 6 2 Hazel 0 5 The actual table is a lot bigger than this but it gives the gist of what I need. Basically I want to extract the names of everyone with more than 0 points in one column, and the points each person has in the next column (Ranked in order if possible). Cheers |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DUPLICATE RANKS
"Bob Phillips" wrote:
.. but doesn't this achieve the same result? =IF(B20,RANK(B2,$B$2:$B$8),"") Unlike : =IF(B20,C2+ROW()/10^10,""), think the above would not be able to function as the arb tie-breaker col in say, D2, filled down to D8 (there would be the same prob of duplicate ranks) In my response, I had simply assumed the source table includes the OP's rank formula in col C, and the suggested 3 col set-up (which is easily filled all the way down <g) was merely an extension from there to drive the results out. Cheers. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DUPLICATE RANKS
You're welcome, Matthew !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Matthew" wrote in message ... Thankyou Everyone....Excellent responses! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find duplicate rows in a list in Excel, and not delete it | Excel Discussion (Misc queries) | |||
Duplicate Disaster | Excel Discussion (Misc queries) | |||
Keeping duplicate rows | Excel Worksheet Functions | |||
Showing Duplicate Rows | Excel Discussion (Misc queries) | |||
Duplicate records in Excel | Excel Discussion (Misc queries) |