software

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???
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 Studentquot;Marcusquot; wrote:

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

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.

quot;Gary''s Studentquot; wrote:

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

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(quot;Sheet1!Aquot; amp; MATCH(A1,Sheet1!B:B,0)) (in another column of Sheet2)
--
Gary's Studentquot;Marcusquot; wrote:

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


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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=537046You are correct, INDEX() works just fine. I only wish we could use VLOOKUP
here.
--
Gary's Studentquot;daddylonglegsquot; wrote:

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

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).

PeteThe index worked great. Thank you.

quot;Pete_UKquot; wrote:

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