View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
flarunner flarunner is offline
external usenet poster
 
Posts: 9
Default sort column corresponding to second column

I have 3 columns of data:
Column A: Daily Miles Run
Column B: Daily Pace of Run
Column C: Sorted Miles

The following formula (copied down) was used to get the Sorted Miles in
Column C:

=IF(COUNT(A$2:A$8)=ROWS($1:1),SMALL(A$2:A$8,ROWS( $1:1)),"")

This is what it looks like:

A B C
3 8:21 3
4 8:38 4
6 8:55 4
5 8:46 5
4 8:45 5
5 8:32 6

I would like to get the Daily Paces (Column B) to also be sorted from
smallest to largest AND to correspond to the Sorted Miles (Column C).

I have used various LOOKUP, INDEX and MATCH formulas, but they return the
FIRST INSTANCE of repeated numbers, and keep repeating that number, as shown
below in Column D:

A B C D
3 8:21 3 8:21
4 8:38 4 8:38
6 8:55 4 8:38
5 8:46 5 8:46
4 8:45 5 8:46
5 8:32 6 8:55


How can I make Column D read as follows?

D
8:21
8:38
8:45
8:32
8:46
8:55

Thanks for any and all help.