Ranking Data

[Marcus]

Hello,
What I am trying to do is get a ranking based off of data. Example: I
have a database with store #'s and their sales. I want to be able to rank who
did the best in sales on a 1st, 2nd, 3rd, and so on, and return it to a
different sheet than what the data is kept in. Anyone got any ideas???

[Gary''s Student]

Say your original data is in Sheet1 column A. In Sheet2, A1 enter:

=LARGE(Sheet1!A:A,ROW()) and copy down

The highest value will be first, the second highest nextm etc.
--
Gary's Student


"Marcus" wrote:

> Hello,
> What I am trying to do is get a ranking based off of data. Example: I
> have a database with store #'s and their sales. I want to be able to rank who
> did the best in sales on a 1st, 2nd, 3rd, and so on, and return it to a
> different sheet than what the data is kept in. Anyone got any ideas???
>
>

[Marcus]

I also want the store # with it. Example. Sheet1 Column A is the store #,
Column 2 is the sales. I want to return both columns based of the highest
sales.

"Gary''s Student" wrote:

> Say your original data is in Sheet1 column A. In Sheet2, A1 enter:
>
> =LARGE(Sheet1!A:A,ROW()) and copy down
>
> The highest value will be first, the second highest nextm etc.
> --
> Gary's Student
>
>
> "Marcus" wrote:
>
> > Hello,
> > What I am trying to do is get a ranking based off of data. Example: I
> > have a database with store #'s and their sales. I want to be able to rank who
> > did the best in sales on a 1st, 2nd, 3rd, and so on, and return it to a
> > different sheet than what the data is kept in. Anyone got any ideas???
> >
> >

[Gary''s Student]

O.K., we modify the ranking formula because your original sales data is in
column B:

=LARGE(Sheet1!B:B,ROW()) (still in column A of Sheet2)

Now we have to do the equivalent of VLOOKUP to get the store number:

=INDIRECT("Sheet1!A" & MATCH(A1,Sheet1!B:B,0)) (in another column of Sheet2)
--
Gary's Student


"Marcus" wrote:

> I also want the store # with it. Example. Sheet1 Column A is the store #,
> Column 2 is the sales. I want to return both columns based of the highest
> sales.
>
> "Gary''s Student" wrote:
>
> > Say your original data is in Sheet1 column A. In Sheet2, A1 enter:
> >
> > =LARGE(Sheet1!A:A,ROW()) and copy down
> >
> > The highest value will be first, the second highest nextm etc.
> > --
> > Gary's Student
> >
> >
> > "Marcus" wrote:
> >
> > > Hello,
> > > What I am trying to do is get a ranking based off of data. Example: I
> > > have a database with store #'s and their sales. I want to be able to rank who
> > > did the best in sales on a 1st, 2nd, 3rd, and so on, and return it to a
> > > different sheet than what the data is kept in. Anyone got any ideas???
> > >
> > >

[daddylonglegs]

Hi Gary''s Student, surely INDEX is preferable to INDIRECT.....

=INDEX(Sheet1!A:A,MATCH(A1,Sheet1!B:B,0))

although there will be problems with this approach if there are ties......

[Gary''s Student]

You are correct, INDEX() works just fine. I only wish we could use VLOOKUP
here.
--
Gary's Student


"daddylonglegs" wrote:

>
> Hi Gary''s Student, surely INDEX is preferable to INDIRECT.....
>
> =INDEX(Sheet1!A:A,MATCH(A1,Sheet1!B:B,0))
>
> although there will be problems with this approach if there are
> ties......
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=537046
>
>

[Pete_UK]

What about overcoming the problem with tied values - if you have two
second placed stores then only the first in the list will be reported.
The way I have overcome this in the past is to have a helper column
which records the position of the item, then use this to determine the
range to use for the next LARGE function (using INDIRECT - it gets
messy).

Pete

[Marcus]

The index worked great. Thank you.

"Pete_UK" wrote:

> What about overcoming the problem with tied values - if you have two
> second placed stores then only the first in the list will be reported.
> The way I have overcome this in the past is to have a helper column
> which records the position of the item, then use this to determine the
> range to use for the next LARGE function (using INDIRECT - it gets
> messy).
>
> Pete
>
>