Index and Match-REP Total

OK, here's my question.


I have a list of a set of values listed below:

REP Total
John 12500
Peter 16955
Rich 14986
Steve 11986
Joe 13579
Joann 12697

=INDEX(REP,MATCH(MAX(Total),Total,0)) <--Max Value(16955),Peter
=INDEX(REP,MATCH(MIN(Total),Total,0)) <--Min Value(11986),Steve


I want to, based on value, sort these on another sheet. Using the
formula above, I can get the MAX and MIN. How do I go about getting
the rest of the values sorted accordingly?


Fester.

Why nit just copy them, and then sort them?

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> OK, here's my question.
>
>
> I have a list of a set of values listed below:
>
> REP Total
> John 12500
> Peter 16955
> Rich 14986
> Steve 11986
> Joe 13579
> Joann 12697
>
> =INDEX(REP,MATCH(MAX(Total),Total,0)) <--Max Value(16955),Peter
> =INDEX(REP,MATCH(MIN(Total),Total,0)) <--Min Value(11986),Steve
>
>
> I want to, based on value, sort these on another sheet. Using the
> formula above, I can get the MAX and MIN. How do I go about getting
> the rest of the values sorted accordingly?
>
>
> Fester.
>

Good question, one of my managers populates the amount information
weekly and has about 60 stores to enter. I'm trying to automate as much
as possible. This way, the amounts are entered and all of the other
results fall into place.

Fester

You could record a macro that does the copy and sort, and assign that to a
button.

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> Good question, one of my managers populates the amount information
> weekly and has about 60 stores to enter. I'm trying to automate as much
> as possible. This way, the amounts are entered and all of the other
> results fall into place.
>
> Fester
>

On another sheet, create two REP sized vectors named Sort_R and Sort_T
Into Sort_T enter:
=LARGE(Total,ROW(Sort_T R)-ROW(INDEX(Sort_T,1))+1)
Into Sort_R enter:
=INDEX(REP,MATCH(Sort_T,Total,0))