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

One way using non array formulae ..

Assume source table is in A1:C8

Put in D2: =IF(B2>0,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" <[email protected]> wrote in message
news:[email protected]...
> 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

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$20>0,ROW($A$1:$A$20),""),ROW($A$1:$A$20))),"",
INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20>0,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$20>0,ROW($A$1:$A$20),""),ROW($A$1:$A$20))),"",
INDEX($B$1:$B$20,SMALL(IF($B$1:$B$20>0,ROW($A$1:$A$20),""),ROW($A$1:$A$20)))
)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Matthew" <[email protected]> wrote in message
news:[email protected]...
> 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

Max,

I haven't studied your solution in enough detail to be sure, but doesn't
this achieve the same result?

=IF(B2>0,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" <[email protected]> wrote in message
news:[email protected]...
> One way using non array formulae ..
>
> Assume source table is in A1:C8
>
> Put in D2: =IF(B2>0,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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

"Matthew" <[email protected]> wrote in message
news:[email protected]...
>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

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

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

"Bob Phillips" wrote:
> .. but doesn't this achieve the same result?
> =IF(B2>0,RANK(B2,$B$2:$B$8),"")

Unlike : =IF(B2>0,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
--

You're welcome, Matthew !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Matthew" <[email protected]> wrote in message
news:[email protected]...
> Thankyou Everyone....Excellent responses!