Index and Match

[bscarano]

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.

 

[Bob Phillips]

Why nit just copy them, and then sort them?

--

HTH

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

 

[bscarano]

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

 

[Bob Phillips]

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)

 

[Herbert Seidenberg]

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